Entity Framework with Sql Server Column Level Encryption

Question

I have a requirement to encrypt multiple database columns (in Sql Server 2012). It was decided that we should use column level encryption (implemented on sql server). On the application side, I will create web api on top of some complex domain models. I really want to use the first Entity Framework code approach to maintain a clean domain model). Does anyone have a workable solution that does not involve returning to stored procedures? Ideally, I would like to somehow manipulate the sql generated by the entity infrastructure to wrap certain fields to perform sql encryption / decryption functions.

Ideally, something like:

modelBuilder.Entity<MyTable>().ToTable("Table1").Property(p => p.SensativeData).encrypt("keyName",authenticatorFunc); 

Answer 1

In SQL Server 2012, column-level encryption can be done mainly in two ways:

• Definition User encryption function in Entity framework. this blog
• SQL cell level encryption is performed in the entity infrastructure in dbcontext. The class (execute the open symmetric key code here) using this blog and using the stored procedure (which contains the decryption code for the specified field in the tables) retrieve the result sets.

There is a new feature in SQL Server 2016, i.e. Always encrypted and its implementation in the infrastructure of the entity is here .

Answer 2

Crypteron has a free Entity Framework adapter, CipherDb , that can work with any SQL Server. In fact, Crypteron CipherDb works with any Entity Framework compatible database - even MySQL, PostGreSQL, etc.

You can annotate the data model using [Secure] or name the property something like Secure_SocialSecurityNumber ( Secure_ is the key part), and CipherDb automatically performs data encryption, protection against unauthorized access, secure key storage, secure key distribution, caching, key control, ACLs and more. You can also use Crypteron to protect streams, files, objects, message queues, noSQL, etc.

You can find sample applications on GitHub at https://github.com/crypteron/crypteron-sample-apps

Disclaimer: I work there, and we have a free community version that anyone can use.