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.

So this is about storing encrypted data, not a secured channel? — Gert Arnold
– Gert Arnold, Commented Feb 22, 2012 at 12:13

Ladislav Mrnka · Accepted Answer · 2012-02-22 19:43:31Z

5

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).

answered Feb 22, 2012 at 19:43

Ladislav Mrnka

365k60 gold badges666 silver badges673 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tom Ax Over a year ago

Thanks for your thorough answer. I have already discounted TDE, as it will not provide the level of encryption I need. I also don't want to encrypt in my app, as I feel this is not as advantageous as sql encryption. It looks like the second option you detail is what I will have to do - as I don't think there is another way with EF that will work the way I want it to.

ribald Over a year ago

@Ladislav Mrnka : for cell level encryption, if you used SP and Views to interact with the DB, what prevents some hacker from using the same SP/Views from decrypting your data?

Ladislav Mrnka Over a year ago

@ribald: It is about having data encrypted on the disk. If attacker gets credentials to access your database through normal connection there is only little what any kind of transparent or cell level encryption on the database level can do for protecting data.

nicolascolman · Accepted Answer · 2013-09-29 13:37:44Z

0

You can create a view that returns decrypted data.

For example:

CREATE VIEW [your_view_name] as
SELECT 
CONVERT(varchar(200),DECRYPTBYPASSPHRASE('[your_symmetric_key]', [encrypted_field])) As [encrypted_field],
FROM [your_table_name]

Then, add your view as an entity in your EF model.

To insert, update or delete operations, use stored procedures.

answered Sep 29, 2013 at 13:37

nicolascolman

5771 gold badge4 silver badges15 bronze badges

2 Comments

Rosdi Kasim Over a year ago

This means hard coding the symmetric key into the view definition right? Also anyone with access to the view will be able to view it.

Tony Over a year ago

My front door is over there and it is unlocked but just in case you need it here is the key.

Collectives™ on Stack Overflow

SQL Server 2008 R2 Encryption - with Entity Framework

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related