Blog

Encrypting Microsoft SQL Server databases and backups with Azure Extensible Key Management – a step by step technical guide.

This post covers some high-level discussion around securing SQL Server databases through Transparent Database Encryption and Backup Encryption, using Microsoft Azure to host the Asymmetric Encryption Keys in a Key Vault. It then delves into the technical steps necessary to achieve this level of encryption, covering both the Azure and SQL Server requirements so that the reader can perform full database encryption and backup encryption from scratch using Microsoft Azure to host the encryption keys. The target audience is senior DBAs and / or infrastructure personnel responsible for securing SQL Server. However, the introduction sections may be useful overviews for senior executives responsible for data protection in SQL Server.

Overview

With the advent of more stringent data security requirements over the last few years (e.g. GDPR), encrypting your SQL Server data has become more and more important as part of a layered, strength-in-depth approach to protecting against data compromise and the reputational and financial damage that can cause. Microsoft have added technologies to SQL Server version by version to support this encryption and with the advent of Microsoft Azure and Key Vaults, this now provides some compelling options for securely encrypting databases at minimal cost and with a lot less complexity than previously (although it is not to say that this is now easy!). 

Microsoft Azure provides the key vault capability. Whilst a full discussion of this is outside the scope of this post, it means that you can create HSM or software backed Asymmetric keys that can then be used by SQL Server to perform encryption. This removes the overhead and maintenance of keeping the keys on-site and provides DR capabilities by default since they are in a location separate to on-premises servers and take advantage of Azure redundancy.

There are many technologies for encrypting data in SQL Server, with this post focusing on those that we can implement easily, with little discernible user impact. As such we will be focusing on using Azure Key Vault with:

Transparent Database Encryption (TDE) – available from SQL Server 2008 (Enterprise Edition Only), this technology encrypts your database at rest, protecting from physical theft of data files and attempts to restore the database to another server, where the individual performing the restore may have sysadmin rights and therefore access to all the data (restores to the other server will fail without the decryption keys). This is done without requiring any developer or user changes to applications (hence the “Transparent” in the name).

Backup Encryption – available natively from SQL Server 2014 in both standard and enterprise edition, this technology encrypts your backups so they can’t be restored to another server without the relevant key. It can be layered with TDE to provide additional security and strength-in-depth. A backup that isn’t encrypted could be restored to another SQL Server instance and data compromised so this technology is vital to securing data that will likely be sent offsite for DR purposes.

Both technologies we will implement below have been improved over the years with, for example, SQL Server 2016 being able to use native Intel CPU hardware AES-NI support to mitigate TDE encryption overhead seen in earlier implementations of the capability. As such your ease of implementation will vary depending on the version of SQL Server being run (and whether your CPU supports the AES-NI instruction set), with most of my clients implementing these capabilities on SQL Server 2016 as of the time of writing.

Technical Implementation

Components Involved

  • Microsoft Azure – used to create the key vault that will store the asymmetric keys. Also used to grant access to the SQL Server to that vault via Azure App Registration.
  • SQL Server Connector for Microsoft Azure Key Vault – the software installed on your SQL Server that will allow it to talk to your Azure Key Vault: https://www.microsoft.com/en-us/download/details.aspx?id=45344
  • SQL Server – For this demo I will be using SQL Server 2016 Developer Edition

Assumptions

  • You already have an Azure account and subscription, and are familiar with navigating Azure
  • You have a dev machine with SQL Server Developer Edition
  • Your dev machine is internet facing and can communicate with Microsoft Azure

Azure Setup

Application Registration – to ensure that your SQL Server instance is granted access to your key vault (which we create later) you will need to create an application registration. This is done in your Azure Active Directory. To do this:

  • Go to your Azure portal and find your Azure Active Directory
  • From here go to App registrations
  • Click on “New Application Registration” and enter the details as shown below then click on “Create”. Note that in the real world you will want to implement a naming convention, such as SQL_HostMachine_InstanceName so you can have one app registration per instance and track them easily.
  • Once the application registration is in place you will want to record down the application ID which will be used later for part of the credentials to access the key vault. To view this, click on the application registration you just made which will bring up the following screen
  • At this point we will need to set a Key Password for the application registration. This will be used with the application ID for part of the credentials to access the key vault. To do this click on the keys option whilst in the application registration
  • Under the password section enter a description for the Key Password and select an expiry.
  • Once done hit save and copy the value that is shown. It will look something like this: RGYfrRY3FRh3UkPOmnJyHfRR2TtyuQWEh6H2MYUIfR2=
  • The credential we define later in SQL Server will use the application ID retrieved previously and this password concatenated together with the dashes removed. In this case it would look like the following string with the application ID (minus its dashes) highlighted with bold and the password part underlined:

