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

1.0 Introduction

This article describes how to integrate Fortanix-Data-Security-Manager (DSM) with Microsoft SQL Server Transparent Data Encryption (TDE).

Microsoft Cryptographic Next Generation (CNG) Provider enables the use of Fortanix Data Security Manager (DSM) for the storage of cryptographic keys and cryptographic operations such as key creation, deletion, encryption, decryption, and so on.

TDE is more secure as the encryption keys do not reside with the encrypted data. The data can be encrypted using Data Encryption Keys (DEK) and these encryption keys are protected by Master Encryption Key (MEK), which reside in Fortanix DSM.

1.1 Product Version Tested

The following versions are supported in Fortanix DSM:

• Windows: v2012, v2016, and v2020

• Fortanix DSM: from v3.0 to latest

• Fortanix CNG Provider: from v3.0 to latest

• SQL Server TDE Features

  Feature	Standard	Enterprise	Web	Express with Advanced Services	Express
  SQL Server 2022	Y	Y	Y	N	N
  SQL Server 2019	Y	Y	Y	N	N
  SQL Server 2017	N	Y	N	N	N
  SQL Server 2016	N	Y	N	N	N
  SQL Server 2014	N	Y	N	N	N
  SQL Server 2012	N	Y	N	N	N
  SQL Server 2008 R2	N	Y	N	N	N
  SQL Server 2008	N	Y	N	N	N

2.0 TDE Concepts

The SQL server encrypts data with a hierarchical encryption and key management infrastructure. Each layer encrypts the layer below it by using a combination of certificates, asymmetric keys, and symmetric keys. Asymmetric and symmetric keys can be stored outside of the SQL server in an Extensible Key Management (EKM) module.

Before enabling TDE, a DEK must be created which is used to encrypt the contents of the database. It is a symmetric key and supported algorithms are AES with 128-bit, 192-bit, or 256-bit keys or three Key Triple DES. After TDE is enabled on a database, the DEK is used to encrypt the contents of the database and the log. When TDE is enabled for any database on the server, TempDB is also encrypted and its DEK is managed internally by SQL Server.

TDE also requires creating a Master Encryption Key (MEK) which is created on Fortanix DSM using the EKM module. The recommended algorithm for the master key is RSA 2048 or higher. This MEK is used to encrypt Data Encryption Keys (DEK). See the diagram below for key hierarchy.

2.1 Creating a Master Encryption Key (MEK)

In SQL Transparent Data Encryption (TDE), MEK is an asymmetric key which protects the DEK.

The asymmetric key always resides on the Fortanix DSM, which secures and compliances the database server.

When you restore or attach a TDE database on another server, ensure that the same MEK is present on this server as well.

The following is a sample command to create the Master Encryption Key (MEK):

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 Fortanix DSM.

2.2 Creating Data Encryption Key (DEK)

Data Encryption Key (DEK) is a symmetric key that encrypts the data on the SQL machine.

A symmetric key is one that is used for both encryption and decryption. Encryption and decryption by using a symmetric key is fast and suitable for routine use with sensitive data in the database.

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

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

2.3 Database Credentials

A credential is a record that contains the authentication information (credentials) required to connect to a resource outside of SQL Server.
In TDE configuration, this credential contains the Fortanix API key to connect to the Fortanix DSM. Refer to the Data Security Manager with Microsoft SQL TDE Integration - Before You Begin guide, to understand the API key.

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

Where,

• sa_ekm_tde_cred refers to the name of the credential.

• Identity1 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.

2.4 Database Login (DB Engine)

A login is a security principal, or an entity that a secure system can authenticate.

CREATE LOGIN EKM_Login
FROM ASYMMETRIC KEY ekm_login_key ;
GO

Where,

• EKM_Login refers to the name of the login.

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

3.0 Reference Documents

Refer to the following documents to know the integration procedure in the same sequence as mentioned:

• Data Security Manager with Microsoft SQL TDE Integration - Before You Begin

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

• Data Security Manager with Microsoft SQL TDE Integration - AOG Server Integration

• Data Security Manager with Microsoft SQL TDE Integration - Key Rotation

• Data Security Manager with Microsoft SQL TDE Integration - Backup & Restore

• Data Security Manager with Microsoft SQL TDE Integration - Advanced