Always Encrypted Feature in SQL Server 2016

• Download source - 459.6 KB
• Download source (scripts) - 2.5 KB

Idea

The idea is to implement the latest and first of its kind “Always Encrypted” feature provided by Microsoft SQL Server 2016 designed to encrypt sensitive data on backend database engine.

Summary

Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine (SQL Database or SQL Server). As a result, "Always Encrypted" provides a separation between those who own the data (and can view it) and those who manage the data (but should have no access).

Process

In this article, we are going to:

1. Create a simple database table and connect the same with ASP.NET MVC application. Scaffold controller and views to populate data. If you are familiar with this process, go directly to step 2.
2. Encrypt sensitive data columns in database table using Always Encrypted feature in SQL server 2016.
3. Make necessary changes in ASP.NET MVC application to automatically encrypt/decrypt the data.

Create a Simple Database Table and Connect the Same With ASP.NET MVC Application

The first step is to create a new database “AlwaysEncryption” with the table name “Customer”. Assuming Email and SSN columns hold sensitive information.

• Download Customer.zip

Now that the database is ready, we will create a new ASP.NET Web Application “AlwaysEncryption”. In this sample, I’m going to use Visual Studio 2015.

Select MVC from the New ASP.NET Project template list. As we are not focusing on authentication, click on “Change Authentication” button to select “No Authentication” option and click "OK".

This is how the basic bootstrap MVC solution looks like:

Now go ahead and add a new Entity Data Model file to our Models folder:

From the “Entity Data Model” Wizard, choose “EF Designer from database” and click “Next”.

From the next wizard screen, choose “Microsoft SQL Server” as data source and click “Continue” to build the connection.

Using “Connection Properties” wizard window to connect to the “AlwaysEncryption” database. Make a quick “Test Connection” to succeed and click “OK” to continue.

From the next wizard screen, select “Yes” radio option to store sensitive SQL credential information within connection string. Then provide the context name “AlwaysEncryptionContext” that should go directly into connection string attribute in web.config file.

Click “Next” to choose Entity Framework version for the application. In our case, I choose EF 6.x option.

Click “Next” to choose our “Customer” table and name the Model Namespace and click “Finish” to import entity model.

Visual Studio 2015 will show a couple of security warning dialog boxes before actually running the Text Template (T4) file. Click “OK” to generate context as well as entity model classes.

This is how our solution looks after the import.

Scaffolding Controller with Views using Entity Framework

Now right click on the Controller folder and add “New Scaffolding Item…” to create MVC 5 Controller along with views using Entity Framework.

Warning: Always first build the application after importing entity model from database before scaffolding a controller or view.

Select the “Customer” Model class and “AlwaysEncryptionContext” as Data Context class and name the new controller as “CustomersController”. Click “Add” to create controller as well as views to perform all CRUD operations.

This is how our solution looks like after scaffolding controller and its corresponding CRUD views:

Now, set the default controller and action in route configuration file to our new Customers controller with Index action method.

routes.MapRoute(
    name: "Default",
    url: "{controller}/{action}/{id}",
    defaults: new { controller = "Customers", action = "Index", id = UrlParameter.Optional }
);

Go ahead and run the application to populate some records in Customer table.

Now go to SQL Server Management Studio and run the following query to list all Customer records. This will display all data in plain text.

To Encrypt Sensitive Data Using Always Encrypted Feature in SQL Server 2016

To encrypt sensitive data in Microsoft SQL 2016, go ahead and right click on “Customer” table to select “Encrypt Columns…” from the context menu.

This will show Always Encrypted wizard with “Introduction” screen, click “Next”.

Now we get “Column Selection” Wizard screen, this is where we choose all data columns from the selected table that need to be encrypted.

Here, we selected Email and SSN columns to be encrypted. Notice that the “Encryption Type” column is set to “Deterministic” and choose default or auto generated Column Encryption Key (CEK).

Selecting Deterministic or Randomized Encryption Type

Always Encrypted supports two types of encryption: Deterministic Encryption and ​​​​​​ Randomized Encryption.

