MVC using Linq to Entity w/ sql encryption

Question

Currently i am using sql encryption and would like to continue using it through Linq. I have all my CRUD stored proc's created and wired up to the table in the model in order to handle the encryption/decryption through sql.

Main problem is that my database model see's a field type of varbinary(max) which is used for the sql encryption storage.

Problems:

A. Configuring the Entity Model - Cannot assign the varbinary field of the encrypted table to the CRUD stored proc's parameters, which are of type varchar. The stored proc's take the varchar value, encrypt it and then store it in the encrypted tables varbinary field.

B. Outside of the Entity Model creation - When referencing the encrypted entity table it expects a varbinary field, when in essence it really is a varchar field. This in turn causes coversion issues when trying to assign values to that encrypted field.

How does one get around this. I need the application to view this field as being decrypted like it is when it comes back from my stored proc. The CUD statements would then take the string values and the stored proc's tied to them in the entity model would do the encryption.

Thanks in advance.

Answer 1

There are 2 technologies "Linq to SQL" and "Entity Framework".

Entity Framework expects that the signature of the stored proc and the table columns match.

There are 2 ways that you could fix this.

• You could add a dummy varbinary field to the stored proc and a varchar filed to the table. This would allow everything to get what it expects, you just ignore the fields that you do not need.
• Execute the stored procedure without using a table (there are different ways to execute stored procs in EF). Then you avoid the problem with the mismatch

Answer 2

We recently did this in exactly that environment. It is not a very simple solution.

I could find NO way to have entities handle the SQL encryption. Here is what I did.

Brought the table into the entities designer.
Change the fields in your table to varchar
Create a sproc that returns all those fields, with the encrypted field decoded to strings
In the entity mapping, import the sproc
Change the sproc to return the table
Create another sproc to update those fields (or the entire table, your choice)
Change the table to NOT update or read the encrypted fields from the database (this allows you to still use that object for other things.

Alternatively, you can change your entity object to just not include those fields, and have those values specifically accessed through a sproc.