Common Issue While Setting up TDE with Azure Key Vault

Setting up TDE is easy enough with Azure Key Vault as EKM but sometimes we face issues that makes us stuck like :

Creds already exisist because only one-to-one relationship between the credential and login is allowed. To support the configuration process firstly assign the credential to your SQL Server login. Later on we’ll re-assign it to a system login that will handle the encryption.

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

The following command create a SQL Server object that represents the key stored in the Azure Key Vault service. Note, that we’re not creating a new key, but just referencing the existing Key Encryption Key. Replace the value of parameter PROVIDER_KEY_NAME with the name of the key in the vault.

CREATE ASYMMETRIC KEY EKM_KEY
FROM PROVIDER [AzureKeyVault_EKM]  
WITH PROVIDER_KEY_NAME = '<Key Vault Key name>',  
CREATION_DISPOSITION = OPEN_EXISTING;

When you execute above command you actually reach to the Key Vault service for the first time. If you made a mistake on the way, this is the moment of truth. I went through the process a bunch of time and quite often this step reported an error.

The error message returned by the SQL engine doesn’t give too much information. To get a more precise explanation open Windows Event Viewer.

There are two main issues you can encounter:

1. Wrong credentials
2. Error accessing the registry

Wrong credentials – resolution:

If you accidentally provided wrong Client ID or Client Secret you must unassign the Credential from your user, drop it and then re-create it:

ALTER LOGIN [<domain>\<login>] DROP CREDENTIAL [sysadmin_ekm_cred]
GO
DROP CREDENTIAL [sysadmin_ekm_cred]

Error accessing the registry – resolution

If you see an event saying there is an “Error when accessing registry” you have to manually create the missing entry.

--Create the registry entry to enable missing messages (this works with any version)
REG ADD "HKLM\SYSTEM\ControlSet001\Services\EventLog\Application\SQL Server Connector for Microsoft Azure Key Vault" /v EventMessageFile /t REG_EXPAND_SZ /d "C:\Program Files\SQL Server Connector for Microsoft Azure Key Vault\Microsoft.AzureKeyVaultService.EKM.dll"

--Validate the new registry entry
REG QUERY "HKLM\SYSTEM\ControlSet001\Services\EventLog\Application\SQL Server Connector for Microsoft Azure Key Vault" /v EventMessageFile

Open the Registry Editor and navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft. Create the key “SQL Server Cryptographic Provider” if it doesn’t exist.

Then assign Full Permissions to this registry key to the account that runs the SQL Server instance:

Finally, the script should complete without any issues:

You can display the Key Encryption Key using following command:

SELECT * FROM sys.asymmetric_keys

Create a new system Login based on the created asymmetric key. You can’t use it to login to the SQL Server, it’s a technical object to handle the encryption.