共用方式為

Using Always Encrypted with Entity Framework 6

  • 發行項

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:

  1. 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: https://blogs.msdn.com/b/sqlsecurity/archive/2015/06/04/getting-started-with-always-encrypted.aspx
  2. Create Entity Framework model
  3. Enable-Migrations
  4. Add-Migration
  5. Remove encrypted properties from the migration code
  6. Add ALTER TABLE calls to add encrypted columns
  7. 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

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 https://blogs.msdn.com/b/sqlsecurity/archive/2015/11/10/using-the-azure-key-vault-key-store-provider.aspx.

AlwaysEncryptedSample.zip

Comments

  • Anonymous
    November 05, 2015
    Dear, Maurycy Markowski. Sorry for my question is not in the context of the post. I’m doing some test with asp.net 5 beta8 and Sqlite, however sqlite is only running at runtime 1.0.0-beta8 clr x86. Perspectiva have any of Sqlite work with x64 architecture.

  • Anonymous
    November 06, 2015
    x64 support will be in in the RC1 version. In the meantime there is a workaround that you can do to make it work in beta8. Here are details on how do do it: github.com/.../138 Currenty, github is the best source of information regarding EF7. Project is located under the following link: github.com/.../EntityFramework Thanks, Maurycy

  • Anonymous
    November 13, 2015
    The comment has been removed

  • Anonymous
    November 13, 2015
    @Justin. Thanks a lot for the suggestion. I have added a small note at the end of the article to clarify that the instructions for using AKV provider are necessary.

  • Anonymous
    November 19, 2015
    Hi Maurycy Thanks for the post but for some reason when I try to use the alter statement to add the encrypted columns I get an error in SQL  Incorrect syntax near 'ENCRYPTED'. I note in your example "ENCRYPTED" has a red underline indicating an error is there anything I need to do in my database to make this work ? I'm using SQL 2014 with EF 6 Thanks Mark

  • Anonymous
    November 19, 2015
    @Mark Always Encrypted is not available for SQL Server 2014. You need to use SQL Server 2016 (currently in CTP3) or Azure SQL Database (where this feature is currently available in public preview). I hope this helps, -Raul Garcia

  • Anonymous
    November 19, 2015
    @Raul Thanks for your reply but I can only use 2014 due to client requirements. Do you know of a solution for this issue when using 2014 ? Thanks Mark

  • Anonymous
    November 23, 2015
    Does anyone have a solution for this in 2014 ? Thanks Mark

  • Anonymous
    November 23, 2015
    @Mark As Raul mentioned Always Encrypted is available exclusively on SQL Server 2016 - it is impossible to make it work on earlier versions of SQL Server. The reason for red squigglies is that I was using older version of SQL Server Management Studio. The database I was connecting to however, was 2016. Thanks, Maurycy

  • Anonymous
    February 05, 2016
    I let my Entity Framework app create the tables and add some Seed Data. Then I used SSMS to create the signed Master Key(stored in User Profile) and Encryption Key. Then I used SSMS to encrypt the SSN column. I added "Column Encryption Setting=Enabled" to my apps ConnectionString and it works great for all CRUD function. My question is, How does my EF application know to use the Master Key for SSN field and know where to get it from? It seemed too easy.

  • Anonymous
    February 28, 2016
    The comment has been removed

  • Anonymous
    December 09, 2016
    We have a fairly extensive Entity Framework application that was developed Database First. From your comment "EF will not be aware of which columns are encrypted" I take it that we cannot use Always Encrypted with our application in its present form. It would seem that using Model First would be the most sensible way forward to update our database and application. Is this correct?

    • Anonymous
      December 13, 2016
      Hi Steve,There should be no need to change your development workflow from Database First to Code First, to use Always Encrypted. In fact, Database First is, in general, easier to use with Always Encrypted, as you do not need to make any tweaks, e.g. to fix migrations or database initializers. What we mean by “EF will not be aware of which columns are encrypted” is that Entity Framework sees Always Encrypted column as any other columns (the EF model does not capture additional metadata about it). This is the case for both Database First and Code/Model First approach. We will try to make it clearer.Here is the link to another article on our blog that provides an example of using Database First with Always Encrypted: https://blogs.msdn.microsoft.com/sqlsecurity/2015/06/12/developing-web-apps-using-always-encrypted/. Thanks,Jakub