Installing the Vault EKM Provider

Prerequisites

• Vault Enterprise server 1.9+ with a license for the Advanced Data Protection Key Management module
• Microsoft SQL Server for Windows (SQL Server for Linux does not support EKM)
• An authenticated Vault client

To check your Vault version and license, you can run:

vault status
vault license get -format=json

The list of features should include "Key Management Transparent Data Encryption".

Installing the Vault EKM provider

Configuring Vault

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

1. Set up AppRole auth:

   vault auth enable approle
   vault write auth/approle/role/tde-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

The remaining steps are all run on the database server.

Install the EKM provider on the server

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

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

Configure the EKM provider using SQL

Open Microsoft SQL Server Management Studio, and run the queries below to complete 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/role-id
   -- vault write -f auth/approle/role/ekm-encryption-key-role/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;