Using Fortanix Data Security Manager with MSSQL Server TDE - Mirror Database

1.0 Introduction

This document provide detailed steps for seamlessly integrating Microsoft SQL with Transparent Data Encryption (TDE) in a database configured for mirroring. The procedures outlined here have been rigorously tested on two servers: Server 1, serving as the nominal principal node, and Server 2, the nominal mirror node. Notably, the configuration employs Fortanix Hardware Security Modules (HSMs) without shared disk architecture.

These integration steps are applicable to each database, including both the primary and replicas, where TDE encryption activation is desired. Prior to initiating the integration, it is assumed that the target database:

  • Already exists.
  • Is not currently encrypted and lacks a Data Encryption Key (TDE DEK).
  • Has never been encrypted previously. In cases of prior encryption, errors may occur, necessitating a log backup.
NOTE
The examples provided herein use a database named SourceDatabase for encryption, and the database wrapping key is referenced as SQL_Server_Key in the Fortanix Data Security Manager (DSM) and ekm_login_key in the master database. You can customize names and parameters according to your specific requirements. Additionally, these steps presuppose the absence of an existing wrapping key with the same name in the Fortanix DSM or the master database.

Before proceeding with the integration, ensure the following prerequisites:

  • The database has been recently backed up.
  • If you have a preferred server for the principal role, ensure a failover to that server.
  • Ensure that the initial roles (principal or replica) are assigned to each server role.

Follow the subsequent steps in the specified order. The description assumes that the server nodes retain their initial principal or replica state.

NOTE
Ensure that you have performed the steps mentioned in Section: Prerequisites in the Data Security Manager with MSSQL TDE Integration Guide – Before You Begin before progressing with the integration steps.

2.0 TDE Configurations

Perform the following procedures on both the primary and secondary replicas.

2.1 Enabling TDE 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

Picture1.png

Figure 1: Run Commands for Error Scenario

2.2 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.

Picture2.png

Figure 2: Create Cryptographic Provider

2.3 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 admin.

The SQL admin 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 the COPY API KEY button to copy the API key of your application, and then paste the Fortanix DSM API key as the value for the SECRET parameter in the next command.

      Picture3.png
      Figure 3: Copy API Key
  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
    Picture4.png
    Figure 4: Create Credential
  3. 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

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

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

    Picture5.png
    Figure 5: Command for No Domain

    If you are not an administrator and hence unable to alter the login, right click the Properties and select the Object Explorer option. Navigate to SecurityLogins<login user> and map the credentials as shown in the following image:

    Picture6.png
    Figure 6: Map Credentials

2.4 Creating Symmetric Keys

The MSSQL admin has the credentials associated with creating the MEK on the Fortanix DSM. This section describes the steps to create the asymmetric key.

2.4.1 On Principle Replica

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,
CREATION_DISPOSITION=CREATE_NEW,
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.
NOTE
It is recommended to add versions to the Fortanix DSM keys for easier key rotation process.

For example:

USE master;
GO
CREATE ASYMMETRIC KEY ekm_login_key_v1
FROM PROVIDER [EKM_Prov]
WITH ALGORITHM = RSA_2048,
CREATION_DISPOSITION=CREATE_NEW,
PROVIDER_KEY_NAME = 'SQL_Server_Key_v1';
GO
Picture7.png
Figure 7: Create Asymmetric Key

2.4.2 On Replica Nodes

Run the following commands to create an asymmetric key on all the secondary replicas:

USE master
CREATE ASYMMETRIC KEY ekm_login_key
FROM PROVIDER EKM_Prov
WITH PROVIDER_KEY_NAME='SQL_Server_Key',
CREATION_DISPOSITION = OPEN_EXISTING;
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.

2.5 Creating Credentials (DB Engine)

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

USE master;
CREATE CREDENTIAL ekm_tde_cred
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.
Picture8.png
Figure 8: Create Credential for Database Engine

2.6 Creating Login (DB Engine)

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

Where,

Picture9.png

Figure 9: Add New Credential to Login

3.0 Enabling Encryption

Perform the following steps only on the principal node and monitor the status using the monitoring command on the primary or replica nodes.

3.1 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.

3.2 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

Picture10.png

Figure 10: Enable TDE

3.3 Monitoring TDE Progress

NOTE
You must perform monitor the TDE on all the replicas to ensure that TDE enabled on all the target servers.

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.

Encryption_state (int) Description
0 No database encryption key present, no encryption
1 Unencrypted
2 Encryption in progress
3 Encrypted
4 Key change in progress
5 Decryption in progress
6 Protection changes in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed).

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.

Comments

Please sign in to leave a comment.

Was this article helpful?
0 out of 0 found this helpful