1.0 Introduction
This article describes the step-by-step procedure to migrate Microsoft SQL with Transparent Data Encryption (TDE) from local environment (certificate) master key protection to protect the master key using Fortanix-Data-Security-Manager (DSM).
2.0 Terminology References
MEK: Master Encryption Key
DEK: Database Encryption Key
TDE: Transparent Data Encryption
EKM: Extensible Key Management
DSM: Fortanix Data Security Manager
3.0 Configure TDE Using Local Environment
These steps will enable local TDE to demonstrate the migration process. If you have completed these steps, proceed to Section 6.0: Migrate TDE from Local Environment to Fortanix DSM.
3.1 Create Master Key
Run the following command to create the master key inside master database:
USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='MyStrongPassword!!!';
GO
.png?sv=2022-11-02&spr=https&st=2025-05-05T11%3A43%3A33Z&se=2025-05-05T12%3A04%3A33Z&sr=c&sp=r&sig=R2pafkHQIgD7iboDRakYjIM14mbzF6PGd00Zx3e5lXg%3D)
Figure 1: Master Key Created
3.2 Create Certificate Protected by Master Key
Run the following command to create a certificate to protect the Database Encryption Key (DEK):
CREATE CERTIFICATE TDE_Cert_test
WITH
SUBJECT='Database_Encryption';
GO
.png?sv=2022-11-02&spr=https&st=2025-05-05T11%3A43%3A33Z&se=2025-05-05T12%3A04%3A33Z&sr=c&sp=r&sig=R2pafkHQIgD7iboDRakYjIM14mbzF6PGd00Zx3e5lXg%3D)
Figure 2: Certificate Protected by Master Key Created
3.3 Create Database Encryption Key (DEK)
Run the following command to switch to the database where encryption needs to be enabled and create a DEK protected by a certificate:
USE AdventureWorks2012
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert_test;
GO
.png?sv=2022-11-02&spr=https&st=2025-05-05T11%3A43%3A33Z&se=2025-05-05T12%3A04%3A33Z&sr=c&sp=r&sig=R2pafkHQIgD7iboDRakYjIM14mbzF6PGd00Zx3e5lXg%3D)
Figure 3: DEK Created
3.4 Enable Encryption
Run the following command to enable encryption on the required database:
ALTER DATABASE AdventureWorks2012
SET ENCRYPTION ON;
GO
.png?sv=2022-11-02&spr=https&st=2025-05-05T11%3A43%3A33Z&se=2025-05-05T12%3A04%3A33Z&sr=c&sp=r&sig=R2pafkHQIgD7iboDRakYjIM14mbzF6PGd00Zx3e5lXg%3D)
Figure 4: Enable Encryption
4.0 Configure Fortanix DSM
A Fortanix DSM service must be configured, and the URL must be accessible. To create a Fortanix DSM account and group, refer to the following sections:
4.1 Signing Up
To get started with the Fortanix Data Security Manager (DSM) cloud service, you must register an account at <Your_DSM_Service_URL>. For example, https://eu.smartkey.io.
For detailed steps on how to set up the Fortanix DSM, refer to the User's Guide: Sign Up for Fortanix Data Security Manager SaaS documentation.
4.2 Creating an Account
Access the <Your_DSM_Service_URL> on the web browser and enter your credentials to log in to the Fortanix DSM.
.png?sv=2022-11-02&spr=https&st=2025-05-05T11%3A43%3A33Z&se=2025-05-05T12%3A04%3A33Z&sr=c&sp=r&sig=R2pafkHQIgD7iboDRakYjIM14mbzF6PGd00Zx3e5lXg%3D)
Figure 5: Logging In
4.3 Creating a Group
Perform the following steps to create a group in the Fortanix DSM:
Click the Groups menu item in the DSM left navigation panel and click the + Groups button on the Groups page to add a new group.
Figure 6: Add Groups
On the Adding new group page, enter the following details:
Title: Enter a title for your group.
Description (optional): Enter a short description for the group.
Click the SAVE button to create the new group.
The new group has been added to the Fortanix DSM successfully.
4.4 Creating an Application
Perform the following steps to create an application (app) in the Fortanix DSM:
Click the Apps menu item in the DSM left navigation panel and click the + button on the Apps page to add a new app.
Figure 7: Add Application
On the Adding new app page, enter the following details:
App name: Enter the name of your application.
ADD DESCRIPTION (optional): Enter a short description for the application.
Authentication method: Select the default API Key as the method of authentication from the drop down menu. For more information on these authentication methods, refer to User's Guide: Authentication documentation.
Assigning the new app to groups: Select the group created in Section 4.3: Creating a Group from the list.
Click the SAVE button to add the new application.
The new application has been added to the Fortanix DSM successfully.
4.5 Copying the API Key
Perform the following steps to copy the API key from the Fortanix DSM:
Click the Apps menu item in the DSM left navigation panel and click the app created in Section 4.4: Creating an Application to go to the detailed view of the app.
In the app detailed view, in the INFO tab, click the VIEW API KEY DETAILS button.
From the API Key Details dialog box, copy the API Key of the app to use in Section 5.1.3: Creating Credentials (SYSadmin) when creating the credentials to generate the MEK on Fortanix DSM.
5.0 Configure TDE Using Fortanix DSM
5.1 Standalone Server
5.1.1 Enabling TDE Features
Run the following commands if Extensible Key Management (EKM) is not supported or enabled in the SQL Server Edition:
sp_configure 'show advanced', 1
GO
RECONFIGURE
GO
sp_configure 'EKM provider enabled', 1
GO
RECONFIGURE
GO

