Microsoft introduced Transparent Data Encryption (TDE) to SQL Server with the 2008 release providing a simple, easy way to add an extra layer of protection to our data. TDE is not unique to SQL Server and hasn’t changed much since being introduced into the product. With Azure becoming more and more popular I get requests from customers fairly frequently asking to talk about storing the certificates in the cloud. Enter Azure Key Vault integration.

TDE is defined at the database level in SQL Server and upon enabling the feature will encrypt your database files, log files, tempdb and all future backups (past backups stored on the server’s backup location will not retroactively be encrypted). A separate post called What is Transparent Data Encryption will be linked later explaining the benefits of the technology and why customers should take advantage of it.

This post will focus on storing the asymmetric key for encryption in an extensible key management (EKM) module adding an extra layer of protection by separating the encryption keys, in our scenario, Azure Key Vault.

Register the Application in Azure Active Directory

The first step is going to be to register the SQL Server application in Azure Active Directory. To begin, login to the Azure portal (https://portal.azure.com) and navigate to the Azure Active Directory service. From the navigation or the Overview blade click App Registrations.

At the top of the blade click New Application Registration.

Enter a name for the application, select Web App/API for the Application Type and enter a Sign-On URL. For the purposes of TDE the Sign-On URL can be any generic, valid URL you choose, such as http://microsoft.com. After filling in all the fields click the Create button at the bottom of the blade.

Upon completion the portal will return to the App Registrations blade. Click on the newly created application from the list. In a large organization the list may contain many entries and it is useful to either leverage the search functionality or change the filter from All Apps to My Apps to limit the result set.

There are two pieces of information to note: the Application ID and the Key. The Application ID can be found on the main blade in the Essentials section. The key will need to be generated by navigating to Keys, found under API Access in the Settings navigation.

On the Keys blade fill in the Description field, select a duration and click Save. Make sure to note the key after clicking save, it cannot be recovered after navigating away from the Keys blade and will need to be regenerated if it is lost.

Application ID: 5a064274-691b-42d7-844a-05c059f37f0c

Key Value: IKIK1OsQaXZVQttynwTbo0zjDlYaOPMDb4JnFJInwy8=

Configure Azure Key Vault

In the Azure Portal navigate to the Key Vaults service. Select the key vault you would like to use to store the encryption key or create a new key vault if necessary. On the Key Vault navigation or overview blade select Keys.

On the Keys blade click Add. In the resulting blade choose the option of Generate, provide a name and click Create.

Next, grant access to the Azure Active Directory application created in the previous section. Return to the Key Vault overview blade and navigate to the Access Policies.

The click Add New button to create a new access policy. In the resulting blade the first setting will be to set the principal. A new blade will open allowing a search of the entire Azure Active Directory, both users and applications will be listed. Search for the application created in the previous section. The one created in the screenshots of this post was called “SQLServerTDE”. From the Configure from Template (optional) drop down select the SQL Server Connector option. This will automatically select the Get, List, Wrap and Unwrap permissions on keys while providing no permissions on Secrets or Certificates. Alternatively, selecting Get, List, Wrap and Unwrap under the Key Permissions drop down will result in the template option of SQL Server Connector being populated. Both have the same end result. Click OK to close the blade and create the access policy. On the Access Policies blade click Save.

Enable TDE on SQL Server Database

To this point the following steps have been completed:

1. Created an application in Azure Active Directory
2. Created a key in Azure Key Vault
3. Registered the application and granted appropriate permissions in Azure Key Vault

Now it is time to launch SQL Server Management Studio, connect to a database server and enable TDE on a database. For purposes of this blog we will be using a database called MyBlogDatabase running on SQL Server 2016 on the server BS-SRV-SQL01. A connection to this database server has already been established. The first step will be to open a new query window and set the context to the master database through the GUI or by running USE master. Here we will ensure that the server is configured to use the EKM provider discussed in the opening section of this blog. To do so run the following commands to show the advanced options then enable the provider.

USE master

GO

sp_configure ‘show advanced options’, 1

GO

RECONFIGURE

GO

sp_configure ‘EKM provider enabled’, 1

GO

RECONFIGURE

GO

The output will look similar to the screenshot below depending on what the existing server settings were. Either way both messages should say the setting was changed to 1.

Next, download and install the SQL Server Connector for Microsoft Azure Key Vault if it is not already installed on the SQL server. In the scenario described in this blog the installation will take place on the server BS-SRV-SQL01, not on the workstations connecting to SQL Server or any application servers. It is required only on the actual server running SQL itself. Several sample files are included in the installation which are very useful. They provide the framework code for registering the key vault provider with SQL Server. If the default installation options are used the sample files can be found at C:Program FilesSQL Server Connector for Microsoft Azure Key VaultSamples. Much of the code that follows is derived from those sample files.

In SSMS while still connected to the master database, run the following command to create the cryptographic provider that will allow SQL Server to communicate with the Azure Key Vault service. Were a different EKM being used this would be to register the provider for that service/hardware. As with the sample code location, the following will only work if the installation was done to the default location. If the installation of the connector was completed with a different installation path be sure to change the path in the code before running.

CREATE CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov

FROM FILE = ‘C:Program FilesSQL Server Connector for Microsoft Azure Key VaultMicrosoft.AzureKeyVaultService.EKM.dll’

GO

Next, create a credential for the Azure Active Directory Application in the first section of this post. Be sure to note that the SECRET portion of the command has all the hyphens removed from the Application ID. If the hyphens are not removed there will be failures in later steps. Another thing to note is that the sample files included with the Key Vault connector (2. Setup Credentials.sql) use some terminology that is no longer in use in Azure. Where there are references to Azure Active Directory (AAD) Client, those are what we see in the portal today as the Application ID and a Secret is what we see today as a Key. Therefore Client = Application and Secret = Key.

In the following command note the following:

1. IDENTITY = Name of the Azure Key Vault (bradschacht in my case)
2. SECRET = Azure Active Directory Application ID (without hyphens) + Key Value

A refresher from earlier on my Azure AD Application values…

Application ID: 5a064274-691b-42d7-844a-05c059f37f0c

Key Value: IKIK1OsQaXZVQttynwTbo0zjDlYaOPMDb4JnFJInwy8=

The command for my environment looks like this:

CREATE CREDENTIAL EKM_KeyVault

WITH

IDENTITY = ‘bradschacht’,

SECRET = ‘5a064274691b42d7844a05c059f37f0cIKIK1OsQaXZVQttynwTbo0zjDlYaOPMDb4JnFJInwy8=’

FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov

GO

Next, we will add the credential to the administrators account. This is generally the login of an administrator or DBA. In my demo environment my admin account happens to also be my account, so I will add the credential to BSbschacht.

ALTER LOGIN [BSbschacht]

ADD CREDENTIAL EKM_KeyVault

GO

Next, be sure to log in with the account that the credential was just added to. Before each command I will make note of the pieces that need to be switched out for your environment. I will be assuming that you have the same provider name (AzureKeyVault_EKM_Prov) and credential name (EKM_KeyVault) as I do though. If you chose different names for those in the scripts above then replace any references to those items below as well.

To start be sure the context is the master database.

USE master

GO

Replace:

1. Optional – Name of the asymmetric key in the database: TDE_KEY
2. Name of the asymmetric key in Azure Key Vault created in the Configure Azure Key Vault section above: MyTestKey

CREATE ASYMMETRIC KEY TDE_KEY

FROM PROVIDER AzureKeyVault_EKM_Prov

WITH

PROVIDER_KEY_NAME = ‘MyTestKey’,

CREATION_DISPOSITION = OPEN_EXISTING

GO

Replace:

1. Optional – Name of the login: TDE_Login
2. Optional – Name of the asymmetric key from the previous command: TDE_KEY

CREATE LOGIN TDE_Login

FROM ASYMMETRIC KEY TDE_KEY ;

GO

Replace:

1. Optional – Name of the credentials: Azure_EKM_TDE_Cred
2. IDENTITY value with the name of your key vault
3. SECRET with your Azure Active Directory Application ID (without hyphens) + Key Value

CREATE CREDENTIAL Azure_EKM_TDE_Cred

WITH IDENTITY = ‘bradschacht’,

SECRET = ‘5a064274691b42d7844a05c059f37f0cIKIK1OsQaXZVQttynwTbo0zjDlYaOPMDb4JnFJInwy8=’

FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov

GO

Replace:

1. Optional – Name of the login: TDE_Login
2. Optional – Name of the credentials: Azure_EKM_TDE_Cred

ALTER LOGIN TDE_Login

ADD CREDENTIAL Azure_EKM_TDE_Cred

GO

At this point the setup at the server level is complete and it’s time to switch over to the database. Change the context to MyBlogDatabase for the next set of commands.

USE MyBlogDatabase

GO

If the name of the asymmetric key was created with a name other than TDE_KEY replace that line in the command below.

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER ASYMMETRIC KEY TDE_KEY

GO

Finally, enable TDE on the database.

ALTER DATABASE MyBlogDatabase

SET ENCRYPTION ON

GO

Check and ensure transparent data encryption is enabled. Notice that not only is the MyBlogDatabase encrypted, but tempdb is as well.

SELECT

d.name, d.is_encrypted, dek.encryption_state

FROM sys.dm_database_encryption_keys dek

INNER JOIN sys.databases d ON dek.database_id = d.database_id

Depending on the size of the database the actual process of encrypting the data could take some time. The encryption_state field in the result set will change to 3 when the process is complete. The possible values for this field are:

0 = No database encryption key present, no encryption

1 = Unencrypted

2 = Encryption in progress

3 = Encrypted

4 = Key change in progress

5 = Decryption in progress

6 = Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)

Summary

Transparent data encryption is a great tool for securing your database and backups. While there are several options for managing the encryption keys, Azure Key Vault is definitely an option to consider. It separates the asymmetric key into a managed service that helps control access.

Pricing for Azure Key Vault is very inexpensive at just dollars per month.

Microsoft’s Extensible Key Management Using Azure Key Vault Documentation