Fortanix Data Security Manager with Microsoft SQL Server TDE Integration - Key Rotation

1.0 Introduction

Fortanix recommends updating your Microsoft Transparent Data Encryption (TDE) security keys regularly by rotating the available symmetric and asymmetric encryption keys.

This article describes the step-by-step procedure for key rotation on standalone and SQL Always On Availability Group (AOG) setup.

Before proceeding with the key rotation steps, ensure the following for the SQL AOG setup only:

• The database was recently backed up.

• The primary and secondary replicas are synchronized within the availability group, and that failover can occur without any data loss.

• If you prefer a particular server for the primary role, then you are switched over to that server.

• You must remember the roles (primary or secondary) with which each server node begins.

2.0 Rotating keys for TDE

To rotate the keys for TDE, you must be logged in as a service account or system administrator user to rotate the Master Encryption Key (MEK).

2.1 Creating Credentials (sysadmin)

This section describes the steps to create the credentials to generate the Master Encryption Key (MEK) on the Fortanix-Data-Security-Manager (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 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 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 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:

   

2.2 Creating Asymmetric Key

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

2.2.1 On Standalone Server/AOG Primary Replica

Run the following commands to generate a new asymmetric key using the Fortanix External Key Manager (EKM) Provider:

USE master ;
GO
CREATE ASYMMETRIC KEY ekm_login_key_v2
FROM PROVIDER EKM_Prov
WITH ALGORITHM = RSA_2048,
PROVIDER_KEY_NAME = 'SQL_Server_Key_v2' ;
GO

Where,

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

• EKM_Prov refers to the Fortanix EKM Provider.

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

2.2.2 On AOG Secondary Replica

Run the following commands to generate an asymmetric key using the Fortanix External Key Manager (EKM) Provider:

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.3 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 = 'Identity_v2',
SECRET = '{key}'
FOR CRYPTOGRAPHIC PROVIDER EKM_Prov

Where,

• ekm_tde_cred_v2 refers to the name of the credentials.

• EKM_Prov refers to the Fortanix EKM Provider.

• SECRET refers to the Fortanix DSM API key.

2.4 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_v2
FROM ASYMMETRIC KEY ekm_login_key_v2 ;
GO
ALTER LOGIN EKM_Login_v2
ADD CREDENTIAL ekm_tde_cred_v2 ;
GO

Where,

• ekm_login_key_v2 refers to the master key alias on the MSSQL database. This key is already created in Section 2.2- Creating Asymmetric Keys.

• EKM_Login_v2 refers to the login name.

• ekm_tde_cred_v2 refers to the key created on the Fortanix DSM. This credential is already created in Section 2.3- Creating Credentials (DB Engine).

2.5 Rotating Master Encryption Key (MEK)

Run the following commands on the standalone server or primary replica to rotate the Master Encryption Key (MEK) for Data Encryption Key (DEK):

USE employee;
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER ASYMMETRIC KEY ekm_login_key_v2;

Where, employee is the Database name. 

 For the old key, the encryptor_thumbprint is the Fortanix UUID of the old key.

After the key rotation, the encryptor_thumbprint changes to the new key UUID.

Run the following command if you want to rotate both DEK and MEK:

USE employee;
ALTER DATABASE ENCRYPTION KEY
REGENERATE
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER ASYMMETRIC KEY ekm_login_key_v2

This command re-encrypts the whole data. Therefore, it is recommended to plan the downtime before running the command.