SQL Server 2008 R2 Encryption - with Entity Framework

Question

I'm looking to use database encryption on the database I connect to in my application via. Entity Framework.

Is there an easy way / best practice to be able to get the unencrypted data & write encrypted data back to the database. I don't particularly want to have to edit the edmx xml manually, but am struggling to find some resources that will tell me how to achieve this.

I am planning to use Symmetric Key and the triple DES encryption algorithm.

Answer 1

What do you mean by SQL Server 2008 R2 Encryption - it is very vague question because it has a lot of meanings.

Your general options:

• Transparent Data Encryption - Feature of SQL Server - whole database is encrypted on SQL Server side. Your application doesn't need to change and it should work with EF.
• Cell level encryption - feature of SQL Server - selected columns are encrypted and stored as varbinary. This requires special query and store commands so you will have to use specialized database views and stored procedures to interact with your DB if you want to use EF. If you don't want to use database views and stored procedures you will have to maintain EDMX manually and write all those SQL commands into its SSDL part.
• Encryption performed in your application - you will use ObjectMaterialized and SavingChanges events to handle decryption and encryption yourselves. You will probably be able to encrypt and decrypt only string or binary data because your property data type must not change (in case of string you will have to store encrypted value as base64 string).