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:
- Wrong credentials
- 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.