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. 

NOTE
To enable this TF, please refer to the steps in the following URL:
https://learn.microsoft.com/en-us/troubleshoot/sql/releases/sqlserver-2022/database-accessibility-issues-high-volume-customer-workloads 

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.
        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 to Microsoft SQL Server EKM provider will uninstall the old CNG provider and install the latest downloaded version.
  • It is highly recommended to plan for any potential downtime.
  • Make sure to take the latest backup of your database to ensure the safety and security of your data.
  1. Download the latest CNG client from
    https://support.fortanix.com/hc/en-us/articles/360018084132-CNG-EKM.
  2. Check the current EKM library installation using the following command:
    SELECT [provider_id]
    [name]
    ,[guid]
    ,[version]
    ,[dll_path]
    ,[is_enabled]
    FROM [model].[sys].[cryptographic_providers]
    
    SELECT [provider_id],[guid],[provider_version]
    ,[sqlcrypt_version]
    ,[friendly_name]
    ,[authentication_type]
    ,[symmetric_key_support]
    ,[symmetric_key_persistance]
    ,[symmetric_key_export]
    ,[symmetric_key_import]
    ,[asymmetric_key_support]
    ,[asymmetric_key_persistance]
    ,[asymmetric_key_export]
    ,[asymmetric_key_import]
    FROM [master].[sys].[dm_cryptographic_provider_properties]
    
  3. Disable the cryptographic provider using the following command:
    ALTER CRYPTOGRAPHIC PROVIDER EKM_Prov_name  
    DISABLE;
    GO
    
  4. Uninstall the old library.
  5. Install a new library and verify it using the following commands: Set the below environment variables using the command line.
    Fortanixkmsclient.exe machine --api-endpoint  // should pass
    Fortanixkmsclient.exe machine --api-key api_key 	// pass
    
  6. Register the new library using the following command:
    ALTER CRYPTOGRAPHIC PROVIDER EKM_Prov_name
    ALTER CRYPTOGRAPHIC PROVIDER EKM_Prov_name
    FROM FILE = 'C:\Program Files\Fortanix\KmsClient\FortanixKmsEkmProvider.dll'
    
  7. Enable the cryptographic provider.
    ALTER CRYPTOGRAPHIC PROVIDER EKM_Prov
    Enable;
    GO
    
  8. Restart the MSSQL services. Once done, the latest library is now installed.
NOTE
For cluster setup, it is recommended to upgrade the secondary nodes before upgrading the primary.

Comments

Please sign in to leave a comment.

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