Fortanix Data Security Manager with Microsoft SQL Server TDE Integration - Standalone Server

1.0 Introduction

This article is a step-by-step guide to implement Microsoft SQL Transparent Data Encryption (TDE) using the Fortanix-Data-Security-Manager (DSM).

2.0 Enabling SQL Features

Run the following commands if Extensible Key Management (EKM) is not supported or enabled in the SQL Server Edition:

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

3.0 Creating Cryptographic Provider

Run the following commands to use the correct location of the EKM DLL:

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

Where,

• EKM_Prov refers to the name of the provider defined by the user.

4.0 Creating Credentials (sysadmin)

This section describes the steps to create the credentials to generate the Master Encryption Key (MEK) on the Fortanix DSM using the SQL administrator.

The SQL administrator requires permission to connect to Fortanix DSM to generate the key.

1. Perform the following steps to get the API key:

   1. Log in to the Fortanix DSM.

   2. From the UI left panel, click the Apps tab.

   3. Click COPY API KEY to copy the API key of your application and then paste the DSM API key as the value for the SECRET parameter in the next command.

      

2. Run the following commands to create a credential using the copied API key in your SQL Server Studio that will be used by the system administrators:

   CREATE CREDENTIAL sa_ekm_tde_cred
   WITH IDENTITY = 'Identity1',
   SECRET = '<DSM API KEY>' 
   FOR CRYPTOGRAPHIC PROVIDER EKM_Prov ;
   GO

   

3. Add the credential to a high privileged user such as your own domain login in the format [DOMAIN\login]:

   ALTER LOGIN "<Domain>\Administrator"
   ADD CREDENTIAL "sa_ekm_tde_cred";
   GO

   Run the following commands in case there is no domain, and the machine is part of a workgroup or standalone:

   ALTER LOGIN "LOCALHOST\Administrator"
   ADD CREDENTIAL "sa_ekm_tde_cred";
   GO

   

   If you are not an administrator and hence unable to alter the login, open the Object Explorer and map the credentials as shown in the following image:

   

5.0 Creating Asymmetric Key

The MSSQL admin has the credentials associated with creating the Master Encryption Key (MEK) on the Fortanix DSM. This section describes the steps to create the asymmetric keys.

Run the following commands to 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

Where,

• ekm_login_key refers to the master key alias on the MSSQL database.

• EKM_Prov refers to the Fortanix EKM Provider.

• SQL_Server_Key refers to the key created on the Fortanix DSM.

For example:

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

6.0 Creating Credentials (DB Engine)

Run the following commands to create a credential that will be used by the database engine:

USE master ;
GO
CREATE CREDENTIAL ekm_tde_cred_v2
WITH IDENTITY = 'Identity2',
SECRET = '<DSM_API_KEY>'
FOR CRYPTOGRAPHIC PROVIDER EKM_Prov

Where,

• ekm_tde_cred refers to the name of the credentials.

• Identity2 refers to the identity name. The value can be any name.

• EKM_Prov refers to the Fortanix EKM Provider.

• SECRET refers to the Fortanix DSM API Key. Refer to the Section 4.0: Creating Credentials (SysAdmin) to get the DSM API Key.

7.0 Creating Login (DB Engine)

Run the following commands to create a login from an asymmetric key and map credentials to the login:

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

Where,

• ekm_login_key refers to the master key alias on the MSSQL database. This key is already created in Section 5.0: Creating Asymmetric Key.

• EKM_Login refers to the login name.

• ekm_tde_cred refers to the key created on the Fortanix DSM. This credential is already created in Section 6.0: Creating Credentials (DB Engine).

8.0 Creating Sample Database

This section describes the steps for creating sample database to enable TDE.

1. Run the following commands to create database employee:

   CREATE DATABASE employee

2. Run the following commands to create table employee:

   USE employee
   CREATE TABLE employee (first_name VARCHAR(128),last_name VARCHAR(128),empID DECIMAL,salary DECIMAL(6));
   GO

9.0 Creating Data Encryption Key (DEK)

Run the following commands to create the Data Encryption Key (DEK) 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

Where,

• employee refers to the database name.

• ekm_login_key refers to the master key alias on the MSSQL database.

10.0 Enabling TDE on Database

Run the following commands to alter the database to enable Transparent Data Encryption (TDE):

ALTER DATABASE employee
SET ENCRYPTION ON ;
GO

11.0 Monitoring TDE Progress

SQL Server keeps track of the encryption progress, and we can pull that information by querying sys.dm_database_encryption_keys. Particularly ‘Percent_Complete’ and ‘encryption_state’ are the two columns that are required to understand the progress of TDE. ‘encryption_state’ column returns an integer value (0-6) which indicates the encryption status of the database and ‘percent_complete’ column tells us the percent completed of the DB encryption state change.

The following T-SQL statement can be used to monitor TDE progress or status:

SELECT DB_NAME(database_id) AS DatabaseName, encryption_state,
encryption_state_desc =
CASE encryption_state
WHEN '0' THEN 'No database encryption key present, no encryption'
WHEN '1' THEN 'Unencrypted'
WHEN '2' THEN 'Encryption in progress'
WHEN '3' THEN 'Encrypted'
WHEN '4' THEN 'Key change in progress'
WHEN '5' THEN 'Decryption in progress'
WHEN '6' THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)'
ELSE 'No Status'
END,
percent_complete,encryptor_thumbprint, encryptor_type FROM sys.dm_database_encryption_keys

The output of this query comes handy to manage TDE.