Configuring Microsoft SQL Server for EKM

This section explains how to configure Microsoft SQL Server for Extensible Key Management (EKM).

Ensure you have configured the KeyControl Database Connector. See Configuring a KeyControl Database Connector .

Configure EKM on the SQL Server

Start a connection with the SQL Server database engine.

Run the following commands to enable Extensible Key Management:

Copy

sp_configure 'show advanced', 1
GO
RECONFIGURE
GO

sp_configure 'EKM provider enabled', 1
GO
RECONFIGURE
GO

Run the following command to create the Cryptographic Provider:

Copy

CREATE CRYPTOGRAPHIC PROVIDER EKM_Prov                                   
FROM FILE = 'c:\program files\hcs\bin\htsqlekm_provider.dll';      
GO

Configure the Cryptographic Provider on SQL Server

Using the identity and secret that you copied when you generated the access token, create a credential file in json format. This file should be named something easily identifiable, and located where it is accessible to SQL Server, for example, C:\Users\Administrator\sqlcred.conf. Please make sure that Windows did not add an extra .txt extension to the filename.

For SQL server clusters, the file path and name should be the same on all nodes.
Copy
```
{
    "identity"    : "<the identity copied from the access token>",
    "secret"     : "<the secret copied from the access token>"
}
```
Create a credential in your Microsoft SQL Server VM that includes the credential file path for the identity and secret file that you created.

Important: The parameter SECRET is mandatory for the CREATE CREDENTIAL statement in EKM, but is not used here because both the identity and secret are contained in the sqlcred.conf file. You can assign it any value because the provider will ignore it.
Copy
```
CREATE CREDENTIAL sa_ekm_tde_cred
WITH IDENTITY = 'file:C:\Users\Administrator\sqlcred.conf',
SECRET = 'ignore'
FOR CRYPTOGRAPHIC PROVIDER EKM_Prov ;
GO
```
Note: We recommend using the file method instead of simply copying the actual identity and secret because it is required for SQL Server clusters.

If you do not plan to use a SQL Server cluster, and would rather use the identity and secret values, enter it as follows:
Copy
```
CREATE CREDENTIAL sa_ekm_tde_cred
WITH IDENTITY = '<Connector name>',
SECRET = '<Secret Access Key>'
FOR CRYPTOGRAPHIC PROVIDER EKM_Prov ;
GO
```

Add the credential to a privileged user with domain login in the format [DOMAIN\login].

Copy

ALTER LOGIN [DOMAIN\login]
ADD CREDENTIAL "sa_ekm_tde_cred";         
GO