CODEWITHSUNDEEP
c#sql-serverlinq-to-sqlsql-server-2016always-encrypted
AnkitMittal
AnkitMittal

Reputation: 164

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

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.

Upvotes: 0

Views: 2247

Answers (1)

mukesh joshi
mukesh joshi

Reputation: 594

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

Upvotes: 1

Related Questions