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.
        SQL_SERVER_MANAGEMENT_STUDIO.png
        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. 

NOTE

Database administrators can try adapting the mentioned commands according to their requirements.

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
NOTE
You should have provided a path to your backups when setting up your availability group.
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

NOTE
The upgrade from Fortanix CNG provider for Microsoft SQL Server EKM provider will uninstall the old CNG provider and install the latest downloaded version.
  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.
    FORTANIX_KMS_CLIENT_SETUP.png
    Figure 2: Fortanix KMS Client Setup
  5. Restart the Microsoft SQL Server service.
NOTE
For cluster setup, it is recommended to upgrade the secondary nodes before upgrading primary.

Comments

Please sign in to leave a comment.

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