1.0 Introduction
This article describes the step-by-step procedure to integrate Microsoft SQL with Transparent Data Encryption on an Always On availability group (AG).
These procedures are tested for an availability group consisting of two servers. Server 1 holds a (nominal) primary replica; Server 2 holds a (nominal) secondary replica. Both the replicas can read and write. The configuration uses Fortanix HSMs and no shared disk. Each server can be accessed directly or through a cluster availability group’s (virtual) address.
NOTE
The MSSQL Server Studio Add Database Wizard (versions for SQL Server 2019) does not support the addition of a database that is already encrypted or that includes a Data Encryption Key (DEK), even if encryption is disabled. However, you may enable TDE encryption for an existing, non-encrypted database that is already within an availability group using T-SQL, as described in the steps below.
When an availability group fails over, SQL Server (versions of SQL Server 2019) might not support a readable secondary that uses a clustered column store index.
The following steps should be performed for each database, both primary and secondary, that is part of the availability group and for which you intend to enable TDE encryption.
Before starting, ensure that the database you wish to encrypt:
Already exists.
Is part of an availability group within a cluster.
Is not currently encrypted and does not contain Data Encryption Key (TDE DEK).
Has never been encrypted before. If it has, you may see errors and a request for a log backup.
NOTE
In the examples shown here, the database to be encrypted is called SourceDatabase, and the database wrapping key is called SQL_Server_Key in the Fortanix-Data-Security-Manager (DSM) and ekm_login_key in the master database. Change names or other parameters to meet your own requirements. Also, these steps assume that a wrapping key of the same name does not already exist in the Fortanix DSM or the master database.
Before proceeding with the integration steps, ensure the following:
The database has been recently backed up.
The primary and secondary replicas are synchronized within the availability group, and failover can occur without any data loss.
If you prefer a particular server for the primary role, then you are switched over to that server.
You must remember the roles (primary or secondary) with which each server role begins.
Perform the following steps in the order shown. The following description assumes that the server nodes retain their initial primary or secondary roles. You can use the availability group cluster virtual address and manually failover between the nodes to access them, but bear in mind that this description refers to the initial (starting) role of each node, even if its actual role changes later.
Before you proceed, ensure that the primary and secondary databases are in a synchronized state as shown below.
.png?sv=2022-11-02&spr=https&st=2025-05-28T23%3A40%3A01Z&se=2025-05-28T23%3A57%3A01Z&sr=c&sp=r&sig=UGvGLDqTbZh83Fw%2Ff5UvgE7qk8JDthxpTSQNluQRvq8%3D)
Figure 1: SQL server management studio
NOTE
Ensure that you have performed the steps from the Data Security Manager with Microsoft SQL TDE Integration – Before You Begin.
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:
sp_configure 'show advanced', 1
GO
RECONFIGURE
GO
sp_configure 'EKM provider enabled', 1
GO
RECONFIGURE
GO

Figure 2: Run commands for error scenario
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 3: 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.
Run the following commands to create a credential in your SQL Server Studio that will be used by the system administrators:
CREATE CREDENTIAL sa_ekm_tde_cred WITH IDENTITY = 'Identity1', SECRET = '<DSM_API_KEY>' FOR CRYPTOGRAPHIC PROVIDER EKM_Prov ; GO
Where,
<DSM_API_KEY>
refers to the Fortanix DSM API key as copied in Fortanix Data Security Manager with Microsoft SQL Server TDE Integration - Before You Begin.
Figure 4: Create a credential
Add the credential to a highly 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 5: 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 6: 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 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 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,
CREATION_DISPOSITION=CREATE_NEW,
PROVIDER_KEY_NAME = 'SQL_Server_Key_v1';
GO

Figure 7: Create an asymmetric key
2.4.2 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.
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 as copied in Fortanix Data Security Manager with Microsoft SQL Server TDE Integration - Before You Begin.
.png?sv=2022-11-02&spr=https&st=2025-05-28T23%3A40%3A01Z&se=2025-05-28T23%3A57%3A01Z&sr=c&sp=r&sig=UGvGLDqTbZh83Fw%2Ff5UvgE7qk8JDthxpTSQNluQRvq8%3D)
Figure 8: 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:
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.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.5- Creating Credential (DB Engine).

Figure 9: Add new credential to login
3.0 Enabling Encryption
Perform the following steps only on the primary replica and monitor the status using the monitoring command on the primary or secondary replica.
3.1 Creating Data Encryption Key (DEK)
Run the following commands to create the Data Encryption Key (DEK) that will be used for TDE:
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
Run the following commands to alter the database to enable Transparent Data Encryption (TDE):
ALTER DATABASE employee
SET ENCRYPTION ON ;
GO

Figure 10: Enable TDE
3.3 Monitoring TDE Progress
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:
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.