Figure 8: Run Commands for Error Scenario
5.1.2 Creating Cryptographic Provider
Run the following commands to use the correct location of the EKM DLL
:
CREATE CRYPTOGRAPHIC PROVIDER EKM_Prov
FROM FILE = 'C:\Program Files\Fortanix\KmsClient\FortanixKmsEkmProvider.dll' ;
GO
Where,
EKM_Prov
refers to the name of the provider defined by the user.

Figure 9: Create Cryptographic Provider
5.1.3 Creating Credentials (SYSadmin)
This section describes the steps to create the credentials to generate the MEK on the Fortanix DSM using the MS-SQL administrator.
To generate the key, the MS-SQL administrator requires permission to connect to Fortanix DSM.
Perform the following steps:
Run the following commands to create a credential using the API key copied in Section 4.5: Copying an API Key in your SQL Server Studio that will be used by the MS-SQL administrators:
CREATE CREDENTIAL sa_ekm_tde_cred WITH IDENTITY = 'Identity1', SECRET = '<DSM API KEY>' FOR CRYPTOGRAPHIC PROVIDER EKM_Prov ; GO
Figure 10: Create Credential
Add the credential to a high privileged user such as your own domain login in the format
[DOMAIN\login]
: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:
ALTER LOGIN "LOCALHOST\Administrator" ADD CREDENTIAL "sa_ekm_tde_cred"; GO
Figure 11: Command for No Domain
If you are not an administrator and hence unable to alter the login, open the Object Explorer by right-clicking Properties from Security → Logins → <login user> and map the credentials as shown in the following image:
Figure 12: Map Credentials
5.1.4 Creating Asymmetric Key (MEK)
The MS-SQL administrator has the credentials required to create the 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:
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
It is recommended to add versions to the Fortanix DSM keys for an easier key rotation process.
For example:
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

Figure 13: Create Asymmetric Key
5.1.5 Creating Credentials (DB Engine)
Run the following commands to create a credential that will be used by the database engine:
USE master ;
GO
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. Refer to the Section 5.1.3- Creating Credentials (sysadmin) to get the DSM API Key.
.png?sv=2022-11-02&spr=https&st=2025-05-05T11%3A43%3A33Z&se=2025-05-05T12%3A04%3A33Z&sr=c&sp=r&sig=R2pafkHQIgD7iboDRakYjIM14mbzF6PGd00Zx3e5lXg%3D)
Figure 14: Create Credential for DB Engine
5.1.6 Creating Login (DB Engine)
Run the following commands to create a login from an asymmetric key and map credentials to the login:
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.1.4- Creating Asymmetric Key (MEK).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 5.1.5- Creating Credentials (DB Engine).

