Using Always Encrypted with Entity Framework 6

08/27/2015

5 minutes to read

In this article

Entity Framework 6 was not designed from the start to work with the Always Encrypted feature of SQL Server 2016. However, a lot of effort has gone into making the feature work as transparently as possible with existing code. This article explores the limitations you will need to work around and the potential issues you should be aware of if you need to make your application based on Entity Framework 6 work with a database containing encrypted columns.

Database Schema Creation

Database First and Code First from existing database

The “EF Designer from database” and “Code First from database” workflows can be used to create a model from a database containing encrypted columns without issue, however EF does not store any additional metadata about encrypted columns. From EF perspective encrypted columns look like regular columns. This means that EF can’t handle encrypted columns in any special way – for instance produce different queries when encrypted columns are involved.

In order to create a new EntityDataModel:

go to Solution Explorer

right click on the project -> Add -> New Item…

choose ADO.NET Entity Data Model

follow Entity Data Model Wizard as shown below

Code First – Migrations

Migrations will require substantial tweaking – Entity Framework is not aware of the Column Master Keys and Column Encryption and has no way to model them, so this needs to be compensated by user.

Suggested solution:

Create database Column Master Keys, Column Encryption Keys, schema etc. outside Entity Framework (e.g. by using SQL Server Management Studio). More information about this can be found here: http://blogs.msdn.com/b/sqlsecurity/archive/2015/06/04/getting-started-with-always-encrypted.aspx Create Entity Framework model Enable-Migrations Add-Migration Remove encrypted properties from the migration code Add ALTER TABLE calls to add encrypted columns Update-Database

Example of the Up() migration method could look like this:



Code used to manually add encrypted columns is as follows:

Sql("ALTER TABLE [dbo].[Patients] ADD [SSN] [nvarchar](11) COLLATE Latin1_General_BIN2 ENCRYPTED WITH(ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = MyCek) NOT NULL");

Sql("ALTER TABLE [dbo].[Patients] ADD [BirthDate] [datetime2] ENCRYPTED WITH(ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = MyCek) NOT NULL");

Automatic migrations should be disabled as it will always try to create columns without information about encrypted columns. They can be disabled using the following code snippet:

public sealed class MyConfiguration : DbMigrationsConfiguration<AlwayEncryptedContext> { public MyConfiguration() { AutomaticMigrationsEnabled = false; } }

Code First – Database Initializers

Database Initializers will not be supported with Always Encrypted feature. EF has no way of modeling the encryption related concepts, and there is no good/easy way to inject custom SQL into the initializer pipeline. The exception is MigrateDatabaseToLatestVersion, which uses Migrations to perform initialization. In general, it is recommended to use Migrations over Initializers when working with encrypted columns. This includes seeding the database with initial data. Migration’s Configuration.Seed method can be used for that purpose.

Model First

Similar to Migrations, this requires user to create database manually. Users can create EF model using designer. EF will then create a DDL statements that should be manually tweaked to include column encryption. CMK and CEKs should be created separately as well.

Connection string adjustments

In order to allow querying, connection string that is being used need to be modified by adding:

Column Encryption Setting=Enabled

Insert, update and delete operations work properly without any additional customization. EF automatically parametrizes all insert/update/delete queries.

Querying over tables with encrypted columns

Entity Framework assumes order-comparability of PKs in many cases. If PK is encrypted, some scenarios will not work.

Also EF will sometimes print values of the Entity key in exception messages. This could cause sensitive information to be leaked to inappropriate parties.

This issue is being tracked by EntityFramework team here:

https://entityframework.codeplex.com/workitem/2784

Until this is fixed, it is recommended to use unencrypted surrogate keys if possible.

Filtering over an encrypted column

EF query will fail if we compare encrypted column to a constant, e.g.:

context.Patients.Where(p => p.SSN == "123-45-6789");

This is because constants are translated to literals in the query.

Suggested solution:

Pass the constant argument as closure – this will force parametrization, producing correct query:

var ssn = "123-45-6789"; context.Patients.Where(p => p.SSN == ssn);

Alternative solution:

EF can transparently replace constants in the query with parameters. This can be achieved using query interception extensibility features.

AlwaysEncryptedSample.cs (attached at the end of this blog post) illustrates how this can be done.

Sorting over an encrypted column

Sorting based on encrypted column is not supported on the database due to limitations of Always Encrypted feature.

Suggested solution:

Perform OrderBy operation on the client:

var firstName = "John"; context.Patients .Where(e => e.FirstName == firstName) .ToList() .OrderBy(e => e.SSN);

Grouping over an encrypted column

Some GroupBy operations are not supported (namely the LINQ specific grouping, without projecting group key or aggregate function) if entity key is encrypted. Reason is that those queries (that simply aggregate elements into IGrouping<,> statements) are translated into TSQL containing ORDER BY operation on the key. If the key is encrypted, the query will fail.

Suggested solution:

Use unencrypted surrogate key.

Alternative solution:

Perform GroupBy operation on the client:

context.Customers .Where(e => e.City == "London") .ToList() .GroupBy(c => c.Age);

Queries that produce GroupBy in relational sense (projecting aggregate function over elements, or projecting group key itself will be translated into TSQL GROUP BY operation, and are working properly without additional customization.

Projecting a collection

Queries that project a collection don’t work with encrypted columns if the key (or any part of the composite key) is encrypted, e.g.:

context.Customers.Select(c => c.Orders);

will not work if PK of the Customer entity is encrypted. This is because (similarly to some group by operations) EF introduces ORDER BY clause to the query.

Suggested solution:

Use unencrypted surrogate key.

Alternative solution:

Modify the query to project unordered structure (using SelectMany) and then group and process the results on the client:

context.Customers .SelectMany(c => c.Orders, (c, o) => new { c, o }) .ToList() .GroupBy(k => k.c, e => e.o, (k, os) => os);

Including collection in query results

Similarly to the above case, Include operation performed on a collection is not supported if the PK of the principal entity is encrypted, e.g:

context.Customers.Include("Orders");

Suggested solution:

Use unencrypted surrogate key.

Alternative solution:

Use lazy loading: https://msdn.microsoft.com/en-us/data/jj574232.aspx#lazy

or explicit loading: https://msdn.microsoft.com/en-us/data/jj574232.aspx#explicit.

[11/13/2015] Update: If you are interested in using Azure Key Vault for this demo instead of the certificate provider, please remember that you will need to register the provider in your code as explained in http://blogs.msdn.com/b/sqlsecurity/archive/2015/11/10/using-the-azure-key-vault-key-store-provider.aspx.

AlwaysEncryptedSample.zip