Using SDKMS with Microsoft SQL Server TDE – Fortanix

Overview

Transparent Data Encryption (TDE) in Microsoft SQL Server Databases allows you to protect sensitive data in tables by encrypting them when they are stored on media. The data is transparently decrypted for authorized users or applications when they access the data. See Transparent Data Encryption for more information.

This article describes how to integrate SDKMS to be used with SQL Server TDE. The steps below have been tested for MSSQL version: 2014.

Before starting, download the SDKMS client installer for Windows 64-bit from Resources.

Installation

FortanixKmsClient.msi installs the SDKMS CNG Provider, as well as the EKM provider and the PKCS#11 library.

Configuration for Windows Client

The Fortanix KMS Server URL and proxy information are configured in the Windows registry for the local machine or current user with C:\Program Files\Fortanix\KmsClient\FortanixKmsClientConfig.exe.

The machine key store uses the local machine configuration, and the user key store uses the current user configuration.

For example, to configure the Fortanix KMS Server URL for the local machine, run

FortanixKmsClientConfig.exe machine --api-endpoint https://sdkms.fortanix.com

To configure the Fortanix KMS Server URL for the current user, run

FortanixKmsClientConfig.exe user --api-endpoint https://sdkms.fortanix.com

To configure proxy information, add --proxy http://proxy.com or --proxy none to unconfigure proxy.

Configuration for SQL Server

To Configure SQL Server to use Fortanix EKM Provider, run the following commands in SQL Server Studio:

Use the correct location of the EKM DLL.

CREATE CRYPTOGRAPHIC PROVIDER EKM_Prov
FROM FILE = 'C:\Program Files\Fortanix\KmsClient\FortanixKmsEkmProvider.dll' ;
GO

**Figure 1: Create cryptographic provider**

In case of error such as "Extensible key management is not supported or enabled in this edition of SQL server":

Run the following commands:

sp_configure 'show advanced', 1
GO
RECONFIGURE
GO
sp_configure 'EKM provider enabled', 1
GO
RECONFIGURE
GO

**Figure 3: Run commands for error scenario**

Now, re-run the following command:

CREATE CRYPTOGRAPHIC PROVIDER EKM_Prov
FROM FILE = 'C:\Program Files\Fortanix\KmsClient\FortanixKmsEkmProvider.dll' ;
GO

Create a credential that will be used by the system administrators. See the Getting Started guide on how to generate an API Key.
```
CREATE CREDENTIAL sa_ekm_tde_cred
WITH IDENTITY = 'Identity1',
SECRET = '<API Key>'
FOR CRYPTOGRAPHIC PROVIDER EKM_Prov ;
GO
```
For getting the API Key:
1. Go to https://sdkms.fortanix.com/#/
2. In the UI left panel, click the Apps tab: https://sdkms.fortanix.com/#/apps
3. Click COPY API KEY to copy the API key of your application, and then paste the key in the SECRET field of the command above.
  
  Figure 4: Copy API key
4. Run the command again to create a credential using the copied API key in your SQL Studio Server.
```
CREATE CREDENTIAL sa_ekm_tde_cred
WITH IDENTITY = 'Identity1',
SECRET = 'NjUyMTg1OGMtNmM5Zi00ODFjLWEzNWEtNWUyYWFhZWViOTM5OlZoUzdjanhEa3d6RmltYVZMQWloUFZWZ0Z6VVJGbkx2NmhYQkNzY2VmTnR4MkhINkhYLUlEeWtYelk0cWh2YWhGZmF1emFUQ1ZRdkI2b0dOQzk5ZHVB'
FOR CRYPTOGRAPHIC PROVIDER EKM_Prov ;
GO
```
  Figure 5: Create credential
