Error creating ASYMMETRIC KEY - Cannot open session for cryptographic provider for SQL Server Cryptographic Provider for Azure Key Vault

Question

I am creating an asymmetric key on the SQL Server database and have the SQL connector for Azure Key Vault installed and I am following the steps mentioned in the link setup steps for extensible key management using the azure key vault.

I am able to create CRYPTOGRAPHIC PROVIDER and credential, but when I try to create Asymmetric key using the below query, I get an error

 CREATE ASYMMETRIC KEY EKMASYKey
 FROM PROVIDER [KeyVault_EKM]
 WITH PROVIDER_KEY_NAME = 'CMKAuto1',
 CREATION_DISPOSITION = OPEN_EXISTING;

Below is the error I get

Cannot open session for cryptographic provider 'KeyVault_EKM'. Provider error code: 2050. (Provider Error - No explanation is available, consult EKM Provider for details)

I have seen similar questions Here and set up "SQL Server Cryptographic Provider" in registry and provided permissions to the service account under which SQL server is running (NT Service\MSSQLSERVER) with full control and have reviewed the permissions. but still get this error

Is there anything else I would need to setup that I am missing.

Answer 1

I have registered an application in active directory and created secret for the application. image for reference:

I have created Azure key vault and created two access policies to the application which I registered in azure active directory. Image for reference:

I created key in azure key vault. Image for reference:

I install the SQL Server Connecter in my local machine. Image for reference:

In SSMS I Configure SQL Server to use EKM by running the following Transact-SQL script:

USE master;
GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'EKM provider enabled', 1;
GO
RECONFIGURE;

I created Cryptographic provider by using below code.

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

Image for reference:

I created SQL Server credential for a SQL Server login to use the key vault where identity is key vault name and secret is concatination of application client id and secret of application without hipens using below code.

 USE master;
    CREATE CREDENTIAL sysadmin_ekm_cred
        WITH IDENTITY = 'ContosoEKMKeyVaulta',                           
        SECRET = '663e01f3-7537-49f6-8074-1ac787d3d452ReplaceWithAADClientSecret'
    FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM;
Image for reference:

Image for reference:

I added the credential to the SQL Server administrator's domain login using below code.

ALTER LOGIN bhanu
ADD CREDENTIAL sysadmin_ekm_cred;

Image for reference:

I have created new asymmetric key using below code where PROVIDER_KEY_NAME is my key name which I created in azure key vault.

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

I also got the same error .

Image for reference:

I changed My secret string as following secret = 'clientId+ReplaceWithAADClientSecret' Code;

USE master;  
CREATE CREDENTIAL sysadmin_ekm_cred   
    WITH IDENTITY = 'ContosoEKMKeyVault', -- for global Azure
    SECRET = '<clientId>ReplaceWithAADClientSecret'   
FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM; 

Image for reference:

Note:

The secret string that is the concatenation of AAD client ID and secret is shorter than 32 characters

It resolved my issue and it run successfully.

Image for reference: