HCP Vault EKM provider for SQL server – HashiCorp Help Center

Features

The Vault EKM provider supports the following features:

Management of KEK with Transit secret engine using rsa-2048 key cipher
AppRole auth

Prerequisites

HCP Vault Plus Tier server as it has added support for the Advanced Data Protection Key Management module
Microsoft Windows Server operating system
Microsoft SQL Server for Windows (SQL Server for Linux does not support EKM)
An authenticated Vault client

Installing the HCP Vault EKM provider

Configuring HCP Vault

The EKM provider requires AppRole auth and the Transit secret engine to be set up on the HCP Vault server. The steps below can be used to configure HCP Vault ready for the EKM provider to use.

Note: rsa-2048 is currently the only supported key type.

1. Set up AppRole auth:

vault auth enable approle
vault write auth/approle/role/ekm-encryption-key-role \
token_ttl=20m \
max_token_ttl=30m \
token_policies=tde-policy

2. Retrieve the AppRole ID and secret ID for use later when configuring SQL Server:

vault read auth/approle/role/ekm-encryption-key-role/role-id
vault write -f auth/approle/role/ekm-encryption-key-role/secret-id

3. Enable the transit secret engine and create a key:

vault secrets enable transit
vault write -f transit/keys/ekm-encryption-key type="rsa-2048"

4. Create a policy for the Vault EKM provider to use. The following policy has the minimum required permissions:

vault policy write tde-policy -<<EOF
path "transit/keys/ekm-encryption-key" {
capabilities = ["create", "read", "update", "delete"]
}

path "transit/keys" {
capabilities = ["list"]
}

path "transit/encrypt/ekm-encryption-key" {
capabilities = ["update"]
}

path "transit/decrypt/ekm-encryption-key" {
capabilities = ["update"]
}

path "sys/license/status" {
capabilities = ["read"]
}
EOF

Configuring SQL server

1. Download and run the latest Vault EKM provider installer from releases.hashicorp.com

2. Enter your Vault server's address URL when prompted and complete the installer

There is a limit on the number of characters entered here, if your HCP vault cluster DNS name is longer, follow step 3 below to update the config.json file with the complete name

3. If you need to configure non-default namespace or mount paths for your AppRole and Transit engines, see the configuration.

Configuration is stored in a config.json file under ProgramData in a path that mirrors the installation folder. This defaults to C:\ProgramData\HashiCorp\Transit Vault EKM Provider\config.json. The configuration file will look like below:

{
"namespace": "admin",
"appRoleMountPath": "approle",
"transitMountPath": "transit",
"enableTrace": "true",
"vaultApiBaseUrl": "https://vault-cluster1-public-vault-7e04054c.e5b2dc.z1.hashicorp.cloud:8200"
}

Configure the EKM provider using SQL

Open Microsoft SQL Server Management Studio, and run the queries below to complete the installation.

1. Enable the EKM feature and create a cryptographic provider using the folder you just installed the EKM provider into.

-- Enable advanced options
USE master;
GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

-- Enable EKM provider
EXEC sp_configure 'EKM provider enabled', 1;
GO
RECONFIGURE;
GO
CREATE CRYPTOGRAPHIC PROVIDER TransitVaultProvider
FROM FILE = 'C:\Program Files\HashiCorp\Transit Vault EKM Provider\TransitVaultEKM.dll'
GO

2. Next, create credentials for an admin to use EKM with your AppRole role and secret ID from above:

-- Replace <approle-role-id> and <approle-secret-id> with the values from
-- the earlier vault commands:
-- vault read auth/approle/role/ekm-encryption-key/role-id
-- vault write -f auth/approle/role/ekm-encryption-key/secret-id
CREATE CREDENTIAL TransitVaultCredentials
WITH IDENTITY = '<approle-role-id>',
SECRET = '<approle-secret-id>'
FOR CRYPTOGRAPHIC PROVIDER TransitVaultProvider;
GO

-- Replace <domain>\<login> with the SQL Server administrator's login
ALTER LOGIN "<domain>\<login>" ADD CREDENTIAL TransitVaultCredentials;

3. You can now create an asymmetric key using the transit key set up earlier:

CREATE ASYMMETRIC KEY TransitVaultAsymmetric
FROM PROVIDER TransitVaultProvider
WITH
CREATION_DISPOSITION = OPEN_EXISTING,
PROVIDER_KEY_NAME = 'ekm-encryption-key';

4. Create another login from the new asymmetric key:

-- Replace <approle-role-id> and <approle-secret-id> with the values from
-- the earlier vault commands again
CREATE CREDENTIAL TransitVaultTDECredentials
WITH IDENTITY = '<approle-role-id>',
SECRET = '<approle-secret-id>'
FOR CRYPTOGRAPHIC PROVIDER TransitVaultProvider;
GO

CREATE LOGIN TransitVaultTDELogin
FROM ASYMMETRIC KEY TransitVaultAsymmetric;
GO

ALTER LOGIN TransitVaultTDELogin
ADD CREDENTIAL TransitVaultTDECredentials;
GO

5. Finally, you can enable TDE and protect the database encryption key with the asymmetric key managed by Vault's Transit secret engine:

CREATE DATABASE TestTDE
GO

USE TestTDE;
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER ASYMMETRIC KEY TransitVaultAsymmetric;
GO

ALTER DATABASE TestTDE
SET ENCRYPTION ON;
GO

6. Check the status of database encryption using the following queries:

SELECT * FROM sys.dm_database_encryption_keys;

SELECT (SELECT name FROM sys.databases WHERE database_id = k.database_id) as name,
encryption_state, key_algorithm, key_length,
encryptor_type, encryption_state_desc, encryption_scan_state_desc FROM sys.dm_database_encryption_keys k;

References:

Troubleshooting the Vault EKM provider

Error Code Explanations for SQL Server Connector

Change server authentication mode