I recently wrote a post about using Transparent Data Encryption (TDE) with Azure Key Vault as an alternative to managing certificates. Today’s post will explore using SQL Server’s Always Encrypted functionality with Azure Key Vault. Always Encrypted, as with TDE, can use Windows certificates or what an external storage location such as a Hardware Security Module (HSM) or Azure Key Vault.

Where TDE encrypts data at rest (aka on disk), Always Encrypted will encrypt data on disk and in transit to the application where it will then be decrypted. In order to decrypt the data the client application must have access to the master key. The client application is the point where the call to the database actually occurs. In the case of running queries in Management Studio, the workstation where SSMS is running needs access to the master key. In the scenario where a user is connecting to an application in a web browser, the application server hosting the application would need access to the master key, not the workstation where the end user is located. However, if there is a standalone application that is installed on all workstations and the call is coming from inside the local application, then the end user workstation would need access to the master key.

If the key is stored within the Windows Certificate store or an on-prem HSM then internet connectivity does not come into the picture for decryption. However, when using Azure Key Vault the workstation or server hosting the application will need access to Azure Key Vault. The documentation page Access Key Vault Behind a Firewall explains the access required for authentication.

This post will walk through creating the column master key in Azure Key vault, creating the column encryption key, then encrypting the data in a table using this key hierarchy. When creating a column master key in SQL Server it is possible to use an existing key or have the system generate a key. In any production environment it is best to have a security administrator generate the key ahead of time and secure it in Key Vault so the DBA doing the setup in SQL Server doesn’t have access to the key itself allowing them to then decrypt any data they would like to. Division of responsibilities where one person creates and manages the key (security administrator) and another person maintains the database (DBA) is ideal if it is necessary to restrict the DBAs from having access to encrypted data. This is important to many of my customers when dealing with PII and sensitive government data.

The general steps will be:

1. Provision a new Azure Key Vault (optionally, use an existing key vault)
2. Generate a new key (done by the security administrator)
3. Create the master encryption key (done by the DBA)
4. Create the column encryption key (done by the DBA)
5. Apply encryption to the appropriate column(s) (done by the DBA)

Provision a New Azure Key Vault

Begin by navigating to the Azure Portal. Click the New button in on the upper left-hand corner of the Azure portal. Select Security + Identity from the Azure Marketplace column, and select Key Vault from the Featured column.

Complete the Create key vault blade by entering a name for the key vault, subscription, resource group and location. Click Create to provision the key vault.

If Key Vault is not shown in the Featured column select See all in the upper right-hand corner of the New page and locate Key Vault in the Security + Protection section of the Security + Identity blade.

Generate a Key in Azure Key Vault

After provisioning is complete click the More services button on the portal navigation located on the left-hand side of the screen. Click Key Vaults, found in the Security + Identity section. Alternatively, type Key Vaults in the search box at the top of the service menu to narrow the list of services.

From the list of key vaults click on the name of the vault where you would like to use to manage the Always Encrypted keys.

From the navigation click Keys.

Click Add from the list of options at the top of the Keys blade.

On the Create a Key blade select Generate from the Options drop down and enter a name for the key. Click Create.

On the Keys page, click the Refresh button and ensure the new key appears with a status of Enabled.

Before SQL Server will be able to access the keys we need to ensure the appropriate access policies are in place. They required permissions are create, get, list, sign, verify, wrap and unwrap.

Close the Keys page to return to the Key Vault overview. Click on Access policies from the navigation list. A list of users will be shown. Select your account from the user list.

On the Key permissions drop down ensure create, get, list, sign, verify, wrap and unwrap are all selected. By default sign, verify, wrap and unwrap will not be selected. Click OK.

Finally, click Save on the Access policies page.

Create the Encryption Keys

Each of these steps in isolation are relatively simple and straightforward. Before we get started be sure to fire up SQL Server Management Studio (SSMS) and connect to your favorite internet connected SQL Server. I recently ran into an issue with SSMS 17.1 would not connect to Azure Key Vault and this appears to be resolved with the SSMS 17.2 release.

Column Master Key

First, expand the database where Always Encrypted will be enabled, expand Security and finally expand Always Encrypted Keys. Notice two folders exist, Column Master Keys and Column Encryption Keys. Right-click on the Column Master Keys folder and select New Column Master Key to launch the New Column Master Key wizard. Alternately, right-click on the Always Encrypted Keys folder and select New Column Master Key. Both launch the same wizard.

