question

FabinVegaSnchez-8309 avatar image
0 Votes"
FabinVegaSnchez-8309 asked OuryBa-MSFT commented

Always encryption and store procedure

Is it possible to use always encryption feature and a stored procedure that is receiving as a parameter a Json string, and then use OpenJson to insert in a table with encrypted columns?
I am using Management Studio 18.9.1, azure key vault to generate the master key for the encryption and net core 3.1.

I am receiving this error:
Operand type clash: varchar is incompatible with varchar(8000) encrypted with (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'db') collation_name = 'SQL_Latin1_General_CP1_CI_AS'`enter code here`

This is the sp:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[sp_upsert] (@myjson NVARCHAR(MAX), @importId int)
AS BEGIN

DECLARE @id VARCHAR(50) = @importId;

CREATE TABLE #Table (
RowID int not null identity(1,1) primary key,
ToEncrypt varchar(8000),
);


INSERT INTO #Table
SELECT * FROM OPENJSON(@myjson)
WITH
(
ToEncrypt varchar(8000)
);

DECLARE @i int;
SELECT @i = MIN(RowID) FROM #Table;
DECLARE @max int;
SELECT @max = MAX(RowID) FROM #Table;
DECLARE @ToEncrypt varchar(8000);

WHILE @i <= @max
BEGIN
SELECT @ToEncrypt = ToEncrypt FROM #Table WHERE RowID = @i;
SET @i = @i + 1;

BEGIN TRANSACTION;
BEGIN TRY
INSERT dbo.Table(ToEncrypt)
SELECT @ToEncrypt
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
END
DROP TABLE #Table;
END

azure-sql-database
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@FabinVegaSnchez-8309 Thank you for posting your question on Microsoft Q&A and for using Azure services.
Could you please open a support ticket so we can further investigate? Please let us know if you don't have a support plan.

Regards,
Oury

0 Votes 0 ·

0 Answers