Part 4 - SQL Server TDE and Extensible Key Management Using Azure Key Vault

[Adrian_Rupp]

Configure SQL Server

This is Part: 4 of a 4-part blog series:

After setting up Azure Active Directory and registering the AAD Application and additionally creating an Azure Key Vault, the next step is to put it all together in SQL Server where you can create credentials (to talk to Azure Key Vault), create an asymmetric key and use that key to configure/encrypt a database with TDE.

Refer to B. Frequently Asked Questions to see a note about the minimum permission levels needed for each action in this section.

Step 1: Launch sqlcmd.exe or SQL Server Management Studio (SSMS): If you use SSMS open a query window, turn on SQLCMD mode (Menu>Query > Click SQLCMD Mode)

Step 2: Configure SQL Server to use EKM: Execute the following Transact-SQL script to configure the Database Engine to use an EKM provider.

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

-- Enable EKM provider 
Exec sp_configure 'EKM provider enabled', 1; 
GO 
RECONFIGURE; 

Step 3: Register (create) the Connector as an EKM provider with SQL Server: Create a cryptographic provider, using the SQL Server Connector, which is an EKM provider for the Azure Key Vault.
This example uses the name AzureKeyVault_EKM.

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

[NOTE]
The file path length cannot exceed 256 characters.

Step 4: Setup a SQL Server credential for a SQL Server login to use the key vault:?A credential must be added to each login that will be performing encryption using a key from the Key Vault. This might include: 

• An SQL Server administrator login who will use key vault in order to setup and manage SQL Server encryption scenarios. 

• Other SQL Server logins such as dedicated Security & Compliance Administrators who might enable Transparent Data Encryption (TDE), or other SQL Server encryption features. 

There is one-to-one mapping between credentials and logins. That is, each login must have a unique credential. 

Modify the Transact-SQL script below in the following ways: 

• Edit the IDENTITY argument (MyAAD-EKM-AKV-DemoKeyVault) to point to your Azure Key Vault. 

• If you're using global Azure, replace the IDENTITY argument with the name of your Azure Key Vault from Part: 2. 

• If you're using a private Azure cloud (ex. Azure Government, Azure China, or Azure Germany), replace the IDENTITY argument with the Vault URI that is returned in Part 2, Step 6. Do not include "https://" in the Vault URI. 
• Replace the first part of the SECRET argument with the Azure Active Directory Client ID from Part 2. In this example, the Client ID is 9A57CBC54C4C40E2B517EA677E0EFA00. 

[IMPORTANT] 
You must remove the hyphens from the App (Client) ID. 

