---
title: "Fortanix DSM with Microsoft SQL Server TDE - Advanced"
slug: "fortanix-dsm-with-microsoft-sql-server-tde-integration-advanced"
updated: 2026-04-01T08:17:07Z
published: 2026-03-11T21:34:12Z
canonical: "support.fortanix.com/fortanix-dsm-with-microsoft-sql-server-tde-integration-advanced"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://support.fortanix.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Fortanix DSM with Microsoft SQL Server TDE - Advanced

## 1.0 Introduction

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

## 2.0 MSSQL TDE Best Practices

It is recommended to follow the MSSQL TDE 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 virtual log file (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]
> NOTE
> 
> To enable this TF, refer to [*Database accessibility issues with high-volume customer workloads that use EKM for encryption and key generation*](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 and 2019 CU 19 below. 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:**

```bash
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 the 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](https://cdn.us.document360.io/c3bd85d2-4ad8-4d85-9f60-f1c168a3aad9/Images/Documentation/13098861376020.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]
> NOTE
> 
> Database administrators can try adapting the mentioned commands according to their requirements.

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

For example,

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

```bash
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 a backup of logs:

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

For example,

```bash
BACKUP LOG SourceDatabase TO EncryptedSourceDatabaseBackupLog;
GO
```

Run the following command to drop the backup device:

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

For example,

```bash
EXEC sp_dropdevice 'EncryptedSourceDatabaseBackupLog';
```

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

```bash
USE [master];
GO
```

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

```bash
ALTER DATABASE SourceDatabase SET ENCRYPTION ON;
GO
```

## 4.0 Logging

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

```bash
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]
> 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.
> - 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 [Fortanix CNG/EKM](https://fortanix.zendesk.com/hc/en-us/sections/27488968118932-CNG-EKM).
2. Check the current EKM library installation.

```bash
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.

```plaintext
ALTER CRYPTOGRAPHIC PROVIDER EKM_Prov_name  
DISABLE;
GO
```
4. Uninstall the old library.
5. Install a new library and verify it. Set the below environment variables.

```bash
Fortanixkmsclient.exe machine --api-endpoint <endpoint_URL>
```
6. Register the new library.

```bash
ALTER CRYPTOGRAPHIC PROVIDER EKM_Prov_name
FROM FILE = 'C:\Program Files\Fortanix\KmsClient\FortanixKmsEkmProvider.dll'
```
7. Enable the cryptographic provider.

```bash
ALTER CRYPTOGRAPHIC PROVIDER EKM_Prov
Enable;
GO
```
8. Restart the MSSQL services.

> [!NOTE]
> NOTE
> 
> For cluster setup, it is recommended to upgrade the secondary nodes before upgrading the primary.

The latest library is now installed on the system.

## 6.0 Frequently Asked Questions

---

### 

#### Is there a way to recover the encryption key from the SQL wrapping key?

There is currently no way to recover the encryption key from the SQL wrapping key. However, in the case of a clustered SQL environment, you can use the read-only SQL node to get the key.

Fortanix Data Security Manager (DSM) is the world’s first cloud service secured with Intel® SGX. With Fortanix DSM, you can securely generate, store, and use cryptographic keys and certificates, as well as other secrets such as passwords, API keys, tokens, or any blob of data. Your business-critical applications and containers can integrate with Fortanix DSM using legacy cryptographic interfaces (PKCS#11, CNG, and JCE) or using the native Fortanix DSM RESTful interface.

## Related

- [Fortanix DSM with Microsoft SQL Server - Always Encrypted](/fortanix-dsm-with-microsoft-sql-server-always-encrypted.md)
- [Fortanix DSM with Microsoft SQL Server TDE - Before You Begin](/fortanix-dsm-with-microsoft-sql-server-tde-integration-before-you-begin.md)
- [Fortanix DSM with Microsoft SQL Server TDE - Introduction](/fortanix-dsm-with-microsoft-sql-server-tde-integration-introduction.md)