SQL Server Transparent Data Encryption: Local Certificate and Azure Key Vault

A production SQL Server running for three years with no encryption at rest. The compliance team flags it. The ask is to enable TDE before the audit in two weeks. The team lead asks: is this just an ALTER DATABASE statement?

It is not. TDE involves a layered key hierarchy, mandatory certificate backups that must happen before encryption is enabled, backup compatibility changes that affect every future restore, and — if you choose Azure Key Vault — a dependency on an external service that, if it becomes unreachable, takes your databases offline. Each of these consequences is silent until it is too late.

The answer

Transparent Data Encryption encrypts database files at rest: data files, log files, and backups. The encryption is transparent to the application — queries and connections do not change. The protection is physical: if someone copies the MDF or takes a backup file, they cannot read it without the certificate or key that protected the DEK.

There are two approaches to protecting the Database Encryption Key (DEK):

• Local certificate: The certificate lives in the master database, protected by the SQL Server Service Master Key. Simple to implement, no external dependencies, but key management is entirely your responsibility.
• Azure Key Vault via EKM: The protecting key lives in Azure Key Vault. Centralised, auditable, HSM-backed. Requires the SQL Server Connector, a managed identity or service principal, and a reachable Key Vault — if any of these are unavailable, the databases will not open.

Both approaches are covered here in full.

Scope: SQL Server 2016–2022, on Windows. TDE is available in Enterprise, Developer, and — from SQL Server 2019 CU6 — Standard edition. Azure SQL Database and Azure SQL Managed Instance manage TDE differently and are not covered here. Azure Key Vault integration via EKM applies to SQL Server on-premises and SQL Server on Azure VMs. Validate in a non-production environment before applying to production. Backup and restore behaviour changes permanently once TDE is enabled.

TDE key hierarchy

Understanding the hierarchy is not optional. Every risk in this article flows from it.

Service Master Key (SMK)
  └── Database Master Key in master (DMK)
        └── Certificate in master
              └── Database Encryption Key (DEK) in each encrypted database

The SMK is created automatically by SQL Server and is tied to the instance. The DMK in master is encrypted by the SMK. The certificate is encrypted by the DMK. The DEK in each user database is encrypted by that certificate.

If you lose the certificate and its private key backup, you lose access to every database encrypted with it, including every backup taken while that certificate was in use. There is no recovery path.

For Azure Key Vault, the DEK is encrypted by an asymmetric key stored in the vault rather than a local certificate. The dependency shifts from your file system to the availability and access policy of the Key Vault.

Part 1 — TDE with a local certificate

Prerequisites

• sysadmin or CONTROL SERVER on the SQL Server instance.
• A secure, tested backup destination for the certificate and private key — separate from the SQL Server host.
• A documented certificate password stored in a password manager or secrets vault. Do not store it only in email or a text file on the server.
• A maintenance window. The initial encryption scan runs in the background, but it generates I/O. Schedule accordingly on busy instances.

Step 1 — Create the Database Master Key in master

If a DMK already exists in master, skip this step. Check first:

-- Run in the master database context
USE master;
GO

SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##';

If no row is returned, create one:

USE master;
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong_password_here>';
GO

Choose a strong password. The DMK password is a recovery mechanism — store it in your secrets management system now.

Step 2 — Create the certificate

USE master;
GO

CREATE CERTIFICATE TDECert2026
    WITH SUBJECT = 'TDE Certificate for <InstanceName>',
    EXPIRY_DATE = '2036-05-14';
GO

The EXPIRY_DATE is advisory for certificates used for TDE — SQL Server does not enforce it for encryption purposes — but set a meaningful value so it appears in audits and monitoring queries.

Step 3 — Back up the certificate immediately

This step is not optional. Do not proceed to Step 4 until the backup file is verified and stored off-server.

If this certificate is lost, every database encrypted with it becomes permanently unreadable — including all backups made after TDE was enabled.

USE master;
GO

BACKUP CERTIFICATE TDECert2026
    TO FILE = 'C:\CertBackups\TDECert2026.cer'
    WITH PRIVATE KEY (
        FILE = 'C:\CertBackups\TDECert2026.pvk',
        ENCRYPTION BY PASSWORD = '<private_key_backup_password>'
    );
GO

