Inserting data into a column that is using Always Encrypted

Question

Inserting data into a column that is using Always Encrypted

SSCrazy Eights

Points: 8485

March 13, 2025 at 4:07 pm

Problem: I am trying to insert a a value into a column that has been encrypted using 'deterministic' encryption via a stored procedure.

The stored Proc code is

ALTER PROCEDURE [dbo].[InsertData]
@ID INT,
@Col1 nvarchar(20),
@Col2 VARCHAR (8),
@ExpiryDate DATE
AS

BEGIN
    SET NOCOUNT ON;
    DECLARE @PModel AS NVARCHAR (5), @FID AS NVARCHAR (4) ;
    SELECT @PModel = PModel,
           @FID = FID
    FROM   [dbo].[table1]
    WHERE  [Col2] = @Col2
           AND RIGHT(@Col1, 10) BETWEEN [LowNumRange] AND [HighHumRange];
    INSERT  INTO [dbo].[EncryptedTable] ([Col1],[Col2], [ID], [PModel], [FID], [EndDigits], [ExpiryDate], [Active], [CreatedDate], [LastModifiedDate])
    VALUES (@Col1, @Col2, @ID,@PModel,@FID,RIGHT(@Col1,4),  @ExpiryDate, 1, GETDATE(), NULL)
END

I call the procedure with the following code. I make sure my connection in SSMS has the 'column encryption setting = enabled' and the Query options - Advanced - 'Enable Parameterization for Always Encrypted' is ticked. I am running SSMS version 20.2. The SQL instance is an Azure Managed Instance. The column that has been encrypted is Col1 using 'deterministic' encryption type and has been created with the data type of Nvarchar(20)

DECLARE @RETURN_VALUE INT
DECLARE @Col1 NVARCHAR(20) = '5533356858004824'
DECLARE @col2 VARCHAR(8) = '553335'
DECLARE @ExpiryDate DATETIME = '2028-01-01'
DECLARE @ID INT = 14638

EXEC @RETURN_VALUE= [dbo].[InsertData]
@ID = @ID,
@Col1 = @Col1,
@Col2 = @Col2,
@ExpiryDate = @ExpiryDate

SELECT'RETURN Value' = @RETURN_VALUE

It errors with:

Msg 33278, Level 16, State 4, Procedure dbo.InsertData, Line 11 [Batch Start Line 0]
Cannot assign the same encryption scheme to two expressions at line '11'. The encryption scheme of the first expression is (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto3', column_encryption_key_database_name = 'xxx') and the encryption scheme of the second expression is (encryption_type = 'PLAINTEXT'). Other columns/variables with the same encryption scheme as the first expression are: 'Col1'. Other columns/variables with the same encryption scheme as the second expression are: '@Col1'.

(1 row affected)

Completion time: 2025-03-13T15:00:44.9388818+00:00

I am interpreting the error message to mean we are trying to pass in 'plaintext' into a field that has been encrypted, but i thought SSMS was supposed to handle that side of things by having the options set in my connection as i have outlined.

Any help to resolve this error so the stored proc can do the insert would be appreciated

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80332 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.