Addressing SQL Server and TDE with AKV errors


I recently wrote an Azure Data Studio Notebook on how to setup TDE for SQL Server 2019 Standard Edition (yes, SQL Server 2019 Standard Edition has TDE) using Azure Key Vault. I ran into a few issues that I had to debug, which I am outlining below. Make sure that you are following the pre-requisites when you are setting TDE with Azure Key Vault.

The first one was a 404 error. When I looked the application event log, I saw the following error:

Operation: getKeyByName
Key Name: ContosoRSAKey0
Message: [error:112, info:404, state:0] The server responded 404, because the key name was not found. Please make sure the key name exists in your vault.

The simple reason for the above error is that I was using an incorrect key name or the key didn’t exist in my Azure Key Vault. So the remediation is to check if the key exists in your Azure Key Vault. If not, then create the key.

Another error I ran into was a 401 error. The following information was included with the event:

Operation: acquireToken
Key Name:
Message: [error:108, info:401, state:0] Server responded 401 for the request. Make sure the client Id and secret are correct, and the credential string is a concatenation of AAD client Id and secret without hyphens.

The CREATE CREDENTIAL command has the following syntax:

CREATE CREDENTIAL Azure_EKM_TDE_cred WITH IDENTITY = 'SQLStandardKeyVault', -- for global Azure -- WITH IDENTITY = 'ContosoDevKeyVault.vault.usgovcloudapi.net', -- for Azure Government -- WITH IDENTITY = 'ContosoDevKeyVault.vault.azure.cn', -- for Azure China 21Vianet -- WITH IDENTITY = 'ContosoDevKeyVault.vault.microsoftazure.de', -- for Azure Germany SECRET = '<combination of AAD Client ID without hyphens and AAD Client Secret>' FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov

The IDENTITY here is the name of your Azure key vault.
The SECRET here is your AAD Client ID (with the hyphens removed) and your AAD Client Secret concatenated together. You will need to create a “New Client Secret” for your Azure AD app registration. See steps here.

Your AAD Client ID will be a GUID and so will your Client Secret will be a random alphanumeric string. If you don’t have the client secret, then create new one and use that.

Advertisements