Immediately copy both files (TDECert2026.cer and TDECert2026.pvk) off the SQL Server host. A secure file share, an encrypted vault, or offline storage are all valid destinations. Do not leave the only copy on the SQL Server host.

Verify the backup files were written:

-- In PowerShell or a file manager
dir C:\CertBackups\

Confirm both files exist and are non-zero in size.

Step 4 — Create the Database Encryption Key

Switch to the user database you are encrypting:

USE [YourDatabase];
GO

CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE TDECert2026;
GO

AES_256 is the current standard. Do not use AES_128, RC4, or TRIPLE_DES_3KEY — these are weaker or deprecated.

SQL Server will warn that the certificate has no private key backup if you skipped Step 3. Do not dismiss this warning. Go back and complete Step 3.

Step 5 — Enable encryption

USE master;
GO

ALTER DATABASE [YourDatabase] SET ENCRYPTION ON;
GO

This starts the background encryption scan. The database remains online throughout. The scan reads every allocated page and writes it back encrypted.

Step 6 — Monitor encryption progress

SELECT
    db.name                             AS database_name,
    dek.encryption_state,
    CASE dek.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'
    END                                 AS encryption_state_desc,
    CONVERT(DECIMAL(5, 2),
        dek.percent_complete)           AS percent_complete,
    dek.encryptor_thumbprint,
    dek.key_algorithm,
    dek.key_length
FROM sys.dm_database_encryption_keys dek
JOIN sys.databases db ON dek.database_id = db.database_id
ORDER BY db.name;

Wait until encryption_state = 3 (Encrypted) before treating the task as complete.

Step 7 — Verify tempdb

When any user database is encrypted, SQL Server automatically encrypts tempdb. This is expected and cannot be prevented. Verify it:

SELECT
    db.name,
    dek.encryption_state,
    CASE dek.encryption_state
        WHEN 3 THEN 'Encrypted'
        ELSE 'Other'
    END AS state_desc
FROM sys.dm_database_encryption_keys dek
JOIN sys.databases db ON dek.database_id = db.database_id
WHERE db.name = 'tempdb';

tempdb will show as encrypted as long as at least one user database has TDE enabled.

Restoring a TDE-protected database to another instance

Any backup taken after ALTER DATABASE SET ENCRYPTION ON is encrypted. To restore it on another SQL Server instance, that instance must have the certificate installed first:

-- On the target instance:
USE master;
GO

-- Ensure DMK exists on target
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<target_dmk_password>';
GO

-- Restore the certificate from backup
CREATE CERTIFICATE TDECert2026
    FROM FILE = 'C:\CertBackups\TDECert2026.cer'
    WITH PRIVATE KEY (
        FILE = 'C:\CertBackups\TDECert2026.pvk',
        DECRYPTION BY PASSWORD = '<private_key_backup_password>'
    );
GO

Only then will the restore succeed. Document this dependency explicitly in your DR runbooks.

Part 2 — TDE with Azure Key Vault (EKM)

Azure Key Vault integration uses SQL Server’s Extensible Key Management (EKM) framework. The protecting key lives in Key Vault rather than in the SQL Server instance. This shifts key management responsibility to Azure but introduces a hard availability dependency.

Prerequisites

• An Azure subscription with permissions to create a Key Vault, a managed identity or service principal, and key operations.
• SQL Server 2016 or later on-premises or on an Azure VM. Azure SQL Database and Managed Instance use TDE with Key Vault differently — this procedure does not apply to them.
• The SQL Server Connector for Microsoft Azure Key Vault installed on the SQL Server host. This is a separate download from Microsoft.
• sysadmin on the SQL Server instance.
• Network path from the SQL Server host to https://<your-vault>.vault.azure.net. If the instance is on-premises, this requires outbound HTTPS. If the instance is on an Azure VM, use a Private Endpoint to avoid public internet exposure.

Step 1 — Create and configure the Azure Key Vault

Using the Azure CLI:

# Create a Resource Group if you do not have one
az group create --name rg-sqlsecurity --location westeurope

# Create the Key Vault with soft-delete and purge protection enabled
az keyvault create \
    --name kv-sql-tde-prod \
    --resource-group rg-sqlsecurity \
    --location westeurope \
    --enable-soft-delete true \
    --enable-purge-protection true \
    --sku premium

