So you are using SQL Server, and you have a field that needs to be encrypted with SQL Server encryption, and you are wondering how to handle this in the entity framework. Unfortunately the news is not good. If you are like me you figured you where using Microsoft’s database and Microsoft’s data access framework, so surely there are just a couple of properties that you set and it handles the encryption and decryption. I was wrong, and I have not found any easy answers to this issue. I will go over a couple examples of how to handle this. I will assume that to encrypt your field you used a symmetric key that is encrypted with a certificate.

Method 1 – Use A View And Stored Procedures

The first method is to use a view and stored procedures to have the entity framework access the data. The first step is to create the view that will be used to access the data, this view will need to decrypt the encrypted fields. In a view you cannot use the OPEN SYMMETRIC KEY statement. So instead you will want to use the DecryptByKeyAutoCert method to decrypt the data. Here a SQL statement that creates a view like this:

CREATE VIEW [dbo].[SampleDecryptView]
AS

SELECT   Field1,
         convert(varchar(250),
             DecryptByKeyAutoCert(CERT_ID('certificatename'), NULL,
             EncryptedField)) AS EncryptedField
FROM TestTable

GO

Now that you have your view created you need to add it to the Entity Framework model.  There are a number of steps that you have to take to get the view working correctly in the entity framework, especially if you want it to have associations.  You can find instructions for how to do this over at the Mehroz’s Experiments blog. However, before you decided to go this route, look at all the steps that are required, and make sure to notice one of his the final comments:

Note that anytime you update the model from database, it will reset the SSDL and we will manually need to redo all the steps (related to storage model) as described above.

That’s right, every time you update the model, you have to redo a lot of these changes.  For me this was a deal killer, because the our projects are constantly growing, and we are constantly adding new tables.

Using Stored Procedures

You can map stored procedures to a entity in in the entity framework.  It allows you to specify stored procedures that will be used for update, insert, and delete operations.  So you just write the update and insert stored procedures so that they encrypt the data before saving it to the table.  Then to get the data out, you can map a stored procedure to the entity as a function.  This stored procedure can decrypt the data.

Here is a good tutorial that shows how to map the update, insert, and delete stored procedures, and the robbagby blog has a create example of how to map the select stored procedure to a function.

The issue I had with this model retrieving records.  If you use the function that you created it works great, because the stored procedure will decrypt the field.  But what if you are access the entity from another entity by a association.  At that point, it is not going to use the stored procedure and your data is not going to be decrypted.

Adding A Function

To be honest I don’t see how this solution solves the problem, I will include it for completeness.  On the SafNet blog, Stephen Fuqua describes how to create a custom function in the Entity framework.  This seems to work find for inserts and updates, but I don’t see how it solved the issue of retrieving the data from the database.  But I might just be missing something.

Revert Back To ADO.Net

Unfortunately this is the path that I ended up taking.  The entity framework handled this scenario to awkwardly for me, and I decided the best path was to just use plain ADO.Net for this object.  I was disappointed with this, because I was really hoping to have all data access go through the entity framework.

Summary

I have tried to do a lot of searching on this to make sure I did not miss something, but if you notice something I missed please let me know.  I am hoping the Entity Framework 4.0 will have a better solution for this, but so far I have not had a chance to test it.

Tags: Encryption, Entity Framework, SQL Server