Add the credential to a high privileged user such as your own domain login in the format [DOMAIN\login].
```
ALTER LOGIN EC2AMAZ-1RDPAEU\Administrator
ADD CREDENTIAL sa_ekm_tde_cred ;
GO
```
In case there is no domain and the machine is part of a workgroup/standalone, please run the commands below:
```
ALTER LOGIN MSSQL2017
ADD CREDENTIAL "sa_ekm_tde_cred";
GO
```
Figure 6: Command for no domain

In case you are unable to alter the login, open the Object Explorer and Map the Credentials as shown below.

Figure 7: Map credentials

Create an asymmetric key stored inside the EKM provider.

USE master ;
GO
CREATE ASYMMETRIC KEY ekm_login_key
FROM PROVIDER [EKM_Prov]
WITH ALGORITHM = RSA_2048,
PROVIDER_KEY_NAME = 'SQL_Server_Key' ;
GO

Create a credential that will be used by the Database Engine.

USE master ;
CREATE CREDENTIAL ekm_tde_cred
WITH IDENTITY = 'Identity2'
, SECRET = '<API Key>'
FOR CRYPTOGRAPHIC PROVIDER EKM_Prov ;

**Figure 9: Create credential for database engine**

Add a login used by TDE, and add the new credential to the login.

CREATE LOGIN EKM_Login
FROM ASYMMETRIC KEY ekm_login_key ;
GO
ALTER LOGIN EKM_Login
ADD CREDENTIAL ekm_tde_cred ;
GO

**Figure 10: Add the new credential to the login**

Create table employee.

CREATE TABLE employee (first_name VARCHAR2(128),last_name VARCHAR2(128),empID DECIMAL,salary DECIMAL(6));
CREATE DATABASE employee
GO

Create the database encryption key that will be used for TDE.

USE employee
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM  = AES_256
ENCRYPTION BY SERVER ASYMMETRIC KEY ekm_login_key ;
GO

**Figure 12: Create Database encryption key**

Alter the database to enable transparent data encryption.
```
ALTER DATABASE employee
SET ENCRYPTION ON ;
GO
```
Figure 13: Enable transparent data encryption

Rotating Keys for Transparent Data Encryption (TDE) in Fortanix

Fortanix recommends updating your TDE security keys regularly by rotating the available symmetric and asymmetric encryption keys. Execute the following from the SQL query window.

Rotate keys for TDE

Generate an asymmetric key using the Fortanix External Key Manager (EKM) Provider.

Use master;
CREATE ASYMMETRIC KEY SQL_EKM_KEY_v7
FROM Provider EKM_Prov
WITH ALGORITHM = RSA_2048,
PROVIDER_KEY_NAME = 'SQL_EKM_KEY_v7',
CREATION_DISPOSITION=CREATE_NEW

Where SQL_EKM_KEY_v7 is the new rotation key.

Create a CREDENTIAL for EKM Provider created by Fortanix.
```
CREATE CREDENTIAL <Name of credential> 
WITH IDENTITY='<Name of EKM User>', SECRET='<SDKMS API KEY>' 
FOR CRYPTOGRAPHIC PROVIDER EKM_Prov
```
Figure 15: Create Credential for EKM Provider

Where Credential and Identity can be as per your internal organization policy.
Create a login based on the recently created asymmetric key.
```
CREATE LOGIN <Name of login> 
FROM ASYMMETRIC KEY SQL_New Rotation key;
```
Figure 16: Create Login for Asymmetric Key
Map the credential to the recently created login.
```
ALTER LOGIN <Name of Login> 
ADD CREDENTIAL <Name of credential>; 
```
Figure 17: Map the Credential

Enable Transparent Database Encryption Key Rotation.

Use employee; 
ALTER DATABASE ENCRYPTION KEY 
REGENERATE 
WITH ALGORITHM = AES_128 
ALTER DATABASE ENCRYPTION KEY 
ENCRYPTION BY SERVER ASYMMETRIC KEY SQL_New Rotation key; 
go 
SELECT * FROM sys.dm_database_encryption_keys 
go

Where employee is the Database name.