CODEWITHSUNDEEP
sql-serverentity-frameworksql-server-2008-r2
Peter Smith
Peter Smith

Reputation: 5550

Searching and updating encrypted data in an SQL server using entity framework

I am using C# 4.0, VS 2010 MVC 3 and Entity Framework for a web application and using SQL server 2008R2 for data storage. I need to encrypt three columns in one table - two are text columns and one is a date column, to update them and to search for items in those columns using the above technologies.

TDE is not an option.

It seems best practice to perform all the encryption functions on the SQL server however, although there are plenty of examples on how to encrypt the columns as a one off exercise, there is nothing on updating or searching these fields once encrypted.

I can see no other way than to move the encryption and decryption into the application.

All guidance, assistance, pointers and hints are very welcome.

Thank you.

Upvotes: 0

Views: 2360

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294407

You cannot search encrypted data, by definition. What you can search instead is a hash of the known plain text and deal with possible collisions. For this you need to add additional columns to save the hash of the plain text being encrypted. See Indexing encrypted data for more details and an example.

As for updates I really don't see what the question is. You update the encrypted value with a new encrypted value, just like any other update.

TDE is, by a large margin, a much much better option. Just sayin'.

Upvotes: 3

Related Questions