--enable-purge-protection true prevents keys from being permanently deleted for a retention period even after explicit deletion. Enable this for production vaults — it is a safeguard against accidental or malicious key destruction.

--sku premium enables HSM-backed keys. Use standard only if HSM is not required by your compliance framework.

Step 2 — Create a managed identity or service principal

Option A — Managed identity (recommended for Azure VMs):

Assign a system-assigned managed identity to the Azure VM running SQL Server via the Azure portal or CLI, then grant it Key Vault access:

# Get the VM's identity object ID
VM_IDENTITY=$(az vm identity show \
    --name <YourVMName> \
    --resource-group <YourVMResourceGroup> \
    --query principalId --output tsv)

# Grant Key Vault access (using RBAC)
az role assignment create \
    --role "Key Vault Crypto Service Encryption User" \
    --assignee $VM_IDENTITY \
    --scope $(az keyvault show --name kv-sql-tde-prod \
        --resource-group rg-sqlsecurity --query id --output tsv)

Option B — Service principal (for on-premises SQL Server):

# Create service principal
az ad sp create-for-rbac \
    --name sp-sql-tde \
    --role "Key Vault Crypto Service Encryption User" \
    --scopes $(az keyvault show --name kv-sql-tde-prod \
        --resource-group rg-sqlsecurity --query id --output tsv)

Record the appId and password from the output. Store the password in your secrets management system immediately — Azure does not display it again.

Step 3 — Create the asymmetric key in Key Vault

az keyvault key create \
    --vault-name kv-sql-tde-prod \
    --name SQLTDEKey2026 \
    --kty RSA-HSM \
    --size 2048 \
    --ops wrapKey unwrapKey

RSA-HSM creates an HSM-backed key. RSA creates a software-protected key. Use RSA-HSM for production where your compliance framework requires it.

Note the key identifier URI — you will reference it in SQL Server.

Step 4 — Install and configure the SQL Server Connector

After downloading the SQL Server Connector MSI from Microsoft:

1. Run the installer on the SQL Server host.
2. The connector installs as an EKM provider DLL at a path similar to C:\Program Files\SQL Server Connector for Microsoft Azure Key Vault\Microsoft.AzureKeyVaultService.EKM.dll.

In SQL Server, enable EKM and register the provider:

USE master;
GO

-- Enable EKM support (instance-level configuration)
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO

sp_configure 'EKM provider enabled', 1;
RECONFIGURE;
GO

-- Register the connector as an EKM provider
CREATE CRYPTOGRAPHIC PROVIDER AzureKeyVaultProvider
    FROM FILE = 'C:\Program Files\SQL Server Connector for Microsoft Azure Key Vault\Microsoft.AzureKeyVaultService.EKM.dll';
GO

Step 5 — Create the SQL Server credential mapped to the Key Vault identity

For a service principal (Option B from Step 2):

USE master;
GO

CREATE CREDENTIAL AzureKeyVaultCredential
    WITH IDENTITY = '<AppID of the service principal>',
    SECRET = '<ClientSecret of the service principal>'
    FOR CRYPTOGRAPHIC PROVIDER AzureKeyVaultProvider;
GO

For a managed identity (Option A):

USE master;
GO

CREATE CREDENTIAL AzureKeyVaultCredential
    WITH IDENTITY = 'managed identity'
    FOR CRYPTOGRAPHIC PROVIDER AzureKeyVaultProvider;
GO

Map the credential to the SQL Server service account login:

ALTER LOGIN [DOMAIN\SQLServiceAccount]
    ADD CREDENTIAL AzureKeyVaultCredential;
GO

Step 6 — Create the asymmetric key reference in SQL Server

USE master;
GO

CREATE ASYMMETRIC KEY TDEKeyFromAKV
    FROM PROVIDER AzureKeyVaultProvider
    WITH
        PROVIDER_KEY_NAME = 'SQLTDEKey2026',
        CREATION_DISPOSITION = OPEN_EXISTING;
GO

This does not copy the key into SQL Server. It creates a reference to the key in Key Vault. All wrap and unwrap operations happen in the vault.

Step 7 — Create a login for EKM key use

USE master;
GO

CREATE LOGIN TDEKeyLogin
    FROM ASYMMETRIC KEY TDEKeyFromAKV;