Complete the second part of the SECRET argument with Client Secret from Part: 2. 
In this example the Client Secret from Part: 2 is 
(Example:?08:k?[:XEZFxcwIPvVVZhTjHWXm7w1?m). 
The final string for the SECRET argument will be a long sequence of letters and numbers, with no hyphens. 

USE master; 
CREATE CREDENTIAL sysadmin_ekm_cred  
WITH IDENTITY    = 'ConstosoKeyVaultKeyVault',  -- for public Azure
-- WITH IDENTITY = 'ContosoEKMKeyVault.vault.usgovcloudapi.net', -- for Azure Government
-- WITH IDENTITY = 'ContosoEKMKeyVault.vault.azure.cn',-- for Azure China
-- WITH IDENTITY = 'ContosoEKMKeyVault.vault.microsoftazure.de', -- for Azure Germany
         --<----Application (Client) ID ---><--AAD App (Client) ID Secret-->
--SECRET = '9A57CBC54C4C40E2B517EA677E0EFA0008:k?[:XEZFxcwIPvVVZhTjHWXm7w1?m'  
SECRET = '<AppID GUID with no dashes from AAD><Client Secret From AAD>'
FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM; 

Step 5: Add Credential to your Windows Login

-- Add the credential to the SQL Server administrator's domain login  
ALTER LOGIN [<domain>\<login>] 
ADD CREDENTIAL sysadmin_ekm_cred; 

Step 6: Open your Azure Key Vault key in SQL Server: Whether you created a new key, or imported an asymmetric key as described in Part:PS2 or Part:AP3, you will need to open the key. Open the key by providing your key name in the following  Transact-SQL script.

• Replace EKMSampleASYKey with the name you'd like the key to have in SQL Server
• Replace ConstosoKeyVaultRSAKey with the name of your key in Azure Key Vault.

CREATE ASYMMETRIC KEY EKMSampleASYKey  
FROM PROVIDER [AzureKeyVault_EKM] 
WITH PROVIDER_KEY_NAME = 'ConstosoKeyVaultRSAKey', 
CREATION_DISPOSITION = OPEN_EXISTING; 

Step 7: Create a new Login from ASYMMETRIC KEY in SQL Server: The new login can now be created using the asymmetric key creates in step 6.

--Create a Login that will associate the asymmetric key to this login
CREATE LOGIN TDE_Login
FROM ASYMMETRIC KEY EKMSampleASYKey;

Step 8: Create a new Login from ASYMMETRIC KEY in SQL Server: Drop the credential mapping from Step 5 so the credential can be mapped to the new login.

--Now drop the credential mapping from the original association
ALTER LOGIN [<domain>\<login>]
DROP CREDENTIAL sysadmin_ekm_cred;

Step 9: Alter the new Login: Alter the new Login and map the EKM credential to the new login.

--Now drop the credential mapping from the original association
ALTER LOGIN TDE_Login
ADD CREDENTIAL sysadmin_ekm_cred;

Step 10: Create Test Database: Create a test database that will be encrypted with the Azure Key Vault (Key). (Or use an existing database - make sure you have a good/current unencrypted backup before turning on TDE)

--Create a test db that will be encrypted with the Azure KeyVault (Key)
CREATE DATABASE TestTDE

Step 11: Create a database encryption Key Create an ENCRYPTION KEY using the ASYMMETRIC KEY (EKMSampleASYKey)

--Create an ENCRYPTION KEY using the ASYMMETRIC KEY (EKMSampleASYKey)
CREATE DATABASE ENCRYPTION KEY  
WITH ALGORITHM = AES_256  
ENCRYPTION BY SERVER ASYMMETRIC KEY EKMSampleASYKey; 

Step 12:  Encrypt the test database Enable TDE by setting ENCRYPTION ON 

--Enable TDE by setting ENCRYPTION ON
ALTER DATABASE TestTDE  
SET ENCRYPTION ON; 

Step 13: Validate TDE is ON and has encrypted the database: SQL Server has a DMV that will show if encryption has been enabled ad the state of the encrypted database.

select db_name(database_id) AS DB
       ,percent_complete AS percent_complete 
       ,encryption_state AS encryption_state
from sys.dm_database_encryption_keys
/*
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
*/

Step 14: Cleanup test objects

-- CLEAN UP
USE Master
ALTER DATABASE [TestTDE] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [TestTDE]

ALTER LOGIN [TDE_Login] DROP CREDENTIAL [sysadmin_ekm_cred]
DROP LOGIN [TDE_Login]

--ALTER LOGIN [domain\login] DROP CREDENTIAL [sysadmin_ekm_cred]
DROP CREDENTIAL [sysadmin_ekm_cred]

USE MASTER
DROP ASYMMETRIC KEY [EKMSampleASYKey]
DROP CRYPTOGRAPHIC PROVIDER [AzureKeyVault_EKM]

Conclusion

Configuring SQL Server with the SQL Connector, registering an Azure Active Directory application, creating RSA key in Azure Key Vault  and creating the SQL credentials, asymmetric key, and enabling TDE are the final steps to enable TDE to use Extensible Key Management (EKM) and Azure Key Vault (AKV) for encryption.

Live secure and prosper!

Adrian

Next steps

SQL Server TDE with EKM Using Azure Key Vault – Intro
SQL Server Connector for Microsoft Azure Key Vault (aka: SQL Server Connector) – Part: 1
Azure Portal Method	PowerShell Method
Set up an Azure Active Directory Service Principal – Part: AP2	Setup Azure Active Directory Service Principal and  Azure Key Vault (one script) – Part: PS2   This script combines Part:AP2 & Part:AP3
Create an Azure Key Vault – Part: AP3
Configure SQL Server TDE EKM using AKV – Part: 4  (this document)

Download the scripts for PowerShell and SQLCMD here:

Setup_TDE_and_EKM_Using_AKV .zip

[Abhijeet_Sri]

Getting this error on Step 6. Any idea what could be wrong.

Msg 33028, Level 16, State 1, Line 38
Cannot open session for cryptographic provider 'AzureKeyVault_EKM'. Provider error code: 3900. (Provider Error - No explanation is available, consult EKM Provider for details)

[Adrian_Rupp]

From my experience, I can reproduce this error if you try to execute the TSQL code to create the Credentials and ASYMMETRIC KEYS before the Azure items are created, this error occurs.

If you have not already done so, I would suggest going through the PowerShell script (Part:PS2) first and make sure everything is in order before attempting the SQL Server portion of the solution. If you have already gone through the Azure setup, I would double-check the Azure Key Vault names to make sure you are are trying to reference the correct objects in Azure.

I'm working on getting the full set of error codes published so the error messages are less cryptic.

Hope this helps.

[Axurexia]

Ok, I've done. 

But now some time has passed and I have to rotate my key. In AKV I generated a New Version of the key and now I have current version enabled key and the old version enabled key. After a while SQL Server loss the possibility to access to my DB... 

I need to know how I can rotate this key and tell to SQL server how to re-encrypt my DEK on my DBs.

And about old backup done with old key version?

[DaCh1]

Hi @Axurexia,

I had this problem recently; here’s how I was able to fix it…

This is a problem that appears to be due to how the earlier versions of the “SQL Server Connector for Microsoft Azure Key Vault” (aka the Cryptographic Provider, aka ‘Microsoft.AzureKeyVaultService.EKM.dll') handles references to Keys in the Azure Key Vault.

You can see what version of the Cryptographic Provider you are using by executing the following statement:

SELECT * FROM [master].sys.cryptographic_providers;

In v1.0.4.0, it only appears to reference the Key Name; but from v1.0.5.0 it references the Key and Version Id (it also stores these references in the Registry, which I’ll come to later).
This can be seen in the length of the thumbprints for the Asymmetric Key, by executing the following statement:

SELECT * FROM [master].sys.asymmetric_keys;

You can download the latest version of the Cryptographic Provider from here:
https://www.microsoft.com/en-us/download/details.aspx?id=45344

A useful resource when using the connector – the list of error codes – can be found here:
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/sql-server-connector-m...

You need to ensure that you have Asymmetric Keys where the thumbprint matches the ‘encryptor_thumbprint’ for the database:

SELECT db_name(database_id) AS DB, database_id, percent_complete, encryption_state, encryptor_thumbprint
FROM sys.dm_database_encryption_keys;

If you haven’t got those Asymmetric Keys with those thumbprints, you’ll need to create them. If you’ve already upgraded to v1.0.5.0 of the Cryptographic Provider, you’ll need to temporarily revert to v1.0.4.0 (you can obtain the DLL from any of the Stock SQL Server VM images in Azure).

The default location for the DLL is:
C:\Program Files\SQL Server Connector for Microsoft Azure Key Vault

I recommend you copy the v1.0.4.0 DLL to:
C:\Program Files\SQL Server Connector for Microsoft Azure Key Vault\EKM_dll_v1.0.4.0

Reverting to the older version

1. In SQL Server:
   

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

2. Stop SQL Server
3. In the Registry Editor, temporarily rename the “HKLM\Software\Microsoft\SQL Server Cryptographic Provider” key. For Example:
   “HKLM\Software\Microsoft\SQL Server Cryptographic ProviderX”
   Full Disclosure: I’m not entirely sure this step is necessary, but I’m doing it to err on the side of caution.
4. Restart SQL Server

Creating the ‘short’ thumbprints

1. Foreach Key you want to recover, execute the CREATE ASYMMETRIC KEY command; use only the name in the PROVIDER_KEY_NAME clause. For example:
   

   CREATE ASYMMETRIC KEY [key0]  
   FROM PROVIDER AzureKeyVault_EKM_Prov
   WITH PROVIDER_KEY_NAME = 'TestKey'   
   , CREATION_DISPOSITION = OPEN_EXISTING;

2. Make a note of all the thumbprints (excluding the “0x” from the beginning) or use the following query:
   

   SELECT LOWER(CONVERT(VARCHAR(100), thumbprint, 2)) AS [RegistryKeyName], *
   FROM [master].sys.asymmetric_keys;

Upgrading and recovering your database

1. Move back to the newer version of the DLL
   In SQL Server:
   

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

2. Stop SQL Server
3. In the Registry Editor, restore the “HKLM\Software\Microsoft\SQL Server Cryptographic Provider” key.
   If you don’t have that Key in the registry, create it and assign “Full Control” permissions to the SQL Server service account (probably “NT Service\MSSQLServer”)
4. The “SQL Server Cryptographic Provider” Registry key should look something like this:
   
5. The Key directly under “Azure Key Vault” is the unique Fully Qualified name of your Key Vault, and each of the long alpha-numeric strings under that is the thumbprint of an Asymmetric Key.
   If you need to create a Registry key for one-or-more of your Asymmetric Keys, each of those contains a “String” value called “KeyUri” which stores the Name and Version number of the key inside the KeyVault.
   For example:
   
6. Ensure that – for the Registry Key that represents the Asymmetric Key for your Database Encryption - the “KeyUri” references the name and version of the Azure Key Vault that you originally encrypted your database with.
7. Restart SQL Server.
   You should now be able to access your database.

Next Steps

1. Create a new TDE key in Azure Key Vault
   Why? Well…
   While you can have two logins that are each mapped to a specific (but different) version of the same Key, you cannot have one login mapped to the generic version of a Key (i.e., one created by v1.0.4.0 or earlier, with the shorter thumbprint) and – at the same time – another login mapped to a specific version of the Key; SQL Server sees these as the same and tells you that the login already exists.
2. Follow steps #4 --> #10 in this TechNet guide for the new TDE key.
   When you are creating your Asymmetric Keys, for the "PROVIDER_KEY_NAME", specify the name and version of the key, for example:
   

   CREATE ASYMMETRIC KEY [key0]  
   FROM PROVIDER AzureKeyVault_EKM_Prov
   WITH PROVIDER_KEY_NAME = 'TestKey/edfe7429bb924ef094087595d0bd3ce9'   
   , CREATION_DISPOSITION = OPEN_EXISTING;

3. Now you can rotate the Database Encryption Key in your database:
   

   USE [<Your_Database>];
   GO
   ALTER DATABASE ENCRYPTION KEY
   ENCRYPTION BY SERVER ASYMMETRIC KEY [<Your_New_TDE_Asymmetric_Key>];
   GO

Depending on the size of your database, this may take some time. You can check on the progress by (re)run the following statement:

SELECT db_name(database_id) AS DB, database_id, percent_complete, encryption_state, encryptor_thumbprint
FROM sys.dm_database_encryption_keys;

While in progress, the [encryption_state] will be something other than ‘3’, and [percent_complete] will be a non-zero value.

1. Once the Key Rotation for your database has finished, you can go to Azure Key Vault and disable the old Key(s).
   Note: The Expiration date (i.e., in cases when it has passed) has no effect on SQL Server.

[EricHe2020]

Andrian; Thx for the excellent post.   can I ask what is purpose of this ? this login is not for users to login?

--Create a Login that will associate the asymmetric key to this login
CREATE LOGIN TDE_Login
FROM ASYMMETRIC KEY EKMSampleASYKey;

[Adrian_Rupp]

Hello EricHe2020;

Sorry for the delay in getting back to you, I've been out on leave for a few months.

You are correct, this is not a login used for logging in to the server by a user.

It is a login for the credential that is tied to an ASYMMETRIC Key. 

That is then used to contact AKV. This is a lot of steps, and switching things around to get all the planets aligned.

I hope this answers your questions.

[EricHe2020]

Adrian: Thanks so much for your reply.  I understand TDE_Login is tied to credential linked to Asymmetric key,  but TDE_Login is used by what to contact AKV? by application?  I read your posting multiple times, seems that once the database TDE is turned on, there is nothing to config, right?  there is no mention in the posting about how TDE_Login is used and by what.

[Adrian_Rupp]

So the credential that was created (is Step 4) is associatedwith the TDE_Login (in Step 9).

It is the credential that SQL uses to contact AKV. Via the Azure Active Directory Application (from Part 2)

This whole process is confusing and complicated. (Written years ago - way before my time).

I've tried simplifying it and not having to swap credentials and logins... but in order to get the Asym key, we need to go through all of these steps.

I believe the database uses the TDE_Login (with the attached Credential) to contact AKV... it all happens under the covers so not much to discuss in this article and no way for me to see how that functions. I know if you do not create the TDE_Login, there is no wayfor the Encryption Provider to get the credentials to access AKV.

And your last question: Yes, once TDE is enabled, you can not configure anything (opther than enabling/disabling inStep 12)

ALTER DATABASE TestTDE  
SET ENCRYPTION ON; 

Hope this helps.

[Manju1175]

Hi Adrian,

Could you please help me understand what happens to the SQL server's access to the AKV/Key when the client secret associated with the registered application on Microsoft Entra ID expires? As we are creating a credential in the SQL server with a combination of the application ID and client secret and using it to access the key on AKV.

Would there be any impacts on the encrypted database other than the possibility of losing access to the key in such scenarios also what would be the solution in such cases?

Thank you,

Manju

[Axurexia]

Hi Manju1175,

consider SQL Server do not use continuously the key in order to decrypt the DB. When a secret expires you have some time (even a few days) in order to change it, typically until next SQL Server restart (for example during a windows update that restarts the server).  

You have to generate a new secret in the app and simply recreate the string "APPID(without '-') plus NEWSECRET" and then change it via SSMS to the ekm credential password used to connect to the vault. I do not remeber if you have to restart SQL but I do non think so.

We made it last year, we forgot to change it but we discovered soon that it worked until the next SQL restart after the expiration.

Regards,

Axurexia. 

[Manju1175]

Axurexia : Thank you for your response to my previous question. Would it be possible for you to answer a few more questions?

1. based on your experience with AKV would you prefer to create a new version of the existing key and update SQL to use the new version or create a new key and re-encrypt the database again from the beginning with the new key?
2. After the key rotation would I still be able to restore the database to a point in time before the key rotation provided that the old key/key version has expired?
3. Are there any other dependencies except for the key and client secret expiry on the AKV which could ultimately affect the availability of the database?

[Axurexia]

Manju1175,

1. using SQL Server on premise you have to use connector version 1.0.5. This version allows (1.0.4 do not) you the key versioning. In AKV you can create a new version of the key or a new key, it does not matter. You have to create another credential in SQL Server and create a new asymmetric key with "WITH PROVIDER_KEY_NAME = '<key name>/<version>" if you use the versioning option. Otherwise you have to create another credential and another asymmetric key in standard way "WITH PROVIDER_KEY_NAME = '<key name>". Leave active all the keys you need on the vault, you have to disabile only when no DBs are encrypted by a key. Decrypt the DB with the old key (or previous version) and encrypt with the new one. At the end you can disable old version or old key. 

2. do not delete any keys if you think you need it. If you have some old backup keep the key disabled but present. If you need to restore a backup made with a previous version or with an old key you have to re-enable it, create a credential and asy key in SQL and then use it to decrypt the backup and restore it. 

3. no I do not think so.

4. if you use the key generated by AKV you cannot save and restore in another AKV. This guaranteeses security but if you lose the AKV you lose all the key. It is a remote event but it a possible event. You can generate your own keys and import in AKV. You can save it in a safe and restore to another AKV if you need.