I have configured 'Always Encrypted' on one of the columns of a table of my SQL Server database. I am able to select and view data from client SSMS after passing the 'Column Encryption Setting = Enabled' option.

But when I am trying to insert data into the table, following error comes:

Msg 206, Level 16, State 2, Line 1 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 = 'TEST') collation_name = 'SQL_Latin1_General_CP1_CI_AS'

I am querying simple insert TSQL statement here. What changes should I need to make for it to work?

Also, what changes need to be made to the application or application code if, we want to update or insert the encrypted column via application?

  • Can you post an example of the insert you are trying to do? – EzLo Jun 28 '18 at 9:31
  • @EzLo Hi, this is one of my test tables and I'm using a very basic insert statement, ie, INSERT INTO dbo.test_table VALUES(10, 'RandomName') – Shaurya S Jun 28 '18 at 9:36

You need to use variables for the values, so that SSMS can parameterize the query against the API that it is using. Something like:

    DECLARE @fname varchar(30) = 'Kula'
DECLARE @ename varchar(30) = 'Kalle'
DECLARE @pnr varchar(11) = '752312-4545'
DECLARE @age tinyint = 45

insert into dbo.Personer2 (Förnamn, Efternamn, Personnummer, Ålder) 
VALUES (@fname, @ename, @pnr, @age)

SELECT * FROM Personer2
  • Oh yes, it worked for me from SSMS! Thanks! But do I have to make changes in application coding as well? Or if the application is using ADO.NET 4.6, then I dont have to make any changes? Because the application owner is asking me if they have to make any changes in application code level if Always Encrypted is implemented. – Shaurya S Jun 28 '18 at 12:10
  • Because I have read this that: Microsoft have implied that converting your application to use Always Encrypted is simply a case of encrypting your data and recompiling your .NET application to use ADO.NET 4.6 – Shaurya S Jun 28 '18 at 12:13
  • 1
    As long as your application is using parameters in ADO.NET, then you should only have to add the connection string attribute. Using parameterized SQL Server in ADO.NET has been a best practice for decades, so hopefully your application does that already. – Tibor Karaszi Jun 28 '18 at 12:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.