Data Security Manager with Microsoft SQL Server TDE Integration - Advanced

1.0 Introduction

This article describes the best practices for Microsoft SQL Transparent Data Encryption (TDE) operations integration.

2.0 Best Practices

It is recommended to follow the tested best practices.

• Always take a full database backup before TDE implementation.
• Use a separate service account for the sysadmin DB credentials for key rotation.
• Use a nomenclature for the TDE master keys such as hostname_dbName to easily identify the key associated with the database instance running on a specific host.
• It is strongly advised not to delete the old master keys from the Fortanix Data Security Manager (DSM) as they are required to restore the database from old backups. You can always disable the old keys if you do not want anyone to access them.

2.1 Enable Startup Trace Flag

2.1.1 Trace flag 15025

Microsoft introduced a startup trace flag (TF) 15025 to disable the HSM access that is required for a newly created VLF. This allows high-volume customer workloads to continue without interruption. Once this trace flag is enabled, SQL Server that uses EKM for encryption and key generation doesn't contact HSM during the creation or rotation of VLF.

This trace flag applies to SQL Server 2019 (15.x) CU 19, SQL Server 2022 (16.x) CU 1, and later.

Hence, we recommend our customers upgrade the MSSQL Always On database to 2019 CU19 or  2022 CU1 and turn on the trace flag (TF) 15025. 

2.1.2 Trace flag 5013

Microsoft also has a trace flag (TF) 5013 that is undocumented and works with SQL Server 2017 and above. In a high transitional database, SQL always tries to get a key from Fortanix DSM. If there is a connection problem between the SQL database and Fortanix DSM, this flag uses the cache key to avoid the database from crashing.

3.0 Troubleshooting

If your database has been encrypted previously, you may see errors at this point. If you are asked to take a pending log backup, then take the backup using the following methods:

• Using command:
  

  BACKUP LOG employee TO DISK = 'C:\employee.TRN'
  GO
  

  This will create a transaction log backup of the employee database and write the backup contents to file "C:\employee.TRN". The .TRN extension is commonly used to indicate that the backup is a transaction log backup.

• Using SQL Server Management Studio:
  1. Right-click the database name.
  2. Select Tasks > Backup.
  3. Select Transaction Log as the backup type.
  4. Select Disk as the destination.
     1. Click Add.. to add a backup file and type "C:\company.TRN" and click OK.
     2. Click OK again to create the backup.
        
        Figure 1: SQL Server Management Studio

If you get an error requesting that you take a log backup, then try the following commands to resolve the issue. 

USE master;
GO
ALTER DATABASE <Name-of-your-database>
SET RECOVERY FULL;
GO
USE master;
GO

For example:

USE master;
GO
ALTER DATABASE SourceDatabase
SET RECOVERY FULL;
GO
USE master;
GO
EXEC sp_addumpdevice 'disk', 'EncryptedSourceDatabaseBackupLog',
'\\Server-2\NetWorkShareFolder\SourceDatabase_20160210122459';
GO

EXEC sp_addumpdevice 'disk', '<Name-of-your-device>',
‘<Path-to-your-backups>\<Name-of-your-log-backup-file>';
GO

Run the following command to take the back up of logs:

BACKUP LOG <Name-of-your-database> TO <Name-of-your-device>;
GO

For example:

BACKUP LOG SourceDatabase TO EncryptedSourceDatabaseBackupLog;
GO

Run the following command to drop the backup device:

EXEC sp_dropdevice '<Name-of-your-device>';

For example:

EXEC sp_dropdevice 'EncryptedSourceDatabaseBackupLog';

Run the following command to break any connection with the SourceDatabase so that encryption can commence:

USE [master];
GO

Run the following command to enable TDE (switch on encryption) on the SourceDatabase:

ALTER DATABASE SourceDatabase SET ENCRYPTION ON;
GO

4.0 Logging

Run the following command on the admin command prompt to find the External Key Management (EKM) logs:

cd c:\
dir /s/a "EkmLog.txt"

The EKM log file is always located at the following path: C:\Windows\ServiceProfiles\MSSQLSERVER\AppData\Roaming\Fortanix\KmsClient.

Where, MSSQLSERVER will change according to your local setup.

5.0 Upgrading KMS Client

1. Download the latest CNG client from
   https://support.fortanix.com/hc/en-us/articles/360018084132-CNG-EKM.
2. It is recommended to plan the downtime.
3. Ensure to take the latest database backup.
4. Install the updated client.
   
   Figure 2: Fortanix KMS Client Setup
5. Restart the Microsoft SQL Server service.