Inserting data into a column that is using Always Encrypted

• PearlJammer1

  SSCrazy Eights

  Points: 8485

  More actions

  March 13, 2025 at 4:07 pm

  #4558849

  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

   

   

• Site Owners

  SSC Guru

  Points: 80342

  More actions

  March 14, 2025 at 4:10 pm

  #4559537

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

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

• Emperor100

  SSCrazy

  Points: 2205

  More actions

  March 15, 2025 at 5:43 pm

  #4560303

  check these links, they might help.

  https://stackoverflow.com/questions/52179767/how-to-perform-insert-update-and-delete-operations-on-encrypted-column-in-a-ta

  and this one

  https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/configure-always-encrypted-using-sql-server-management-studio?view=sql-server-2017

   

   

  =======================================================================

• March 17, 2025 at 7:39 am

  This was removed by the editor as SPAM

• March 17, 2025 at 7:39 am

  This was removed by the editor as SPAM