0

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.

Dov Miller
  • 1,958
  • 5
  • 34
  • 46
AnkitMittal
  • 166
  • 2
  • 18

1 Answers1

1

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

mukesh joshi
  • 584
  • 5
  • 19
  • 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! – AnkitMittal Jul 04 '17 at 15:35
  • 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. – mukesh joshi Jul 05 '17 at 16:49
  • Thats exactly what i did. I increased the size to varchar(8000) and it worked. – AnkitMittal Jul 06 '17 at 08:01