---
title: "Fortanix DSM with MSSQL Server TDE - Mirror Database"
slug: "fortanix-dsm-with-mssql-server-tde-mirror-database"
updated: 2026-04-17T16:38:01Z
published: 2026-04-17T16:38:01Z
canonical: "support.fortanix.com/fortanix-dsm-with-mssql-server-tde-mirror-database"
---

> ## 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 MSSQL Server TDE - Mirror Database

## 1.0 Introduction

This article provides detailed steps for seamlessly integrating Microsoft SQL Server with Transparent Data Encryption (TDE) in a database configured for mirroring. The procedures outlined here have been thoroughly tested on two servers: Server 1, serving as the nominal principal node, and Server 2, the nominal mirror node. Notably, the configuration employs Fortanix Hardware Security Modules (HSMs) without shared disk architecture.

These integration steps apply to each database, including both the primary and replicas, for which TDE encryption is to be enabled. Before initiating the integration, it is assumed that the target database:

- Already exists.
- Is not currently encrypted and does not contain a Data Encryption Key (TDE DEK).
- Has never been encrypted previously. If it has, you may encounter errors requiring a transaction log backup.

> [!NOTE]
> NOTE
> 
> The examples provided herein use a database named `SourceDatabase` for encryption, and the database wrapping key is referenced as `SQL_Server_Key` in the Fortanix-Data-Security-Manager (DSM) and as `ekm_login_key` in the master database. You can customize names and parameters according to your specific requirements. Additionally, these steps also assume that no wrapping key with the same name already exists in either Fortanix DSM or the master database.

Before proceeding with the integration, ensure the following prerequisites:

- The database has been recently backed up.
- If you have a preferred server for the principal role, ensure a failover to that server.
- Ensure that the initial roles (principal or replica) are assigned to each server role.

Follow the subsequent steps in the specified order. The article assumes that the server nodes retain their initial principal or replica state.

> [!NOTE]
> NOTE
> 
> Ensure that you have performed the steps from the [*Data Security Manager with Microsoft SQL TDE Integration – Before You Begin*](/v1/docs/data-security-manager-with-microsoft-sql-server-tde-integration-before-you-begin) guide*.*

## 2.0 TDE Configurations

Perform the following procedures on both the primary and secondary replicas.

### 2.1 Enabling TDE Features

Run the following commands if Extensible Key Management (EKM) is not supported or enabled in the SQL Server Edition:

```bash
sp_configure 'show advanced', 1
GO
RECONFIGURE
GO
sp_configure 'EKM provider enabled', 1
GO
RECONFIGURE
GO
```