ac20f6df77f5492787a2a708d12df63bRGYfrRY3FRh3UkPOmnJyHfRR2TtyuQWEh6H2MYUIfR2=

At this point our application registration is complete. Now we need to create a key vault to hold our Asymmetric keys

Azure Key Vault

To create an Azure Key Vault

  • Go to your Azure portal and find your Azure Key Vaults
  • Once in your key vaults click on “Add”
  • Fill the details out as shown here (ignoring the Access Policies which we will discuss next). Note that the standard pricing tier supports only software based Asymmetric Keys whilst the Premium tier supports HSM backed Asymmetric keys. A discussion of the difference of these is beyond the scope of this post but if you are implementing this in production HSM keys are recommended so the premium tier is likely to be used. For this implementation we will use standard software backed keys. Note the following screenshot has had the subscription name removed, yours will have your subscription name selected.
  • Once the details above are completed select the “Access Policies” section and select “Add New”. Enter the details as follows:

Configure From Template – SQL Server Connector

Select Principal – SQLDemoApp (the name of our app registration)

  • This should ensure that the key permissions are set to get, List, unwrap key and wrap key. Once checked select “Ok”, “Ok”, and then create the vault. NB: Set your location according to your need.

At this point the key vault is created and we need to now create a couple of keys inside the vault. To do this:

  • In Azure select your key vault, select the keys option and click on “Add”
  • Enter details as shown below. Note that the key type is software and cannot be changed because our vault is the standard tier. For HSM backed keys a vault with the premium tier is required.
  • Click on “Create” to create the key
  • Do the same for the backup key, as shown below

This completes the Azure configuration. We now have two keys, sqldemotdekey and sqldemobkpkey that can be used to encrypt SQL Server databases and backups.

SQL Server Setup

SQL Server Connector for Microsoft Azure Key Vault

The SQL Server Azure connector should be downloaded from https://www.microsoft.com/en-us/download/details.aspx?id=45344

Installation is a simple case of running the downloaded file on the SQL Server host machine (or all nodes of a cluster if deploying to a clustered environment) and stepping through the process. You will need to note down the path you install to if you install to a non-default path:

SQL Server Instance Configuration

The SQL Server instance configuration consists of enabling and installing the Azure provider previously installed, creating a credential so the DBA can connect to the Azure Key Vault through SQL Server Management Studio, then creating the Asymmetric keys in SQL Server ready for use in encryption. Once done additional logins are created to automate the opening of these keys by SQL Server and databases / backups can be encrypted. The detailed steps are:

  • Configure SQL Server to allow for Extensible Key Management Providers
USE master;  
GO  

-- Enable advanced options.
sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  

-- Enable EKM provider  
sp_configure 'EKM provider enabled', 1;  
GO  
RECONFIGURE;
GO
-- Disable advanced options.
sp_configure 'show advanced options', 0;  
GO  
RECONFIGURE;  
GO
  • Install the specific Azure provider. Note that if you installed this to a non-default location earlier you will need to change the path.
--Register The Cryptographic Provider

CREATE CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov  

FROM FILE = 'C:\Program Files\SQL Server Connector for Microsoft Azure Key Vault\Microsoft.AzureKeyVaultService.EKM.dll'; 

GO
  • Create the credential to allow access to the key vault in Azure and modify your login to map to it. Note the following items:

ChangeMeHere_ekm_cred should have the name changed to something relevant. So if your name is BobSmith it might be BobSmith_ekm_cred

The IDENTITY field here is named after the key vault name we used earlier. If you name your key vault differently put that name here.

The secret here is the combination of the Azure App Registration application ID and key password discussed earlier. Remember these fields need to be concatenated and the dashes removed. The value shown earlier was: ac20f6df77f5492787a2a708d12df63bRGYfrRY3FRh3UkPOmnJyHfRR2TtyuQWEh6H2MYUIfR2=

The alter login command needs modifying to your login, with the credential name changing to whatever you have named the credential.

--Create a credential that will be associated with the DBA login. 
--This will then allow the DBA login to access the key vault keys via the EKM connector 
USE master;  
CREATE CREDENTIAL ChangeMeHere_ekm_cred --Credential Name    
WITH IDENTITY = 'SQLDemoKeyVault', --Key Vault name    
SECRET = 'AppIDAndSecretValueHere' --Combo of the App ID and Secret 
FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov;  -- Add the credential to the SQL Server administrator's domain login   
ALTER LOGIN [Domain\Login]  ADD CREDENTIAL ChangeMeHere_ekm_cred;
  • Create the asymmetric keys to be used for encryption. This is dependent on the successful creation of the credential and login mapping just completed. Most times where there is an error the secret value of the credential has been entered incorrectly
  • You should only have to change this code snippet if you created your key vault keys with different names in the Azure configuration section.