Deterministic encryption uses a method which always generates the same encrypted value for any given plain text value. Using deterministic encryption allows grouping, filtering by equality, and joining tables based on encrypted values, but can also allow unauthorized users to guess information about encrypted values by examining patterns in the encrypted column. This weakness is increased when there is a small set of possible encrypted values, such as True/False, or North/South/East/West region. Deterministic encryption must use a column collation with a binary2 sort order for character columns.

Randomized encryption uses a method that encrypts data in a less predictable manner. Randomized encryption is more secure, but prevents equality searches, grouping, indexing, and joining on encrypted columns.

Use deterministic encryption for columns that will be used as search or grouping parameters, for example a government ID number. Use randomized encryption, for data such as confidential investigation comments, which are not grouped with other records and are not used to join tables.

After selecting the columns with the desired encryption type, click “Next”, this will display “Master Key Configuration” Wizard screen. In order for us to encrypt columns using CEK, we need to have a Master Encryption Key (MEK) which is stored outside of the database in either Windows Certificate store or Azure key vault.

Here, we auto generate MEK and choose to store the same in Windows certificate store under Current User.

Click next to view “Run Settings” Wizard. This screen warns users not to perform any read/write operations onto the table while encryption and decryption is in process. This may lead to potential data loss. SQL Server 2016 recommend to run this settings during planned maintenance window.

Hit “Next” to view the “Summary” wizard screen. Click “Finish” after verifying the settings.

This will show the "Results" wizard screen with details. In our cases, it passed all three steps:

1. Generated a new Column Master Key CMK in windows certificate
2. Generated new Column Encryption Key CEK, and
3. Performed encryption operation on selected columns on the Customer table

Now again, run the select script to see the selected columns encrypted. Perfect, isn’t it?!

Encrypting columns in a table will definitely alter the table schema but not like what we all expected. There will be no change to the column data types. If you closely look at the table create script, we can see COLLATE along with ENCRYPTED WITH CEK and the ALGORITHM.

Note: As of now, there is only one encryption algorithm “AEAD_AES_256_CBC_HMAC_SHA_256” provided by SQL Server 2016.

• Download CustomerAfterEncryption.zip

Make Necessary Changes in ASP.NET MVC Application to Automatically Encrypt/Decrypt the Data

Now, re-run our MVC application to view how encrypted data is shown in our views. The strange thing is that it shows the following error message. The application is expecting string data type in Email column but it has been fed with encrypted data stream as an array of bytes.

To fix this, we need to update our connection string to enable “column encryption settings”. This will allow our application to automatically encrypt/decrypt the necessary columns when connecting to the database.

column encryption setting=enabled

This is how our connection string looks like after the update.

Now run the application once again to view columns automatically decrypted/encrypted. The application makes use of MEK as well as CEK to decrypt/encrypted columns.

If you closely look at the "Always Encrypted Keys" settings under database Security:

Right click on CEK to generate its create script:

Right click on CMK to generate the create script. Here, you can see the path to Windows Certificate:

If you go and look for "Always Encrypted Certificate" under Current User…

Here are the certificate details when you double click it.

Suppose you take a backup of the database and restore the same on other machine/server. Having the MVC application targeted to the new database… What do you think, the application would retrieve the data if we restore the database onto another machine or server?

The answer is NO.

Why? Because, although we may have the database restored from the backup file and have access to all CEKs to encrypt/decrypt columns, we do not have the Master Encryption Keys which are stored externally on Windows Certificate store. And to get the MEK certificates, we need to contact the owners.

We will get an error when doing this as the application could not able to decrypt/encrypt a column using keys provided.

Check my other article (yet to be published) on how to export and/or import Master Encryption Key from/to Windows Certificate store.

Click here to learn more about "Always Encrypted" feature in SQL Server 2016.

I hope you had a wonderful time learning new things. Let me know if you have any suggestions or improvements.

Feel free to rate this article, bookmark and leave comments for better clarification.

History

• 6^th July, 2016: Initial version