Implementing Transparent Data Encryption (TDE), Backup Encryption, Always Encrypted, Symmetric key and Asymmetric keys all require that a final secret is stored at some point which protects the encryption key(s) used to secure the data. The main difference between a symmetric and asymmetric key is that to perform both the encryption and decryption the same secret is used for a symmetric key, where as an asymmetric key has a secret for each method. This means that in order to share an encryption securely, a asymmetric key is needed so that the ability to use the encryption method can be public, while the decryption is private.

Managing the final secrets used can quickly become an administrative burden and in the past this was only reduced by using expensive hardware or software based extensible key management (EKM) systems. Azure Key Vault allows for a secure cloud based EKM which is easy to setup and greatly streamlines key management.

Setup

We'll split the setup into two sections, with the first section being optional if a key vault has already been configured.

Setup a new Azure Key Vault (Optional if already present)

Step 1.

Navigate to the Azure portal and create or sign in to your account.

https://portal.azure.com/

Step 2.

Navigate or search for a new key vault resource.

https://portal.azure.com/#create/Microsoft.KeyVault

Step 3.

Complete key vault creation. Opting for the premium pricing tier allows for hardware backed key storage which can be a requirement for some companies. A hardware based key vault is not vulnerable to software related issues.

The name, subscription and region should be supplied. The key vault name will need to be supplied to anyone wanting to access it.

Step 4.

Create Registered Application in AzureAD tenant

• Navigate to AzureAD tenant.
• App registrations.
• Click new application registration
• Enter Name AppKeyVaultSQL01
• Application type as Web app/API
• Sign-on URL as https://AppKeyVaultSQL01
• Create (Make a note of the Application ID without hyphens)

bd34364853ae473d802dc6d25bcab7e9 

Step 5.

Add access policy for the key vault

• Navigate to relevant Key vault.
• Access policies
• Add New
• Configure from template. SQL Server Connector.
• Select Principal. AppKeyVaultSQL01
• Ok, Save

Step 6.

Add key for the registered app.

• Navigate to AzureAD tenant.
• App registrations.
• Change drop down to all apps, select AppKeyVaultSQL01.
• Settings
• Keys
• Key Description AK01
• Expires As Req
• Save (Make a note of the value field as this is the only time it will be displayed).

rPV6zY9l49a2GV/sUITtlrv5uYZDhZuF9hOkIndFTuY=

Step 7.

Add a key to the vault.

• Navigate to relevant Key vault.
• Keys
• Generate add name (no more than 26 characters) and additional details as needed.
• Create

Configure SQL Server for use with the key vault

The key vault name in the example above is KeyVaultSQL01 with the secret consisting of the Application ID (also known as the Client ID) without hyphens concatenated with the generated AK01 key value.

bd34364853ae473d802dc6d25bcab7e9rPV6zY9l49a2GV/sUITtlrv5uYZDhZuF9hOkIndFTuY=

Once the name and secret have been provided the configuration of SQL Server can begin.

Step 1.

Install the SQL Server Connector for Microsoft Azure Key Vault on all SQL Server database servers. https://www.microsoft.com/en-us/download/details.aspx?id=45344

Note down the installed path as this will be needed later.

This software is supported on Enterprise 64-bit editions and the following SQL Server versions:

• SQL Server 2014 later
• SQL Server 2012 SP2
• SQL Server 2012 SP1 CU6
• SQL Server 2008 R2 SP2 CU8

Step 2.

Enable the EKM provider.

1. USE master;
2. GO
3. sp_configure 'show advanced options', 1 ;
4. GO
5. RECONFIGURE ;
6. GO
7. sp_configure 'EKM provider enabled', 1 ;
8. GO
9. RECONFIGURE ;

Step 3.

Register the EKM installed. Alter file install path if needed. This will register a cryptographic provider for SQL Server for the Azure Key Vault Extensible Key Management (EKM) provider.

1. CREATE CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov
2. FROM FILE = 'C:\Program Files\SQL Server Connector for Microsoft Azure Key Vault\Microsoft.AzureKeyVaultService.EKM.dll';

Step 4.

Create credential using the prerequisite information as shown at the start of this section consisting of the key vault name, Application ID (also known as the Client ID) without hyphens concatenated with the generated key value.

1. CREATE CREDENTIAL sysadmin_ekm_cred
2. WITH IDENTITY = 'KeyVaultSQL01',
3. SECRET = 'bd34364853ae473d802dc6d25bcab7e9rPV6zY9l49a2GV/sUITtlrv5uYZDhZuF9hOkIndFTuY='
4. FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov

Using SQL Server with the Key Vault.

At this stage the credential can be added to your login via the following script.

1. ALTER LOGIN [AzureAD\JonGurgul] ADD CREDENTIAL [sysadmin_ekm_cred];

The preexisting key in the vault can then be registered within SQL Server.

1. CREATE ASYMMETRIC KEY Key01
2. FROM PROVIDER [AzureKeyVault_EKM_Prov]
3. WITH PROVIDER_KEY_NAME = 'Key01',
4. CREATION_DISPOSITION = OPEN_EXISTING

In order for SQL Server to use this key internally a login has to be provided based on this asymmetric key as well as having the required credential. Below is an example of how to configure TDE.

1. CREATE CREDENTIAL [key01_ekm_cred]
2. WITH IDENTITY = 'KeyVaultSQL01',
3. SECRET = 'bd34364853ae473d802dc6d25bcab7e9rPV6zY9l49a2GV/sUITtlrv5uYZDhZuF9hOkIndFTuY='
4. FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov
5. CREATE LOGIN [key01_ekm_login] FROM ASYMMETRIC KEY [Key01];
6. ALTER LOGIN [key01_ekm_login] ADD CREDENTIAL [key01_ekm_cred];

Add TDE to a database using the key.

1. CREATE DATABASE ENCRYPTION KEY
2. WITH ALGORITHM = AES_128
3. ENCRYPTION BY SERVER ASYMMETRIC KEY [Key01]
4. GO
5. ALTER DATABASE [tde] SET ENCRYPTION ON

Summary

The management of keys used by SQL Server should now be administered and maintained by Azure Key Vault.

Further reading

SQL Server connector troubleshooting guide. https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/sql-server-connector-maintenance-troubleshooting