Provide a name for the key. Change the Key store option to Azure Key Vault in the drop down. If prompted, click the Sign In button and authenticate with your credentials.

Upon successful authentication to the Azure environment, select the appropriate subscription, key vault and key from the options presented. Click OK to close the wizard.

Note: As an alternative to the previous section, Generate a Key in Azure Key Vault, a new key can be created from the New Column Master Key wizard on this screen by clicking the Generate Key button.

Expanding the Column Master Keys folder in the SSMS navigation tree will show the newly created key.

Scripting the key reveals, as expected, that there is no key stored locally, but instead the key resides in Azure Key Vault at the URL shown.

Column Encryption Key

Next, right-click on the Column Encryption Keys folder and select New Column Encryption Key to launch the New Column Encryption Key wizard. Alternately, right-click on the Always Encrypted Keys folder and select New Column Encryption Key. Both launch the same wizard.

Provide a name for the key and select the appropriate column master key to protect the column encryption key. Click OK to close the wizard.

Expanding the Column Encryption Keys folder in the SSMS navigation tree will show the newly created key.

Apply Encryption to an Existing Table

Now that the keys are configured and created it is time to encrypt our data.

The sample data in this post is in a table called Customer and contains three records each consisting of an ID, name and a social security number. The social security number is personally identifiable information and should be encrypted so only authorized applications and users can view the data.

To start, locate the table containing the columns to be encrypted in the SQL Server Object Explorer in SQL Server Management Studio. Right-click the table and choose Encrypt Columns. The Always Encrypted wizard will launch. It is possible to generate keys through the wizard but it provides much less control than the above process or generating and uploading keys using PowerShell.

If displayed, click Next on the Introduction page.

On the Column Selection page click the check box next to the column(s) to be encrypted. In this example, that is the SocialSecurityNumber column. Choose either deterministic or randomized in the Encryption Type and select the column encryption key created in the previous section in the Encryption Key column. Notice in the Encryption Key drop down menu there is an option called CEK_Auto followed by a number and the text (New). Choosing this option will generate a new column encryption key rather than leveraging the key that has already been created. Click Next.

Because we chose to use existing keys there is no configuration necessary on the Master Key Configuration page. Simply click Next. Had the CEK_Auto option been selected on the prior screen the Master Key Configuration page would have looked similar to the wizard in the Column Master Key section of this post.

On the Run Settings page click Next. If the table contains are large amount of data or the table is actively being written to it is advisable to change the option to Generate PowerShell script to run later and schedule the encryption to run during an off hours maintenance time.

On the Summary page click Finish. The encryption process will begin. The wizard will automatically proceed to the Results page upon completion of the encryption process. If any errors occur click the link to view the Always Encrypted Wizard Log Report. Otherwise, click Close.

Rerunning a SELECT command on the table will reveal the SocialSecurityNumber column is now encrypted.

A look at the table definition shows the changes that have been made to the DDL. Notice the data type is still listed as CHAR(11) but the data is being stored in binary format. Here we can also see the column encryption key being used and the encryption type is set to randomized.

It is worth noting that the table can be created empty with the column encryption specified, it does not need to be created and then have encryption applied using the Always Encrypted wizard.

Summary

Azure Key Vault is just one of many ways to create and store the encryption keys for SQL Server Always Encrypted. The method explored here shows just one set of steps to get to the same end result of encrypted data stored in SQL Server.

You may have noticed that when running a query in SSMS the data was encrypted. You may want to view the decrypted data inside SSMS. Before showing how to do this it is important to understand how the current implementation of Always Encrypted handles authentication of the master key. When an application initiates a connection to SQL Server the column master key will be validated. That means, in this scenario, it will need to authenticate against Azure Key Vault. That master key will be cached in the application for the life of the connection. This keeps the application from needing to make a trip to the certificate store or Key Vault for every single query that is run causing potential performance issues. That means in order to get the decrypted data in SSMS we will need to close the application and reopen it, or at a minimum, disconnect the current connection.

When connection to the server expand the Options on the Connect to Server dialog, switch to the Additional Connection Parameters page and enter the text Column Encryption Setting = Enabled. Then, click Connect.

Now, when running queries in SSMS against a table with encrypted data you may be prompted to authenticate to Azure before being shown the decrypted data.

Note: If using Azure Key Vault. There will be no authentication if using the local Windows Certificate Store which was not covered in this post.