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.

• Ensure to take the latest backup before implementation.
• It is recommended to have a dedicated service for sysadmin DB credentials for smoother key rotation.
• Plan the key rotation and key nomenclature based on the requirements for better operational handling.
• Periodic backup restores are recommended to validate the data consistency.
• It is recommended not to delete the old master keys in the Fortanix Data Security Manager (DSM) to restore the old backups if required.

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.