Transparent Data Encryption with Azure Key Vault

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 Files\SQL Server Connector for Microsoft Azure Key Vault\Samples\. 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 Files\SQL Server Connector for Microsoft Azure Key Vault\Microsoft.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 BS\bschacht.
ALTER LOGIN [BS\bschacht]
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

About the author

Bradley Schacht

Bradley Schacht is a Principal Program Manager on the Microsoft Fabric product team based in Jacksonville, FL. Bradley is a former consultant, trainer, and has authored 5 SQL Server and Power BI books, most recently the Microsoft Power BI Quick Start Guide. As a member of the Microsoft Fabric product team, Bradley works directly with customers to solve some of their most complex data problems and helps shape the future of Microsoft Fabric. He frequently presents at community events around the country, is a contributor to sites such as SQLServerCentral.com, and is a member of the Jacksonville SQL Server User Group (JSSUG).

12 comments

Leave a Reply to Joe Bush Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  • Hello, Bradley.
    Thanks for perfect instructions, they really work!
    I’m able to encrypt my database with Azure Key Vault but I have problems with restoring it on another server. I did everything you mentioned on the second server: created logins and credentials, created symmetric key that I used before, etc. Now I can attach encrypted database taken from the first server and it works OK but I can’t restore database from backup file: “no backupset selected to be restored”.
    Is it a bug or I did something wrong?
    Best regards
    Maciej Ciurla, Unit4

    • I just want to make sure I have this right. You are able to detach and then reattach the database files from one server to another but you are unable to take a backup and restore it to another server?
      Was the backup taken before or after TDE was applied to the database? Also, have you tried doing the restore with T-SQL or just through the GUI?

  • I’ve checked it once again: yes, I can detach and attach database from one server to another after importing asymmetric key. I CANNOT restore backup of the same database using both GUI and T-SQL.
    I found news from MS https://support.microsoft.com/en-us/help/4024305/cumulative-update-4-for-sql-server-2016-sp1 that “Restore fails when you do backup by using compression and checksum on a TDE enabled database in SQL Server 2016” and applied CU4 to my servers but it still doesn’t work :-((

    • Sorry for the delay. I ran through everything on a couple servers in my demo environment and I was able to take the backup from the first server (with TDE enabled) and move it over to the new server. I tried a restore before running the appropriate scripts to connect to Key Vault and it failed as expected. I then ran through the scripts, did the restore and it worked flawlessly.
      You basically need to run through everything up to the point of changing the context over to the encrypted database on the secondary server before doing the restore. All the commands run at the master database should be performed ahead of trying to do a restore on the second server.

  • I know I am couple of years late 🙂 but just want to drop by and say “Thankyou”. This article really helped me to understand the overall picture much better :).

  • Hi Bradley,
    If I have an on-premises SQL Server, can you still use TDE with Azure Key Vault? If so, what are the extra steps you will need to configure? Do you need direct connectivity to Azure AD?
    Thanks
    Farhan

  • What a great article. Let me ask this. I set up SQL TDE on a SQL Server 2016 instance by creating a Database Master Key and a certificate right on the SQL Server. After that, I created a database encryption key in one of the databases using the certificate on the SQL server. After that, I enabled encryption on the database, works just fine. Now I’m tasked with encrypting the database using objects stored in the Azure Key Vault. Would I have to decrypt the database and then delete the database encryption key, certificate, and the database master key on the SQL server and then start the encryption process over starting with the information in this article to get things set up right?

Bradley Schacht

Bradley Schacht is a Principal Program Manager on the Microsoft Fabric product team based in Jacksonville, FL. Bradley is a former consultant, trainer, and has authored 5 SQL Server and Power BI books, most recently the Microsoft Power BI Quick Start Guide. As a member of the Microsoft Fabric product team, Bradley works directly with customers to solve some of their most complex data problems and helps shape the future of Microsoft Fabric. He frequently presents at community events around the country, is a contributor to sites such as SQLServerCentral.com, and is a member of the Jacksonville SQL Server User Group (JSSUG).

Follow Me