Sql-server – Always Encrypted: How to insert or update encrypted columns

always-encryptedencryptionsql serversql-server-2016t-sql

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?

Best Answer

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
Related Question