Always encryption and store procedure

Fabián Vega Sánchez 1 Reputation point
2022-04-22T19:12:14.343+00:00

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
{count} votes