always encrypted | Versatile Digital DNA

This article focuses on the steps on how to configure the Always Encrypted (AE) feature for SQL Databases using Azure Key Vault as the key store provider; which is still in preview at the time of this writing.

In order to try out what I have explained here you should have January 2016 preview or upper version of SSMS.

You can download the January 2016 preview version here

Please note this is not an article to discuss about what AE is and how it works nor an introduction to Azure Key Vault. I recommend to read these following posts before continuing this article in order to get the full understanding.

Settings up Always Encrypted in SQL Database using Azure Key Vault

In order to set up the Always Encrypted, first we need to create Column Master Key (CMK) and Column Encryption Key (CEK).

Creating CMK.

Navigate to the SQL Databases using SSMS. Expand the database node and then expand the Security section. Under the Security section you will see Always Encrypted Keys node. Right click on that and you will see the menu, shown in the below image.

Select ‘New Column Master Key’ and you will get the below dialog box. Name your CMK (cmk1) and then select the Azure Key Vault as Key Store. Click the Sign In button to login to Azure subscription.

You should use an Azure account with at least co-admin privileges to the Azure subscription where the Azure Key Vault is. After signing in you will see the list of Azure Key Vaults in the drop down, choose the one you want to use as KMS for the CMK.

Click Generate Key and let the Azure Key Vault generate a key for you. This will be your CMK.

Creating CEK.

As shown in the below image right click on the ‘Column Encryption Keys’ node and create a CEK.

You will get a dialog box shown below. Name the CEK and choose the CMK created above.

When you select the CMK stored Azure Key Vault, you will see the Azure login dialog. Here you should enter the credentials of the user who has permission to access the particular key in the vault. If not you will get an error analogous to the below dialog.

In order to give the permission to the key from to the specific user, execute the following PowerShell script.

Note: When you create the CMK you login to Azure using a co-admin credential who has access to Vault, but that does not make him/her able to manage the keys created. So using the same credentials when creating the CEK might produce the above error. You should give permissions to the user account which is used to access the keys in the Vault.

Now the setup of creating CMK and CEK are done. Let’s create a table.

Execute this script in order to create the table in the database. (click on the images to enlarge)

We have created a table with 3 columns 2 of them are encrypted using CEK1 with Always Encrypted Feature. This completes the setup in the SQL Database.

You can query the database for more information about the Always Encrypted settings. You will find some useful queries in this article.

Application Setup

We are done with all the setups in the SQL Databases, in order to work with the Always Encrypted feature enabled database we need a .NET 4.6 application.

Also since we use Azure Key Vault as the key store provider, we need to implement a custom provider, this could be done by implementing the SqlColumnEncryptionKeyStoreProvider abstract class in System.Data.SqlClient.

You can find an implementation here.

Below is the core code snippets of the implementation.

The connection settings goes like below.

Implementing the GetToken method

Only AAD identities can access Azure Key Vault, so now our application needs an AAD identity in order to communicate with the Azure Key Vault.

So we need an AAD application which has permissions to the Azure Key Vault in order to access Azure Key Vault programmatically.

Create an AAD app and the App Id and the App secret key.

And the set the permission for the app in the Key Vault in using PowerShell.

So the GetToken method goes as below.

In the above snippet the provider name is AZURE_KEY_VAULT, you can retrieve this information by executing the following TSQL command.

After all the setups the application and the database both are ready with Always Encrypted enabled with Azure Key Vault.

Introduction

SQL Server 2016 is yet to be released, but you can download the CTP versions. The mostly anticipated and marveled feature is Always Encrypted. Let me discuss few bits of this feature before getting into the technical side of it.
I got to know this right after reading an article about Microsoft had applied some court case to safeguard the information of one of its users, from the US laws. This is highly appreciated and I personally feel that Microsoft is at the forefront of protecting customer data. And I made a tweet.

If customer can secure their data without any control of public cloud vendors, even in the situations like powerful unauthorized people gaining access to your data results in reduced data theft. And it solves a headache for the public cloud vendors as well.

How it works

SQL Server 2016 Always Encrypted is a feature which allows the encryption and the decryption of the data in the client side, rather than in the database server itself. Since the encryption happens in the client side using a client driver data is secured not only at rest but also at transit, this makes the feature takes its pride name Always Encrypted.

Always Encrypted works as follows.

First we have to create Column Encryption Keys (CEK) and Column Master Keys (CMK) in the Always Encrypted section of the database
CEKs are symmetric keys and CMKs are asymmetric keys.
CEKs are stored in SQL Server where CMKs are stored outside the SQL Server. Read this MSDN article to get information about how to create these keys.
Create database with the table and specify the columns to be encrypted. Note that the encryption type (deterministic or randomized), encryption algorithm and the CEK to be used are specified.
In the demo I’ve created the CMK in the local certificate store of the machine, but you can keep the CMK wherever possible. Because SQL Server stores only the meta data of the CMK.
Now the database is ready. We need .NET 4.6 client application to access the data in the Always Encrypted enabled database. I summarized everything in this image.

Application sends an INSERT statement, driver intercepts the statement and identifies the database it talks is an Always Encrypted feature enabled database. This identification happens because of the connection string property Column Encryption Setting=Enabled. So the driver asks the database to send the details of the encryption for the specific table.
SQL Server returns column details, encrypted values of the CEK, CMK name and the path.
Client driver retrieves the CMK using the meta received from the SQL Server. In this step driver gets the private key of the CMK, which is used to decrypt the encrypted CEK. (CEK is encrypted using CMK’s public key during the creation of CEK in the SQL Server, also the CEK is signed by the private key of the CMK) SQL Server does not store the CMK’s private key.
Client driver encrypts the data using the decrypted CEK and send it to the SQL Server.

Read operations also work similar as SQL Server send the encrypted data along with the encryption details and CMK meta data information. Client driver then retrieves the CMK decrypts the CEK and the decrypts the data.
Client driver implements possible caching of the keys for performance.

Sample .NET application code for the above table