This chapter covers Transparent Data Encryption (TDE) with customer-managed keys (CMK) stored in Azure Key Vault, a critical compute security control for protecting data at rest in Azure SQL databases. For the AZ-500 exam, this topic appears in approximately 5-8% of questions, primarily under objective 2.3 (Implement data encryption). You must understand how TDE with CMK differs from service-managed keys, the exact key hierarchy (DEK and KEK), the permissions required in Key Vault, and the recovery scenarios including key rotation and revocation.
Jump to a section
Imagine a bank safety deposit box (the database) that is already locked with a master key (the TDE protector). The bank normally holds the master key for you. But you want to control access yourself, so you bring your own padlock (the customer-managed key in Key Vault). First, you give the bank a copy of your padlock's key (the AKV key URI) — the bank stores it securely, but only you have the physical key. When you need to open the box, the bank uses your padlock key to unlock your padlock, then uses the master key to open the box. If you revoke access to your padlock key, the bank cannot unlock your padlock, and the master key becomes useless — the box stays locked. This mirrors TDE with CMK: the DEK (master key) is encrypted by the KEK (your CMK). The database engine can only decrypt the DEK if it has access to the CMK via Key Vault. If you disable the CMK or revoke access, the database becomes inaccessible until access is restored.
What is TDE with Customer-Managed Keys?
Transparent Data Encryption (TDE) is a feature of Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics that encrypts data at rest automatically. By default, Azure uses a service-managed key (SMK) to protect the TDE protector. With customer-managed keys (CMK), you bring your own key stored in Azure Key Vault to protect the TDE protector. This gives you full control over the encryption key lifecycle: rotation, revocation, and access auditing.
Key Hierarchy and Mechanism
TDE uses a two-tier key hierarchy: - Database Encryption Key (DEK): A symmetric key stored in the database boot record. It is encrypted by the TDE protector. - TDE Protector: An asymmetric key (or symmetric key) that encrypts the DEK. With CMK, this is a key stored in Azure Key Vault (either an RSA key or a symmetric key).
The encryption flow: 1. When a database page is written to disk, the database engine encrypts it using the DEK. 2. The DEK itself is encrypted by the TDE protector (the CMK). 3. Before a database can start, the SQL Server process must decrypt the DEK. It sends a request to Azure Key Vault to unwrap the DEK using the CMK. 4. The decrypted DEK is held in memory (not persisted) and used for subsequent encryption/decryption operations.
Key Vault Permissions Required
To use a CMK for TDE, the Azure SQL logical server (or managed instance) must have a managed identity (system-assigned or user-assigned) that is granted the following Key Vault access policies: - get: Allows the server to retrieve the key and its metadata. - wrapKey: Allows the server to encrypt the DEK with the CMK. - unwrapKey: Allows the server to decrypt the DEK with the CMK.
Without the unwrapKey permission, the database cannot start. Without wrapKey, automatic key rotation cannot re-encrypt the DEK.
Key Types and Sizes
Azure Key Vault supports RSA keys (2048, 3072, 4096 bits) and symmetric keys (AES 256-bit). For TDE with CMK, you can use either: - RSA key: The TDE protector is an asymmetric key. The DEK is encrypted with the RSA public key and decrypted with the private key. - Symmetric key: The TDE protector is a symmetric key. The DEK is encrypted and decrypted using the same key.
RSA 2048-bit is the most common choice. Symmetric keys are newer and offer better performance but require Key Vault Premium tier.
Key Rotation
Key rotation can be manual or automatic:
- Manual rotation: You create a new key version in Key Vault and then run ALTER DATABASE ... SET ENCRYPTION OFF/ON or use PowerShell/CLI to rotate. This causes a brief downtime.
- Automatic rotation: If you use the same key URI but enable soft delete and purge protection, Azure SQL automatically uses the latest key version when the DEK needs to be re-encrypted (e.g., after a failover). However, the DEK is not automatically re-encrypted with the new version until it is unwrapped again. To force re-encryption, you can run ALTER DATABASE ... SET ENCRYPTION ON (no downtime if already encrypted).
Monitoring and Auditing
You should monitor Key Vault audit logs for unwrap and wrap operations. If access is revoked, the database will become inaccessible. Use Azure Monitor alerts on Key Vault requests from the SQL server.
Geo-DR Considerations
For geo-replicated databases, each server must have access to the same CMK. You can use a key vault in the same region, or a global key vault with replication. If using failover groups, ensure the secondary server has the required permissions.
Configuration Steps
Create an Azure Key Vault (enable soft delete and purge protection).
Create or import a key (RSA or symmetric).
Assign a managed identity to the Azure SQL logical server.
Grant the managed identity get, wrapKey, and unwrapKey permissions in the Key Vault access policy.
Set the TDE protector to the CMK using Azure Portal, PowerShell, or CLI.
Example PowerShell:
# Set TDE protector to CMK
Set-AzSqlServerTransparentDataEncryptionProtector -ResourceGroupName "myRG" `
-ServerName "myServer" `
-Type AzureKeyVault `
-KeyId "https://myvault.vault.azure.net/keys/mykey/version"Example Azure CLI:
az sql server tde-key set --server myServer --resource-group myRG --server-key-type AzureKeyVault --kid "https://myvault.vault.azure.net/keys/mykey/version"Verification
To verify the TDE protector type:
Get-AzSqlServerTransparentDataEncryptionProtector -ResourceGroupName "myRG" -ServerName "myServer"The output shows Type as AzureKeyVault and KeyId.
Interaction with Other Technologies
Always Encrypted: Uses separate keys (column master keys) stored in Key Vault, independent of TDE.
Azure Defender for SQL: Can alert on suspicious access patterns, but does not affect TDE key management.
Azure Policy: Can enforce that SQL servers use CMK for TDE.
Common Pitfalls
Missing permissions: The managed identity must have unwrapKey. Without it, the database will fail to start after a restart or failover.
Key Vault deleted: If the key vault is deleted without soft delete, the database becomes permanently inaccessible. Always enable soft delete and purge protection.
Key disabled: If the key is disabled in Key Vault, the database cannot be accessed until the key is re-enabled.
Firewall blocking: The SQL server must be able to reach the Key Vault endpoint. If network restrictions block access (e.g., private endpoints), the database may become unavailable.
Performance Impact
TDE with CMK adds minimal overhead because the DEK is decrypted once per database startup and held in memory. The actual page-level encryption uses the DEK, which is the same as with service-managed keys. The Key Vault operation (unwrap) is a one-time cost per database restart or failover.
Create Azure Key Vault
Create a Key Vault in the same Azure region as your SQL server. Enable soft delete and purge protection to prevent permanent data loss. Soft delete retains deleted vaults/keys for 90 days; purge protection prevents deletion during that period. Without these, if the key is deleted, the database becomes inaccessible with no recovery option.
Create or import a key
Generate a new key (RSA 2048-bit recommended) or import an existing key (e.g., from an on-premises HSM). The key must be stored in Key Vault as a key (not a secret). For symmetric keys, use Key Vault Premium. The key URI will be used when configuring the TDE protector.
Assign managed identity to SQL server
The Azure SQL logical server must have a system-assigned or user-assigned managed identity. This identity is used to authenticate to Key Vault. For system-assigned, it is created automatically when you enable it. For user-assigned, you create the identity separately and assign it to the server.
Grant Key Vault permissions
In the Key Vault access policy, add the SQL server's managed identity as a principal. Grant the permissions: get (to read the key), wrapKey (to encrypt the DEK), and unwrapKey (to decrypt the DEK). Without wrapKey, automatic re-encryption fails; without unwrapKey, the database cannot start.
Set TDE protector to CMK
Use Azure Portal, PowerShell, or CLI to set the TDE protector to the customer-managed key. Specify the key URI (including version) or use a key without version for automatic rotation. This operation re-encrypts the DEK with the new protector. The database remains online during this operation.
Verify and monitor
Verify the TDE protector type using Get-AzSqlServerTransparentDataEncryptionProtector. Monitor Key Vault audit logs for unwrap operations. Set up alerts for failed access attempts. Test failover to ensure the secondary server can access the key. Document the key URI and permissions for disaster recovery.
Enterprise Scenario 1: Financial Services Compliance
A bank must comply with PCI DSS and GDPR, which require that encryption keys be managed by the customer, not the cloud provider. The bank uses TDE with CMK for all Azure SQL databases containing cardholder data. They store the keys in a dedicated Key Vault with strict access policies and enable soft delete and purge protection. They rotate keys every 90 days using a manual process: a new key version is created in Key Vault, and the TDE protector is updated using PowerShell. They also use Azure Policy to enforce that all new SQL servers use CMK. A common issue is that when the key is rotated, the DEK is not automatically re-encrypted until a failover or restart. To force re-encryption, they run ALTER DATABASE ... SET ENCRYPTION ON which is an online operation.
Enterprise Scenario 2: Multi-Region Disaster Recovery A global e-commerce company uses Azure SQL Database with active geo-replication. They configure TDE with CMK using the same key in both primary and secondary regions by storing the key in a Key Vault that is replicated using Key Vault's geo-replication feature (premium tier). They assign the same user-assigned managed identity to both SQL servers and grant access to the key vault. During a failover, the secondary server must be able to unwrap the DEK. If the key vault in the secondary region is not reachable due to network issues, the database will not start. They mitigate this by using a private endpoint for Key Vault and ensuring the key vault is in the same region as each SQL server.
Enterprise Scenario 3: SaaS Provider with Multi-Tenant Isolation A SaaS provider hosts hundreds of customer databases on a single Azure SQL Managed Instance. They use TDE with a single CMK for all databases, but each customer has their own key in a separate Key Vault for column-level encryption using Always Encrypted. They must carefully manage permissions: the managed instance identity needs access to the TDE CMK, while each customer's application identity needs access to their own column master key. A common misconfiguration is granting the managed instance identity access to customer keys, which violates isolation. They use Azure RBAC to scope permissions and audit all key access.
AZ-500 tests TDE with CMK under objective 2.3 (Implement data encryption) and 2.4 (Implement key management). Expect 2-3 questions on this topic. The most common wrong answers are:
"TDE with CMK encrypts data in transit." — Wrong. TDE encrypts data at rest only. Always Encrypted or TLS encrypts data in transit.
"You must grant the SQL server 'Contributor' role on Key Vault." — Wrong. The SQL server needs specific Key Vault access policies: get, wrapKey, unwrapKey. Contributor role is too broad and not sufficient (it does not grant key operations).
"After key rotation, the DEK is automatically re-encrypted." — Wrong. The DEK is re-encrypted only when it is unwrapped (e.g., during a restart or failover). You must force re-encryption manually or wait for an event.
"Soft delete is optional." — Wrong. For production, soft delete and purge protection are critical. Without them, key deletion leads to permanent data loss.
Key numbers and terms that appear verbatim:
Key sizes: RSA 2048, 3072, 4096; symmetric AES 256.
Permissions: get, wrapKey, unwrapKey.
Key Vault tiers: Standard (RSA keys) and Premium (symmetric keys).
Soft delete retention: 90 days.
Edge cases the exam loves:
What happens if the CMK is disabled? The database becomes inaccessible immediately.
What happens if the key vault is deleted but soft delete is enabled? The database becomes inaccessible, but you can restore the vault within 90 days.
Can you use a CMK in a different Azure AD tenant? Yes, but you must configure cross-tenant authentication.
How to eliminate wrong answers:
If the answer mentions "encrypting network traffic," eliminate it (that's not TDE).
If the answer says "no permissions needed" or "just Contributor role," eliminate it.
If the answer says "automatic re-encryption after rotation," eliminate it unless it specifies a restart/failover.
TDE with CMK uses a two-tier hierarchy: DEK encrypted by a KEK stored in Azure Key Vault.
The SQL server managed identity needs get, wrapKey, and unwrapKey permissions on the Key Vault.
Soft delete and purge protection must be enabled on Key Vault to prevent permanent data loss.
Key rotation does not automatically re-encrypt the DEK; you must force re-encryption manually or wait for a restart/failover.
If the CMK is disabled or deleted, the database becomes inaccessible until the key is re-enabled or restored.
TDE encrypts data at rest only; use Always Encrypted or TLS for column-level or in-transit encryption.
For geo-replicated databases, ensure the secondary server has access to the same CMK.
These come up on the exam all the time. Here's how to tell them apart.
Service-Managed Keys (SMK)
Key is managed by Azure automatically.
No additional cost for key management.
Key rotation is automatic and transparent.
No access to key material or audit logs.
Cannot revoke access to the key.
Customer-Managed Keys (CMK)
Key is stored in your Azure Key Vault.
You have full control over key lifecycle.
You must rotate keys manually or automate.
You can audit key access via Key Vault logs.
You can revoke access, making database inaccessible.
Mistake
TDE with CMK encrypts data in transit as well as at rest.
Correct
TDE only encrypts data at rest on disk and backups. Data in transit is protected by TLS/SSL, not TDE.
Mistake
You need to grant the SQL server 'Contributor' or 'Key Vault Contributor' role to use CMK.
Correct
The SQL server needs specific Key Vault access policy permissions: get, wrapKey, and unwrapKey. Azure RBAC roles like Contributor do not grant these key operations.
Mistake
After rotating the CMK, the DEK is automatically re-encrypted immediately.
Correct
The DEK is re-encrypted only when it is unwrapped (e.g., during a database restart, failover, or manual re-encryption). Automatic rotation does not trigger immediate re-encryption.
Mistake
Soft delete is optional and not required for TDE with CMK.
Correct
Soft delete and purge protection are strongly recommended. Without them, if the key is deleted, the database becomes permanently inaccessible. Soft delete allows recovery within 90 days.
Mistake
You can use any key type in Key Vault for TDE, including secrets and certificates.
Correct
Only keys (RSA or symmetric) can be used as TDE protectors. Secrets and certificates are not supported for TDE.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
The database becomes inaccessible immediately. The SQL server cannot unwrap the DEK, so it cannot start or serve queries. To restore access, re-enable the key in Key Vault. If the key is deleted and soft delete is enabled, you can recover it within 90 days. Without soft delete, the database is permanently inaccessible and you will need to restore from backup (if available) with a new key.
Yes, the key vault can be in a different subscription, but it must be in the same Azure region as the SQL server. The SQL server's managed identity must be granted permissions on that key vault. Cross-tenant scenarios are also supported but require additional configuration.
The overhead is minimal. The DEK is decrypted once per database startup and cached in memory. The actual page encryption/decryption uses the DEK, which is the same as with service-managed keys. The Key Vault operation (unwrap) is a one-time cost. No measurable impact on query performance.
Create a new key version in Key Vault. Then, set the TDE protector to the new key version using PowerShell or CLI. This operation re-encrypts the DEK online without downtime. However, note that the DEK is not re-encrypted for existing pages until a restart or failover. To force immediate re-encryption, run ALTER DATABASE ... SET ENCRYPTION ON.
TDE encrypts the entire database at rest (pages and backups). Always Encrypted encrypts specific columns within a table, and the keys are stored client-side or in Key Vault. TDE is transparent to the application; Always Encrypted requires client driver support and can encrypt data in transit as well.
Yes, Azure Key Vault Premium supports symmetric keys (AES 256-bit). They can be used as TDE protectors. However, they are not supported in the Standard tier. RSA keys are the most common choice.
It needs three Key Vault access policy permissions: get (to retrieve the key), wrapKey (to encrypt the DEK), and unwrapKey (to decrypt the DEK). Without unwrapKey, the database cannot start.
You've just covered TDE with Customer-Managed Keys in Key Vault — now see how well it sticks with free AZ-500 practice questions. Full explanations included, no account needed.
Done with this chapter?