---
title: "Fortanix DSM with Microsoft SQL Server TDE - Standalone Server"
slug: "fortanix-dsm-with-microsoft-sql-server-tde-integration-standalone-server"
updated: 2026-04-17T17:11:50Z
published: 2026-04-17T17:11:50Z
canonical: "support.fortanix.com/fortanix-dsm-with-microsoft-sql-server-tde-integration-standalone-server"
---

> ## 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 - Standalone Server

## 1.0 Introduction

This article is a step-by-step guide to implement **Microsoft SQL Transparent Data Encryption (TDE)** using the **Fortanix-Data-Security-Manager (DSM)**.

> [!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 Enabling SQL 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
```

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

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

## 3.0 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.

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

**Figure 2: Create cryptographic provider**

## 4.0 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 administrator.

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).

![Create_Credentials.png](https://cdn.us.document360.io/c3bd85d2-4ad8-4d85-9f60-f1c168a3aad9/Images/Documentation/Screenshot (868).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 "<Domain>\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
```

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

**Figure 4: Command for no domain**

If you are not an administrator and hence unable to alter the login, open the Object Explorer and map the credentials as shown in the following image:

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

**Figure 5: Map credentials**

## 5.0 Creating an Asymmetric Key

The MSSQL admin has the credentials associated with creating the Master Encryption Key (MEK) on the Fortanix DSM. This section describes the steps to create the asymmetric keys.

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,
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,
PROVIDER_KEY_NAME = 'SQL_Server_Key_v1';
GO
```

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

**Figure 6: Create asymmetric key**

## 6.0 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).

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

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

## 7.0 Creating Login (DB Engine)

Run the following commands to create a login from an asymmetric key and map credentials 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 5.0: Creating Asymmetric Key*](/v1/docs/data-security-manager-with-microsoft-sql-server-tde-integration-standalone-server#50-creating-asymmetric-key).
- `EKM_Login`: Refers to the login name.
- `ekm_tde_cred`: Refers to the key created on the Fortanix DSM. This credential is already created in [*Section 6.0: Creating Credentials (DB Engine)*](/v1/docs/data-security-manager-with-microsoft-sql-server-tde-integration-standalone-server#60-creating-credentials-db-engine).

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

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

## 8.0 Creating Sample Database

This section describes the steps for creating a sample database to enable TDE.

1. Run the following commands to create the database `employee`:

```bash
CREATE DATABASE employee
```
2. Run the following commands to create the table `employee`:

```bash
USE employee
CREATE TABLE employee (first_name VARCHAR(128),last_name VARCHAR(128),empID DECIMAL,salary DECIMAL(6));
GO
```

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

**Figure 9: Create table**

## 9.0 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.

## 10.0 Enabling TDE on Database

### 10.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;
```

### 10.2 Enabling TDE

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

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

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

**Figure 10: Enable TDE**

## 11.0 Monitoring TDE Progress

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 - Key Rotation](/fortanix-dsm-with-microsoft-sql-server-tde-integration-key-rotation.md)
- [Fortanix DSM with EDB Postgres for TDE](/fortanix-dsm-with-edb-postgres-for-tde.md)
- [Fortanix DSM with Microsoft SQL Server TDE - Before You Begin](/fortanix-dsm-with-microsoft-sql-server-tde-integration-before-you-begin.md)
- [Definitions](/dsm-definitions.md)
- [Fortanix DSM with Microsoft SQL Server TDE - Introduction](/fortanix-dsm-with-microsoft-sql-server-tde-integration-introduction.md)