![Picture1.png](https://cdn.us.document360.io/c3bd85d2-4ad8-4d85-9f60-f1c168a3aad9/Images/Documentation/23457983184404.png)

**Figure 1: Run commands for error scenario**

### 2.2 Creating Cryptographic Provider

Run the following commands to use the correct location of the `EKM DLL`:

```bash
CREATE CRYPTOGRAPHIC PROVIDER EKM_Prov
FROM FILE = 'C:\Program Files\Fortanix\KmsClient\FortanixKmsEkmProvider.dll' ;
GO
```

Where, `EKM_Prov` is the name of the provider defined by the user.

![Picture2.png](https://cdn.us.document360.io/c3bd85d2-4ad8-4d85-9f60-f1c168a3aad9/Images/Documentation/23457983213204.png)

**Figure 2: Create a cryptographic provider**

### 2.3 Creating Credentials (SYSadmin)

This section describes the steps to create the credentials to generate the Master Encryption Key (MEK) on the Fortanix DSM using the SQL admin.

1. Run the following commands to create a credential in your SQL Server Studio that will be used by the system administrators:

```bash
CREATE CREDENTIAL sa_ekm_tde_cred
WITH IDENTITY = 'Identity1',
SECRET = '<DSM_API_KEY>' 
FOR CRYPTOGRAPHIC PROVIDER EKM_Prov ;
GO
```

Where, `&lt;DSM_API_KEY&gt;` is the Fortanix DSM API key as copied in [*Fortanix Data Security Manager with Microsoft SQL Server TDE Integration - Before You Begin*](https://support.fortanix.com/docs/data-security-manager-with-microsoft-sql-server-tde-integration-before-you-begin#35-copying-the-api-key).

![Picture4.png](https://cdn.us.document360.io/c3bd85d2-4ad8-4d85-9f60-f1c168a3aad9/Images/Documentation/Screenshot (870).png)

**Figure 3: Create a credential**
2. Add the credential to a highly privileged user, such as your own domain login, in the format `[DOMAIN\login]`:

```bash
ALTER LOGIN EC2AMAZ-1RDPAEU\Administrator
ADD CREDENTIAL "sa_ekm_tde_cred";
GO
```

Run the following commands in case there is no domain, and the machine is part of a workgroup or standalone:

```bash
ALTER LOGIN LOCALHOST\Administrator
ADD CREDENTIAL "sa_ekm_tde_cred";
GO
```

![Picture5.png](https://cdn.us.document360.io/c3bd85d2-4ad8-4d85-9f60-f1c168a3aad9/Images/Documentation/23457950948500.png)

**Figure 4: Command for no domain**

If you are not an administrator and hence unable to alter the login, right-click the **Properties** and select the **Object Explorer** option. Navigate to **Security** → **Logins** → **<login user>** and map the credentials as shown in the following image:

![Picture6.png](https://cdn.us.document360.io/c3bd85d2-4ad8-4d85-9f60-f1c168a3aad9/Images/Documentation/23458265266068.png)

**Figure 5: Map credentials**

### 2.4 Creating an Asymmetric Key

The MSSQL admin has the credentials associated with creating the MEK on the Fortanix DSM. This section describes the steps to create the asymmetric key.

#### 2.4.1 On Principle Replica

Run the following commands to create an asymmetric key stored inside the EKM provider:

```bash
USE master ;
GO
CREATE ASYMMETRIC KEY ekm_login_key
FROM PROVIDER EKM_Prov
WITH ALGORITHM = RSA_2048,
CREATION_DISPOSITION=CREATE_NEW,
PROVIDER_KEY_NAME = 'SQL_Server_Key'; 
GO
```

Where,

- `ekm_login_key`: Refers to the master key alias on the MSSQL database.
- `EKM_Prov`: Refers to the Fortanix EKM Provider.
- `SQL_Server_Key`: Refers to the key created on the Fortanix DSM.

> [!NOTE]
> NOTE
> 
> It is recommended to add versions to the Fortanix DSM keys for an easier key rotation process.

For example,

```bash
USE master;
GO
CREATE ASYMMETRIC KEY ekm_login_key_v1
FROM PROVIDER [EKM_Prov]
WITH ALGORITHM = RSA_2048,
CREATION_DISPOSITION=CREATE_NEW,
PROVIDER_KEY_NAME = 'SQL_Server_Key_v1';
GO
```

![Picture7.png](https://cdn.us.document360.io/c3bd85d2-4ad8-4d85-9f60-f1c168a3aad9/Images/Documentation/23457983297556.png)

**Figure 6: Create an asymmetric key**

#### 2.4.2 On Replica Nodes

Run the following commands to create an asymmetric key on all the replica nodes:

```bash
USE master
CREATE ASYMMETRIC KEY ekm_login_key 
FROM PROVIDER EKM_Prov
WITH PROVIDER_KEY_NAME='SQL_Server_Key',
CREATION_DISPOSITION = OPEN_EXISTING; 
GO
```

Where,

- `ekm_login_key`: Refers to the master key alias on the MSSQL database.
- `EKM_Prov`: Refers to the Fortanix EKM Provider.
- `SQL_Server_Key`: Refers to the key created on the Fortanix DSM.

### 2.5 Creating Credentials (DB Engine)

Run the following commands to create a credential that will be used by the database engine:

```bash
USE master;
CREATE CREDENTIAL ekm_tde_cred
WITH IDENTITY = 'Identity2',
SECRET = '<DSM_API_KEY>'
FOR CRYPTOGRAPHIC PROVIDER EKM_Prov;
```

Where,

- `ekm_tde_cred`: Refers to the name of the credentials.
- `Identity2`: Refers to the identity name. The value can be any name.
- `EKM_Prov`: Refers to the Fortanix EKM Provider.
- `SECRET`: Refers to the Fortanix DSM API key as copied in [*Fortanix Data Security Manager with Microsoft SQL Server TDE Integration - Before You Begin*](https://support.fortanix.com/docs/data-security-manager-with-microsoft-sql-server-tde-integration-before-you-begin#35-copying-the-api-key).

![Picture8.png](https://cdn.us.document360.io/c3bd85d2-4ad8-4d85-9f60-f1c168a3aad9/Images/Documentation/Screenshot (871).png)

**Figure 7: Create credential for database engine**

### 2.6 Creating Login (DB Engine)

Run the following commands to add a login used by TDE and add the new credential to the login:

```bash
CREATE LOGIN EKM_Login
FROM ASYMMETRIC KEY ekm_login_key ;
GO
ALTER LOGIN EKM_Login
ADD CREDENTIAL ekm_tde_cred ;
GO
```

Where,

- `ekm_login_key`: Refers to the master key alias on the MSSQL database. This key is already created in [*Section 2.4- Creating an Asymmetric Key*](/v1/docs/using-fortanix-data-security-manager-with-mssql-server-tde-mirror-database#24-creating-symmetric-keys).
- `EKM_Login`: Refers to the login name.
- `ekm_tde_cred`: Refers to the key created on Fortanix DSM. This credential is already created in [*Section 2.3- Creating Credentials (SYSadmin)*](/v1/docs/using-fortanix-data-security-manager-with-mssql-server-tde-mirror-database#23-creating-credentials-sysadmin).

![Picture9.png](https://cdn.us.document360.io/c3bd85d2-4ad8-4d85-9f60-f1c168a3aad9/Images/Documentation/23457951024660.png)

**Figure 8: Add new credential to login**

## 3.0 Enabling Encryption

Perform the following steps only on the **principal node** and monitor the status using the monitoring command on the principal or replica nodes.

### 3.1 Creating Data Encryption Key (DEK)

Run the following commands to create the Data Encryption Key (DEK) that will be used for TDE:

```bash
USE employee
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER ASYMMETRIC KEY ekm_login_key ;
GO
```

Where,

- `employee`: Refers to the database name.
- `ekm_login_key`: Refers to the master key alias on the MSSQL database.

### 3.2 Enabling TDE on Database

#### 3.2.1 Prechecks

Perform the following prechecks before enabling TDE to make sure that the database is free of corruption by running an integrity check using `DBCC CHECKDB`.

- **Basic Check**: The following command verifies the logical and physical integrity of all objects in the specified database.

```bash
DBCC CHECKDB('<DB Name>');
```
- **Recommended (Detailed) Check:** The following command performs the same integrity check, suppressing non-essential messages (`NO_INFOMSGS`) and displaying all errors in detail (`ALL_ERRORMSGS`), making it easier to identify and review issues.

```bash
DBCC CHECKDB('<DB Name>') WITH NO_INFOMSGS, ALL_ERRORMSGS;
```

#### 3.2.2 Enabling TDE

Run the following commands to alter the database to enable Transparent Data Encryption (TDE):

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

![Picture10.png](https://cdn.us.document360.io/c3bd85d2-4ad8-4d85-9f60-f1c168a3aad9/Images/Documentation/23457983377044.png)

**Figure 9Using Fortanix DSM for Google Workspace Client-Side Encryption: Enable TDE**

## 4.0 Monitoring TDE Progress

> [!NOTE]
> NOTE
> 
> You must monitor the TDE on all the replicas to ensure that TDE is enabled on all the target servers.

SQL Server keeps track of the encryption progress, and we can pull that information by querying `sys.dm_database_encryption_keys`. Particularly, `‘Percent_Complete’` and `‘encryption_state’` are the two columns that are required to understand the progress of TDE. `‘encryption_state’` column returns an integer value (0-6) which indicates the encryption status of the database and `‘percent_complete’` column tells us the percent completed of the DB encryption state change.

| **Encryption_state (int)** | **Description** |
| --- | --- |
| 0 | No database encryption key present, no encryption |
| 1 | Unencrypted |
| 2 | Encryption in progress |
| 3 | Encrypted |
| 4 | Key change in progress |
| 5 | Decryption in progress |
| 6 | Protection changes in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed). |

The following T-SQL statement can be used to monitor TDE progress or status:

```bash
SELECT DB_NAME(database_id) AS DatabaseName, encryption_state,
encryption_state_desc =
CASE encryption_state
WHEN '0' THEN 'No database encryption key present, no encryption'
WHEN '1' THEN 'Unencrypted'
WHEN '2' THEN 'Encryption in progress'
WHEN '3' THEN 'Encrypted'
WHEN '4' THEN 'Key change in progress'
WHEN '5' THEN 'Decryption in progress'
WHEN '6' THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)'
ELSE 'No Status'
END,
percent_complete,encryptor_thumbprint, encryptor_type FROM sys.dm_database_encryption_keys
```

The output of this query comes in handy to manage TDE.

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 TDE - Standalone Server](/fortanix-dsm-with-microsoft-sql-server-tde-integration-standalone-server.md)
- [Fortanix DSM with Microsoft SQL Server TDE - Introduction](/fortanix-dsm-with-microsoft-sql-server-tde-integration-introduction.md)
- [CMK Rotation - Fortanix DSM Version Below 5.6](/cmk-rotation-fortanix-dsm-version-below-5-6.md)
- [Data Center Labeling](/fortanix-data-security-manager-data-center-labeling.md)