Update/Insert rows in SQL table with encrypted columns using LinqToSQL - Always Encrypt

Question

I am trying to get a hands-on on Always Encrypt feature of SQL Server 2016. I am using an existing application and database to do this.

I have a table [User], where i have encrypted the 'Password' column with 'Deterministic' type. I have made the connection string changes and I am able to retrieve all the rows. I have created the repository for this table.

I am trying to insert and update rows in this table with LinqToSQL using InsertOnSubmit() and SubmitChanges().

But whenever I try to insert new rows or update existing rows, I get the error:

Msg 206, Level 16, State 2, Line 7 Operand type clash: varchar is incompatible with varchar(20) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'BRO_UAT') collation_name = 'Latin1_General_BIN2'

I have read articles where using Stored Procs and parameterization has solved the issue. But, as I mentioned earlier, this is an existing project, and I have used LinqToSql and do not want to change the code. Insertion/Update works fine if the column is not encrypted!

Am I missing some setting?Please point me towards the right direction.

mukesh joshi · Accepted Answer · 2017-07-03 14:04:53Z

1

+50

Change your column to nvarchar and check. Also now even storing the password is not the best approach, store a signature instead and validate that signature when validating the password.

Check the link for the same. https://social.msdn.microsoft.com/Forums/en-US/77bb69f0-590e-40f5-b5e9-714bf590e008/how-to-handle-encrypted-column?forum=linqtosql

answered Jul 3, 2017 at 14:04

mukesh joshi

6046 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

AnkitMittal Over a year ago

Thanks Mukhesh. This seems to be working ok for few of my tests, I will check a coupke of scenarios and mark your answer accepted. Thanks so much for your help. This simple issue was making me pull my hair!

mukesh joshi Over a year ago

The error varchar is not compatible with varchar(20) also states a case when your column's size is less then the converted key being generated and that's why you are not able to save it. You can also try increasing the size of the column in the database.

AnkitMittal Over a year ago

Thats exactly what i did. I increased the size to varchar(8000) and it worked.

Collectives™ on Stack Overflow

Update/Insert rows in SQL table with encrypted columns using LinqToSQL - Always Encrypt

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related