GO

ALTER LOGIN TDEKeyLogin
    ADD CREDENTIAL AzureKeyVaultCredential;
GO

Step 8 — Create the Database Encryption Key

USE [YourDatabase];
GO

CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER ASYMMETRIC KEY TDEKeyFromAKV;
GO

Step 9 — Enable encryption and monitor

USE master;
GO

ALTER DATABASE [YourDatabase] SET ENCRYPTION ON;
GO

Monitor exactly as in the local certificate procedure:

SELECT
    db.name,
    dek.encryption_state,
    CONVERT(DECIMAL(5, 2), dek.percent_complete) AS percent_complete,
    dek.encryptor_thumbprint
FROM sys.dm_database_encryption_keys dek
JOIN sys.databases db ON dek.database_id = db.database_id
ORDER BY db.name;

Disabling TDE and decrypting a database

If you need to remove TDE:

-- Disable encryption on the database
ALTER DATABASE [YourDatabase] SET ENCRYPTION OFF;
GO

SQL Server runs a background decryption scan. Monitor encryption_state until it returns to 1 (Unencrypted). Once decryption completes, remove the DEK:

USE [YourDatabase];
GO

DROP DATABASE ENCRYPTION KEY;
GO

Important: Disabling TDE does not re-encrypt existing backup files. Backups taken while TDE was enabled remain encrypted and still require the certificate or key. Keep the certificate or Key Vault key accessible for as long as any encrypted backup must be restorable.

Risks and dangers

Certificate loss is permanent and total

This is the most common and most severe failure. If the certificate in master is lost — through instance failure, disk corruption, accidental drop, or a migration that did not include a certificate transfer — every database encrypted with it becomes permanently unreadable. Every backup taken after TDE was enabled is also permanently unreadable. There is no Microsoft recovery path. There is no support escalation that resolves it.

Mitigations:

• Back up the certificate and private key file before enabling TDE.
• Store the backup in at least two separate, durable locations — not on the SQL Server host.
• Store the private key password in a secrets vault. Losing the password to decrypt the private key backup has the same consequence as losing the file.
• Test the restore of the certificate to a second instance before enabling TDE on production.
• Include the certificate in your standard backup and DR documentation.

Backup files are permanently tied to the certificate

Any full, differential, or log backup taken after ALTER DATABASE SET ENCRYPTION ON is encrypted. This is unconditional. You cannot take an unencrypted backup of a TDE-protected database.

Consequences:

• Restoring to a different instance — for DR, migration, or cloning a test environment — requires the certificate on the target instance first.
• If your DBA team rotates out and the new team does not have the certificate documentation, the next restore test will fail.
• Native backup compression interacts with encryption: compressed encrypted backups are larger than compressed unencrypted backups, because encrypted data does not compress well.

Azure Key Vault availability dependency

With AKV-backed TDE, SQL Server must contact Key Vault to unwrap the DEK every time a database is opened — including on service restart, failover, or availability group role change.

If Key Vault is unreachable when SQL Server attempts to open the database, the database will not open. This can happen due to:

• Key Vault outage or regional Azure incident.
• Expired service principal secret or certificate.
• Misconfigured network path (firewall rules, private endpoint changes, NSG updates).
• Key Vault access policy changes that remove the SQL Server identity’s permissions.
• The key being disabled, deleted, or purged in the vault.

For Availability Groups, both the primary and all secondary replicas must be able to reach the vault independently. A secondary that cannot reach Key Vault will not be able to take over as primary during a failover.

Mitigations:

• Use Private Endpoints for Key Vault access from Azure VMs.
• Enable soft-delete and purge protection on the vault.
• Monitor Key Vault availability and access separately from SQL Server.
• Test planned failover with Key Vault accessible and then simulate a vault unavailability scenario to understand the failure mode before it happens in production.
• For on-premises instances, consider whether the outbound network path to Key Vault is within or outside your SLA boundary.

Service principal secret expiry

Service principal secrets have an expiry date. When the secret expires, SQL Server can no longer authenticate to Key Vault. Any database currently open will remain open (the DEK is cached in memory) until SQL Server is restarted. After restart, the database will not open.

If you use a service principal rather than a managed identity, implement monitoring for upcoming secret expiry and a documented rotation procedure that includes updating the SQL Server credential.

