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

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-maintenance-troubleshooting?view=sql-server-ver15#AppendixC

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.