Encryption insert driving me mad

• NicHopper

  SSCrazy Eights

  Points: 9090

  More actions

  May 23, 2023 at 9:59 am

  #4195455

  Hi all,

  I'm wondering if someone can help me with this, I have a table (very simple)

  CREATE TABLE [api].[Employee](
  [EmployeeID] [int] NULL,
  [CreatedDate] [datetime] NULL,
  [Title] [varchar](10) NULL
  ) ON [PRIMARY]
  GO

  I've then gone and encrypted the Title column in SSMS using Randomized encryption type with a key in our Azure Key vault, all works fine and existing data in the table is encrypted. The issue I have is when I try to insert

  DECLARE @X INT = 1,
  @Z VARCHAR(10) = 'MR'
  
  INSERT INTO [api].[Employee]
             ([EmployeeID]
             ,[Title]
          )
       VALUES
             (@X
             ,@Z
     )
  

  I get the error

  Encryption scheme mismatch for columns/variables '@Z'. The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '4' expects it to be RANDOMIZED, or DETERMINISTIC, or PLAINTEXT.

  and I've looked round and cant pinpoint what exactly what the issue is, any pointers from anyone would be greatly appreciated.

  Thanks,

  Nic

  Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

• Site Owners

  SSC Guru

  Points: 80330

  More actions

  May 24, 2023 at 10:10 am

  #4196390

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

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

• Jonathan AC Roberts

  SSCoach

  Points: 18354

  More actions

  May 24, 2023 at 2:27 pm

  #4196560