Using a managed identity eliminates this risk for Azure VM deployments.

Performance impact

TDE adds CPU overhead for encryption and decryption on every I/O. On modern hardware this is typically 3–10% CPU overhead for I/O-intensive workloads. It is measurable. Benchmark before enabling on a heavily loaded instance.

The initial encryption scan is an I/O-intensive operation. On large databases, it can take hours. It runs at low priority but competes with normal workload I/O. Run it during a maintenance window on busy instances.

tempdb is always encrypted when any database is encrypted

When any user database has TDE enabled, SQL Server automatically encrypts tempdb. This affects all databases on the instance that use tempdb — including those that are not themselves encrypted. The performance overhead of tempdb encryption applies to the whole instance, not just the encrypted database.

Certificate rotation

Rotating the TDE certificate requires:

1. Creating a new certificate.
2. Backing up the new certificate before use.
3. Altering the DEK to use the new certificate (ALTER DATABASE ENCRYPTION KEY REGENERATE WITH ALGORITHM).
4. Retaining the old certificate until all backups encrypted with it are beyond your retention window.

Certificate rotation while an older backup set exists means you must maintain both certificates as long as those backups need to be restorable. Plan your rotation and backup retention policy together.

Verification queries

Current encryption state for all databases

SELECT
    db.name                             AS database_name,
    db.is_encrypted,
    dek.encryption_state,
    CASE dek.encryption_state
        WHEN 0 THEN 'No DEK'
        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'
    END                                 AS encryption_state_desc,
    CONVERT(DECIMAL(5, 2),
        dek.percent_complete)           AS percent_complete,
    dek.key_algorithm,
    dek.key_length,
    dek.encryptor_type,
    cert.name                           AS protecting_certificate,
    cert.expiry_date                    AS certificate_expiry
FROM sys.databases db
LEFT JOIN sys.dm_database_encryption_keys dek
    ON db.database_id = dek.database_id
LEFT JOIN master.sys.certificates cert
    ON dek.encryptor_thumbprint = cert.thumbprint
ORDER BY db.name;

Certificates present in master

USE master;
GO

SELECT
    name,
    certificate_id,
    pvt_key_encryption_type_desc,
    is_active_for_begin_dialog,
    issuer_name,
    subject,
    start_date,
    expiry_date,
    thumbprint
FROM sys.certificates
ORDER BY name;

Check whether the DMK in master is backed up

SQL Server tracks whether the DMK is backed up to a file:

USE master;
GO

SELECT
    name,
    is_master_key_encrypted_by_server,
    create_date,
    modify_date
FROM sys.databases
WHERE name = 'master';

Note: SQL Server does not track certificate backup state the same way. Maintaining certificate backup documentation is a manual operational responsibility.

Rollback considerations

Operational recommendations

• Document the certificate inventory. Record which certificate protects which databases, where the backup files are stored, where the backup password is stored, and when the certificate was created. This documentation must be accessible during a DR event — store it somewhere other than the encrypted SQL Server instance.
• Test certificate restores. Before enabling TDE on any production database, complete a full test: enable TDE on a non-production copy, take a backup, restore it on a separate instance using only the certificate backup files. Verify the restore succeeds. Most certificate restore failures are discovered for the first time during an actual DR event.
• For AKV, use managed identities where possible. Service principal secrets expire. Managed identities for Azure VM workloads eliminate that failure mode entirely.
• Monitor Key Vault availability separately. Key Vault health should be in your monitoring stack alongside SQL Server. An AKV issue that goes undetected will prevent any SQL Server restart or failover on affected instances.
• Align certificate rotation with backup retention. Rotating the TDE certificate before older backups fall outside retention means you need both the old and new certificates accessible for restores. Plan explicitly for the overlap period.
• Include TDE in DR runbooks. Every DR procedure for an encrypted instance must include the steps to present the certificate on the target before attempting the restore. A restore runbook that omits this step will fail at exactly the worst moment.

References

• Transparent Data Encryption — Microsoft Learn
• TDE with Azure Key Vault using EKM — Microsoft Learn
• SQL Server Connector for Microsoft Azure Key Vault
• Move a TDE Protected Database to Another SQL Server — Microsoft Learn
• sys.dm_database_encryption_keys — Microsoft Learn