--Key for Transparent Database Encryption 
--Now create the asymmetric key in the SQL Server master database that will reference the key in Azure 
--For this to work we need: 
--The application registration in Azure that the credential references in its password needs access to the key vault 
CREATE ASYMMETRIC KEY [sqldemotdekey]  
FROM PROVIDER [AzureKeyVault_EKM_Prov] --This is the Azure provider, so it will know how to comms to Azure Key Vaults  
WITH PROVIDER_KEY_NAME = 'sqldemotdekey', --This is the key name  
CREATION_DISPOSITION = OPEN_EXISTING; --Key for Backup Encryption 
--Now create the asymmetric key in the SQL Server master database that will reference the key in Azure 
--For this to work we need: 
--The application registration in Azure that the credential references in its password needs access to the key vault 
CREATE ASYMMETRIC KEY [sqldemobkpkey]
FROM PROVIDER [AzureKeyVault_EKM_Prov] --This is the Azure provider, so it will know how to comms to Azure Key Vaults
WITH PROVIDER_KEY_NAME = 'sqldemobkpkey', --This is the key name
CREATION_DISPOSITION = OPEN_EXISTING;

At this point if the key creation commands executed without error we have a working connection between SQL Server and the Azure Key Vault. The next section now links those keys to dedicated logins, which themselves will have dedicated credentials that mirroring the credential setup above. This will allow the keys to be used automatically by SQL Server for encryption operations. To configure these accounts the following steps should be completed.

  • Create the credentials to be used by the accounts. Only the secret value needs to be changed here as per the previous credential created.
--This bit creates the details that we will use to allow the keys to open automatically.
--In effect, the sequence is: 
--Credential has the key vault and access (App ID Secret Combo) defined 
--A login is created that maps to the Asymmetric key created in SQL Server 
--The credential is mapped to the login so the Asymmetric key > Login > Credential > grants access to the vault 
--We'll create totally separate credentials here for each login so the TDE and backup keys can theoretically be from different vaults if that level of separation is needed.

--TDE credential
USE master;
CREATE CREDENTIAL Azure_ekm_cred_tde --Credential Name
WITH IDENTITY = 'SQLDemoKeyVault', --Key Vault name
SECRET = 'AppIDAndSecretValueHere' --Combo of the App ID and Secret
FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov;

--Backup Credential
USE master;
CREATE CREDENTIAL Azure_ekm_cred_bkp
WITH IDENTITY = 'SQLDemoKeyVault', --Key Vault name
SECRET = 'AppIDAndSecretValueHere' --Combo of the App ID and Secret
FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov;   
  • Create the logins for each asymmetric key and then assign the credential to the logins can talk to the Azure Key Vault
USE master;
--Create the login for the TDE key
CREATE LOGIN TDE_Login
FROM ASYMMETRIC KEY [sqldemotdekey]
GO
--Assign the credential to the login
ALTER LOGIN TDE_Login
ADD CREDENTIAL Azure_ekm_cred_tde ;
GO

USE master;
--Create the login for the backup key
CREATE LOGIN BKP_Login
FROM ASYMMETRIC KEY [sqldemobkpkey]
GO
--Assign the credential to the login
ALTER LOGIN BKP_Login
ADD CREDENTIAL Azure_ekm_cred_bkp ;
GO

This completes the configuration of Azure, the SQL Server Connector and SQL Server itself. The keys can now be used to encrypt the databases and backups as shown below.

Encrypting a database

To encrypt a database using the Azure Asymmetric key:

  • Right click the database in SQL Server Management Studio, select “Tasks” and “Manage Database Encryption”
  • Select your encryption algorithm for the database encryption key, select your asymmetric key to encrypt the database encryption key and check the box to turn on database encryption as shown here:
  • Click “Ok”. This will then scan the database and encrypt it.

To backup a database using the Azure asymmetric key

Simply select the encryption option and choose and algorithm and asymmetric key on the backup options page of the SSMS backup dialog as shown here.

Summary

The above instructions should allow you to create a secure Azure Key Vault with asymmetric keys that can then be used to encrypt your databases and backups, natively, with no disruption to end users and with no additional cost. This can then be added to your other data protection strategies to provide defence in depth against data compromise, reducing the risk of reputational and financial damage and proving due diligence for audit.