Figure 15: Add New Credential to Login
5.2 Always On Group
Perform the following procedures on both the primary and secondary replicas.
5.2.1 Enabling TDE Features
Run the following commands if EKM is not supported or enabled in the SQL Server Edition:
sp_configure 'show advanced', 1
GO
RECONFIGURE
GO
sp_configure 'EKM provider enabled', 1
GO
RECONFIGURE
GO

Figure 16: Run Commands for Error Scenario
5.2.2 Creating Cryptographic Provider
Run the following commands to use the correct location of the EKM DLL
:
CREATE CRYPTOGRAPHIC PROVIDER EKM_Prov
FROM FILE = 'C:\Program Files\Fortanix\KmsClient\FortanixKmsEkmProvider.dll' ;
GO
Where,
EKM_Prov
refers to the name of the provider defined by the user.

Figure 17: Create Cryptographic Provider
5.2.3 Creating Credentials (sysadmin)
This section describes the steps to create the credentials to generate the MEK on the Fortanix DSM using the MS-SQL administrator.
To generate the key, the MS-SQL administrator requires permission to connect to Fortanix DSM.
Perform the following steps:
Perform the steps mentioned in Section 4.0: Configure Fortanix DSM to create an app and fetch the API key.
Paste the API key of your application as the value for the
SECRET
parameter in the next command.Figure 18: Copy API Key
Run the following commands to create a credential using the copied API key in your SQL Server Studio that will be used by the MS-SQL administrators:
CREATE CREDENTIAL sa_ekm_tde_cred WITH IDENTITY = 'Identity1', SECRET = '<DSM_API_KEY>' FOR CRYPTOGRAPHIC PROVIDER EKM_Prov ; GO
Figure 19: Create Credential
Add the credential to a high privileged user such as your own domain login in the format
[DOMAIN\login]
: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:
ALTER LOGIN LOCALHOST\Administrator ADD CREDENTIAL "sa_ekm_tde_cred"; GO
Figure 20: Command for No Domain
If you are not an administrator and hence unable to alter the login, open the Object Explorer by right-clicking Properties from Security → Logins → <login user> and map the credentials as shown in the following image:
Figure 21: Map Credentials
5.2.4 Creating Asymmetric Key (MEK)
The MS-SQL administrator has the credentials associated with creating the MEK on the Fortanix DSM. This section describes the steps to create the asymmetric key.
On Primary Replica
Run the following commands to create an asymmetric key stored inside the EKM provider:
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
It is recommended to add versions to the Fortanix DSM keys for easier key rotation process.
For example:
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

Figure 22: Create Asymmetric Key
On Secondary Replica
Run the following commands to create an asymmetric key on all the secondary replicas:
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.
5.2.5 Creating Credentials (DB Engine)
Run the following commands to create a credential that will be used by the database engine:
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.
.png?sv=2022-11-02&spr=https&st=2025-05-05T11%3A43%3A33Z&se=2025-05-05T12%3A04%3A33Z&sr=c&sp=r&sig=R2pafkHQIgD7iboDRakYjIM14mbzF6PGd00Zx3e5lXg%3D)
Figure 23: Create Credential for Database Engine
5.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:
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.2.4- Creating Asymmetric Key (MEK).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 5.2.5- Creating Credential (DB Engine).
.png?sv=2022-11-02&spr=https&st=2025-05-05T11%3A43%3A33Z&se=2025-05-05T12%3A04%3A33Z&sr=c&sp=r&sig=R2pafkHQIgD7iboDRakYjIM14mbzF6PGd00Zx3e5lXg%3D)
Figure 24: Add New Credential to Login
6.0 Migrate TDE from Local Environment to Fortanix DSM
This method will migrate the master key from the local environment using a certificate to the master key created on Fortanix DSM without decrypting and re-encrypting the entire database. It is recommended due to the minimal downtime required.
Perform the following steps to migrate the TDE:
Ensure to back up the database and transaction logs before changing the TDE configuration.
Run the following command to re-encrypt the existing database encryption key with the master key created on Fortanix DSM:
USE AdventureWorks2012; ALTER DATABASE ENCRYPTION KEY ENCRYPTION BY SERVER ASYMMETRIC KEY ekm_login_key
Where,
ekm_login_key
refers to the master key alias on the MSSQL database.

Figure 25: SQL Query
7.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. The ‘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:
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 handy to manage TDE.