How to Configure Microsoft SQL Server Transparent Data Encryption (TDE) With Azure Key Vault EKM Providers

Protecting data at rest is a foundational requirement for any enterprise database architecture. If a malicious actor steals the physical hard drives from a server chassis, or exfiltrates the raw .mdf or .bak files from a compromised backup server, they can mount those databases elsewhere and read the raw data. Microsoft SQL Server Transparent Data Encryption (TDE) mitigates this by encrypting the storage pages in real-time before they are written to disk. However, if the Database Encryption Key (DEK) is protected by a certificate stored locally on the same server, a complete system compromise nullifies the encryption. To achieve true separation of duties and cryptographic resilience, Database Administrators (DBAs) must configure SQL Server Extensible Key Management (EKM) to store the master keys securely off-site in Azure Key Vault.

The Architecture of Extensible Key Management

When TDE is enabled, SQL Server uses a Database Encryption Key (DEK) to encrypt the data pages. This DEK must also be encrypted to protect it. Traditionally, this is done using a certificate stored in the SQL Server master database.

By introducing the EKM provider for Azure Key Vault, the architecture shifts. The DEK is still stored within the database, but it is encrypted using an Asymmetric Key (RSA) that resides entirely within the Azure Key Vault cloud service. The private key never leaves the Azure Hardware Security Module (HSM). When SQL Server boots and needs to decrypt the database, it must authenticate to Azure via Entra ID (formerly Azure AD), send the encrypted DEK to the Key Vault, and receive the decrypted DEK in return. If the local SQL Server is stolen, the attacker cannot start the database because they do not have the credentials to access the Azure Key Vault.

Provisioning the Azure Key Vault

Before configuring SQL Server, you must provision the cloud infrastructure.

1. Navigate to the Azure Portal and create a new Key Vault. Ensure you enable Purge Protection and Soft Delete to prevent accidental or malicious destruction of your master keys.
2. Create a new App Registration in Microsoft Entra ID. This generates the Client ID and Client Secret that SQL Server will use to authenticate to the vault.
3. In the Key Vault’s Access Policies (or RBAC settings), grant the newly created App Registration the specific permissions to Get, Wrap Key, and Unwrap Key.
4. Within the Key Vault, generate a new Asymmetric Key (RSA 2048 or 4096). Note the Key URI (e.g., https://myvault.vault.azure.net/keys/SQLMasterKey).

Installing and Configuring the EKM Provider

On your on-premises Windows Server hosting SQL Server, you must download and install the SQL Server Connector for Microsoft Azure Key Vault. This DLL acts as the translation layer between the SQL engine and the Azure REST API.

Once installed, open SQL Server Management Studio (SSMS) and execute the following Transact-SQL (T-SQL) commands to register the provider.

-- 1. Enable advanced options and EKM
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'EKM provider enabled', 1;
RECONFIGURE;

-- 2. Create the Cryptographic Provider mapping to the DLL
CREATE CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM
FROM FILE = 'C:\Program Files\SQL Server Connector for Microsoft Azure Key Vault\Microsoft.AzureKeyVaultService.EKM.dll';

Creating Credentials and Asymmetric Keys

Next, you must provide SQL Server with the Entra ID credentials (Client ID and Secret) required to communicate with the vault.

-- 3. Create a credential using the Entra ID Client ID and Secret
-- Note: The secret must be concatenated without hyphens
CREATE CREDENTIAL [https://myvault.vault.azure.net]
WITH IDENTITY = 'Your-Client-ID',
SECRET = 'Your-Client-Secret';

-- 4. Map the SQL Server login to the credential
ALTER LOGIN [sa] ADD CREDENTIAL [https://myvault.vault.azure.net];

Now, you pull a reference to the RSA key stored in Azure into SQL Server. The private key material does not download; this merely creates a pointer.

-- 5. Create the Asymmetric Key in the master database pointing to the Azure Vault
USE master;
CREATE ASYMMETRIC KEY TDE_Master_Key
FROM PROVIDER AzureKeyVault_EKM
WITH PROVIDER_KEY_NAME = 'SQLMasterKey',
CREATION_DISPOSITION = OPEN_EXISTING;

-- 6. Create a SQL Login mapped to the Asymmetric Key
CREATE LOGIN TDE_Login FROM ASYMMETRIC KEY TDE_Master_Key;
ALTER LOGIN TDE_Login ADD CREDENTIAL [https://myvault.vault.azure.net];

Encrypting the Database

With the infrastructure established, you can now encrypt the target database. The DEK will be generated locally but encrypted by the Azure Key Vault before being stored.

-- 7. Switch to the target database and create the DEK
USE [FinancialData];
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER ASYMMETRIC KEY TDE_Master_Key;

-- 8. Enable TDE
ALTER DATABASE [FinancialData] SET ENCRYPTION ON;

SQL Server will initiate a background thread to encrypt the storage pages. By relying on Azure Key Vault EKM, your database is not only protected by military-grade AES-256 encryption, but the keys required to unlock it are physically segregated in a heavily audited cloud HSM, neutralizing the threat of physical server theft or offline backup extraction.