Azure SQL transparent data encryption with customer-managed key

Applies to: Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics (dedicated SQL pools only)

Transparent data encryption (TDE) in Azure SQL with customer-managed key (CMK) enables Bring Your Own Key (BYOK) scenario for data protection at rest, and allows organizations to implement separation of duties in the management of keys and data. With customer-managed TDE, the customer is responsible for and in a full control of a key lifecycle management (key creation, upload, rotation, deletion), key usage permissions, and auditing of operations on keys.

In this scenario, the key used for encryption of the Database Encryption Key (DEK), called TDE protector, is a customer-managed asymmetric key stored in a customer-owned and customer-managed Azure Key Vault (AKV), a cloud-based external key management system. Key Vault is highly available and scalable secure storage for RSA cryptographic keys. It doesn't allow direct access to a stored key, but provides services of encryption/decryption using the key to the authorized entities. The key can be generated by the key vault, or imported.

For Azure SQL Database and Azure Synapse Analytics, the TDE protector is set at the server level and is inherited by all encrypted databases associated with that server. For Azure SQL Managed Instance, the TDE protector is set at the instance level and is inherited by all encrypted databases on that instance. The term server refers both to a server in SQL Database and Azure Synapse and to a managed instance in SQL Managed Instance throughout this document, unless stated differently.

Managing the TDE protector at the database level in Azure SQL Database is available. For more information, see Transparent data encryption (TDE) with customer-managed keys at the database level.

Benefits of the customer-managed TDE

Customer-managed TDE provides the following benefits to the customer:

• Full and granular control over usage and management of the TDE protector;

• Transparency of the TDE protector usage;

• Ability to implement separation of duties in the management of keys and data within the organization;

• Key Vault administrator can revoke key access permissions to make encrypted database inaccessible;

• Central management of keys in AKV;

• Greater trust from your end customers, since AKV is designed such that Azure cannot see nor extract encryption keys;

How customer-managed TDE works

In order for the logical server in Azure to use the TDE protector stored in AKV for encryption of the DEK, the Key Vault Administrator needs to give access rights to the server using its unique Microsoft Entra identity. There are two access models to grant the server access to the key vault:

• Azure role-based access control (RBAC) - Use Azure RBAC to grant a user, group, or application access to the key vault. This method is recommended for its flexibility and granularity. The Key Vault Crypto Service Encryption User role is needed by the server identity to be able to use the key for encryption and decryption operations.

• Vault access policy - Use the key vault access policy to grant the server access to the key vault. This method is simpler and more straightforward, but less flexible. The server identity needs to have the following permissions on the key vault:

  • get - for retrieving the public part and properties of the key in the Key Vault
  • wrapKey - to be able to protect (encrypt) DEK
  • unwrapKey - to be able to unprotect (decrypt) DEK

In the Access configuration Azure portal menu of the key vault, you have the option of selecting Azure role-based access control or Vault access policy. For step by step instructions on setting up an Azure Key Vault access configuration for TDE, see Set up SQL Server TDE Extensible Key Management by using Azure Key Vault. For more information on the access models, see Azure Key Vault security.

A Key Vault Administrator can also enable logging of key vault audit events, so they can be audited later.

When server is configured to use a TDE protector from AKV, the server sends the DEK of each TDE-enabled database to the key vault for encryption. Key vault returns the encrypted DEK, which is then stored in the user database.

When needed, server sends protected DEK to the key vault for decryption.

Auditors can use Azure Monitor to review key vault AuditEvent logs, if logging is enabled.

Requirements for configuring customer-managed TDE

Requirements for configuring AKV

• Soft-delete and purge protection features must be enabled on the key vault to protect from data loss due to accidental key (or key vault) deletion.

• Grant the server or managed instance access to the key vault (get, wrapKey, unwrapKey) using its Microsoft Entra identity. The server identity can be a system-assigned managed identity or a user-assigned managed identity assigned to the server. When using the Azure portal, the Microsoft Entra identity gets automatically created when the server is created. When using PowerShell or Azure CLI, the Microsoft Entra identity must be explicitly created and should be verified. See Configure TDE with BYOK and Configure TDE with BYOK for SQL Managed Instance for detailed step-by-step instructions when using PowerShell.

  • Depending on the permission model of the key vault (access policy or Azure RBAC), key vault access can be granted either by creating an access policy on the key vault, or by creating a new Azure RBAC role assignment with the role Key Vault Crypto Service Encryption User.

• When using a firewall with AKV, you must enable the option Allow trusted Microsoft services to bypass the firewall, unless you are using private endpoints for the AKV. For more information, see Configure Azure Key Vault firewalls and virtual networks.

Enable soft-delete and purge protection for AKV

Soft-delete and purge protection are important features of Azure Key Vault that allow recovery of deleted vaults and deleted key vault objects, reducing the risk of a user accidentally or maliciously deleting a key or a key vault.

• Soft-deleted resources are retained for 90 days, unless recovered or purged by the customer. The recover and purge actions have their own permissions associated in a key vault access policy. The soft-delete feature is on by default for new key vaults and can also be enabled using the Azure portal, PowerShell or Azure CLI.

• Purge protection can be turned on using Azure CLI or PowerShell. When purge protection is enabled, a vault or an object in the deleted state can't be purged until the retention period has passed. The default retention period is 90 days, but is configurable from 7 to 90 days through the Azure portal.

• Azure SQL requires soft-delete and purge protection to be enabled on the key vault containing the encryption key being used as the TDE protector for the server or managed instance. This helps prevent the scenario of accidental or malicious key vault or key deletion that can lead to the database going into Inaccessible state.

• When configuring the TDE protector on an existing server or during server creation, Azure SQL validates that the key vault being used has soft-delete and purge protection turned on. If soft-delete and purge protection aren't enabled on the key vault, the TDE protector setup fails with an error. In this case, soft-delete and purge protection must first be enabled on the key vault and then the TDE protector setup should be performed.

Requirements for configuring TDE protector

• TDE protector can only be an asymmetric, RSA key. The supported key lengths are 2048 bits and 3072 bits.

• The key activation date (if set) must be a date and time in the past. Expiration date (if set) must be a future date and time.

• The key must be in the Enabled state.

• If you're importing existing key into the key vault, make sure to provide it in the supported file formats (.pfx, .byok, or .backup).

Recommendations when configuring customer-managed TDE

Recommendations when configuring AKV

• Associate at most 500 General Purpose or 200 Business Critical databases in total with a key vault in a single subscription to ensure high availability when server accesses the TDE protector in the key vault. These figures are based on the experience and documented in the key vault service limits. The intention is to prevent issues after server failover, as it will trigger as many key operations against the vault as there are databases in that server.

• Set a resource lock on the key vault to control who can delete this critical resource and prevent accidental or unauthorized deletion. Learn more about resource locks.

• Enable auditing and reporting on all encryption keys: Key vault provides logs that are easy to inject into other security information and event management tools. Operations Management Suite Log Analytics is one example of a service that is already integrated.

• Link each server with two key vaults that reside in different regions and hold the same key material, to ensure high availability of encrypted databases. Mark the key from one of the key vaults as the TDE protector. System will automatically switch to the key vault in the second region with the same key material, if there's an outage affecting the key vault in the first region.

Recommendations when configuring TDE protector

• Keep a copy of the TDE protector on a secure place or escrow it to the escrow service.

• If the key is generated in the key vault, create a key backup before using the key in AKV for the first time. Backup can be restored to an Azure Key Vault only. Learn more about the Backup-AzKeyVaultKey command.

• Create a new backup whenever any changes are made to the key (for example, key attributes, tags, ACLs).

• Keep previous versions of the key in the key vault when rotating keys, so older database backups can be restored. When the TDE protector is changed for a database, old backups of the database are not updated to use the latest TDE protector. At restore time, each backup needs the TDE protector it was encrypted with at creation time. Key rotations can be performed following the instructions at Rotate the transparent data encryption protector Using PowerShell.

• Keep all previously used keys in AKV even after switching to service-managed keys. It ensures database backups can be restored with the TDE protectors stored in AKV. TDE protectors created with Azure Key Vault have to be maintained until all remaining stored backups have been created with service-managed keys. Make recoverable backup copies of these keys using Backup-AzKeyVaultKey.

• To remove a potentially compromised key during a security incident without the risk of data loss, follow the steps from the Remove a potentially compromised key.

Rotation of TDE protector

Rotating the TDE protector for a server means to switch to a new asymmetric key that protects the databases on the server. Key rotation is an online operation and should only take a few seconds to complete. The operation only decrypts and re-encrypts the database encryption key, not the entire database.

Rotation of the TDE protector can either be done manually or by using the automated rotation feature.

Automated rotation of the TDE protector can be enabled when configuring the TDE protector for the server. Automated rotation is disabled by default. When enabled, the server will continuously check the key vault for any new versions of the key being used as the TDE protector. If a new version of the key is detected, the TDE protector on the server or database will be automatically rotated to the latest key version within 24 hours.

When used with automated key rotation in Azure Key Vault, this feature enables end-to-end zero-touch rotation for the TDE protector on Azure SQL Database and Azure SQL Managed Instance.

Geo-replication considerations when configuring automated rotation of the TDE protector

To avoid issues while establishing or during geo-replication, when automatic rotation of the TDE protector is enabled on the primary or secondary server, it's important to follow these rules when configuring geo-replication:

• Both the primary and secondary servers must have Get, wrapKey and unwrapKey permissions to the primary server's key vault (key vault that holds the primary server's TDE protector key).

• For a server with automated key rotation enabled, before initiating geo-replication, add the encryption key being used as TDE protector on the primary server to the secondary server. The secondary server requires access to the key in the same key vault being used with the primary server (and not another key with the same key material). Alternatively, before initiating geo-replication, ensure that the secondary server's managed identity (user-assigned or system-assigned) has required permissions on the primary server's key vault, and the system will attempt to add the key to the secondary server.

• For an existing geo-replication setup, prior to enabling automated key rotation on the primary server, add the encryption key being used as TDE protector on the primary server to the secondary server. The secondary server requires access to the key in the same key vault being used with the primary server (and not another key with the same key material). Alternatively, before enabling automated key, ensure that the secondary server's managed identity (user-assigned or system-assigned) has required permissions on the primary server's key vault, and the system will attempt to add the key to the secondary server.

• Geo-replication scenarios using customer-managed keys (CMK) for TDE is supported. TDE with automatic key rotation must be configured on all servers if you're configuring TDE in the Azure portal. For more information on setting up automatic key rotation for geo-replication configurations with TDE, see Automatic key rotation for geo-replication configurations.

Inaccessible TDE protector

When TDE is configured to use a customer-managed key, continuous access to the TDE protector is required for the database to stay online. If the server loses access to the customer-managed TDE protector in AKV, in up to 10 minutes a database starts denying all connections with the corresponding error message and change its state to Inaccessible. The only action allowed on a database in the Inaccessible state is deleting it.

After access to the key is restored, taking database back online requires extra time and steps, which might vary based on the time elapsed without access to the key and the size of the data in the database:

Below is a view of the extra steps required on the portal to bring an inaccessible database back online.

Accidental TDE protector access revocation

It might happen that someone with sufficient access rights to the key vault accidentally disables server access to the key by:

• revoking the key vault's get, wrapKey, unwrapKey permissions from the server

• deleting the key

• deleting the key vault

• changing the key vault's firewall rules

• deleting the managed identity of the server in Microsoft Entra ID

Learn more about the common causes for database to become inaccessible.

Blocked connectivity between SQL Managed Instance and Key Vault

On SQL Managed Instance, network errors while trying to access TDE protector in Azure Key Vault might not cause the databases to change its state to Inaccessible but will render the instance unavailable afterwards. This happens mostly when the key vault resource exists but its endpoint can't be reached from the managed instance. All scenarios where the key vault endpoint can be reached but connection is denied, missing permissions, etc., will cause the databases to change its state to Inaccessible.

The most common causes for lack of networking connectivity to Key Vault are:

• Key Vault is exposed via private endpoint and the private IP address of the AKV service isn't allowed in the outbound rules of the Network Security Group (NSG) associated with the managed instance subnet.
• Bad DNS resolution, like when the key vault FQDN isn't resolved or resolves to an invalid IP address.

Test the connectivity from SQL Managed Instance to the Key Vault hosting the TDE protector.

• The endpoint is your vault FQDN, like <vault_name>.vault.azure.cn (without the https://).
• The port to be tested is 443.
• The result for RemoteAddress should exist and be the correct IP address
• The result for TCP test should be TcpTestSucceeded: True.

In case the test returns TcpTestSucceeded: False, review the networking configuration:

• Check the resolved IP address, confirm it's valid. A missing value means there's issues with DNS resolution.
  • Confirm that the network security group on the managed instance has an outbound rule that covers the resolved IP address on port 443, especially when the resolved address belongs to the key vault's private endpoint.
  • Check other networking configurations like route table, existence of virtual appliance and its configuration, etc.

Monitoring of the customer-managed TDE

To monitor database state and to enable alerting for loss of TDE protector access, configure the following Azure features:

• Azure Resource Health. An inaccessible database that has lost access to the TDE protector will show as "Unavailable" after the first connection to the database has been denied.
• Activity Log when access to the TDE protector in the customer-managed key vault fails, entries are added to the activity log. Creating alerts for these events enable you to reinstate access as soon as possible.
• Action Groups can be defined to send you notifications and alerts based on your preferences, for example, Email/SMS, Logic App, Webhook, ITSM, or Automation Runbook.

Database backup and restore with customer-managed TDE

Once a database is encrypted with TDE using a key from Key Vault, any newly generated backups are also encrypted with the same TDE protector. When the TDE protector is changed, old backups of the database are not updated to use the latest TDE protector.

To restore a backup encrypted with a TDE protector from Key Vault, make sure that the key material is available to the target server. Therefore, we recommend that you keep all the old versions of the TDE protector in key vault, so database backups can be restored.

If the key that is needed for restoring a backup is no longer available to the target server, the following error message is returned on the restore try: "Target server <Servername> doesn't have access to all AKV URIs created between <Timestamp #1> and <Timestamp #2>. Retry operation after restoring all AKV URIs."

To mitigate it, run the Get-AzSqlServerKeyVaultKey cmdlet for the target server or Get-AzSqlInstanceKeyVaultKey for the target managed instance to return the list of available keys and identify the missing ones. To ensure all backups can be restored, make sure the target server for the restore has access to all of keys needed. These keys don't need to be marked as TDE protector.

To learn more about backup recovery for SQL Database, see Recover a database in SQL Database. To learn more about backup recovery for dedicated SQL pools in Azure Synapse Analytics, see Recover a dedicated SQL pool. For SQL Server's native backup/restore with SQL Managed Instance, see Quickstart: Restore a database to SQL Managed Instance.

Another consideration for log files: Backed up log files remain encrypted with the original TDE protector, even if it was rotated and the database is now using a new TDE protector. At restore time, both keys are needed to restore the database. If the log file is using a TDE protector stored in Azure Key Vault, this key is needed at restore time, even if the database has been changed to use service-managed TDE in the meantime.

High availability with customer-managed TDE

Even in cases when there's no configured geo-redundancy for server, it's highly recommended to configure the server to use two different key vaults in two different regions with the same key material. The key in the secondary key vault in the other region shouldn't be marked as TDE protector, and it's not even allowed. If there's an outage affecting the primary key vault, and only then, the system will automatically switch to the other linked key with the same thumbprint in the secondary key vault, if it exists. Note though that switch won't happen if TDE protector is inaccessible because of revoked access rights, or because key or key vault is deleted, as it might indicate that customer intentionally wanted to restrict server from accessing the key. Providing the same key material to two key vaults in different regions can be done by creating the key outside of the key vault, and importing them into both key vaults.

Alternatively, it can be accomplished by generating key using the primary key vault in one region and cloning the key into a key vault in a different Azure region. Use the Backup-AzKeyVaultKey cmdlet to retrieve the key in encrypted format from the primary key vault and then use the Restore-AzKeyVaultKey cmdlet and specify a key vault in the second region to clone the key. Alternatively, use the Azure portal to back up and restore the key. Key backup/restore operation is only allowed between key vaults within the same Azure subscription and Azure geography.

Geo-DR and customer-managed TDE

In both active geo-replication and failover groups scenarios, the primary and secondary servers involved can be linked either to the same key vault (in any region) or to separate key vaults. If separate key vaults are linked to the primary and secondary servers, customer is responsible for keeping the key material across the key vaults consistent, so that geo-secondary is in sync and can take over using the same key from its linked key vault if primary becomes inaccessible due to an outage in the region and a failover is triggered. Up to four secondaries can be configured, and chaining (secondaries of secondaries) isn't supported.

To avoid issues while establishing or during geo-replication due to incomplete key material, it's important to follow these rules when configuring customer-managed TDE (if separate key vaults are used for the primary and secondary servers):

• All key vaults involved must have same properties, and same access rights for respective servers.

• All key vaults involved must contain identical key material. It applies not just to the current TDE protector, but to the all previous TDE protectors that might be used in the backup files.

• Both initial setup and rotation of the TDE protector must be done on the secondary first, and then on primary.

To test a failover, follow the steps in Active geo-replication overview. Testing failover should be done regularly to validate that SQL Database has maintained access permission to both key vaults.

Azure SQL Database server and SQL Managed Instance in one region can now be linked to key vault in any other region. The server and key vault don't have to be colocated in the same region. With this, for simplicity, the primary and secondary servers can be connected to the same key vault (in any region). This helps avoid scenarios where key material might be out of sync if separate key vaults are used for both the servers. Azure Key Vault has multiple layers of redundancy in place to make sure that your keys and key vaults remain available in case of service or region failures. Azure Key Vault availability and redundancy

Azure Policy for customer-managed TDE

Azure Policy can be used to enforce customer-managed TDE during the creation or update of an Azure SQL Database server or Azure SQL Managed Instance. With this policy in place, any attempts to create or update a logical server in Azure or managed instance will fail if it isn't configured with a customer-managed key. The Azure Policy can be applied to the whole Azure subscription, or just within a resource group.

For more information on Azure Policy, see What is Azure Policy? and Azure Policy definition structure.

The following two built-in policies are supported for customer-managed TDE in Azure Policy:

• SQL servers should use customer-managed keys to encrypt data at rest
• Managed instances should use customer-managed keys to encrypt data at rest

The customer-managed TDE policy can be managed by going to the Azure portal, and searching for the Policy service. Under Definitions, search for customer-managed key.

There are three effects for these policies:

• Audit - The default setting, and will only capture an audit report in the Azure Policy activity logs
• Deny - Prevents logical server or managed instance creation or update without a customer-managed key configured
• Disabled - Will disable the policy, and won't restrict users from creating or updating a logical server or managed instance without customer-managed TDE enabled

If the Azure Policy for customer-managed TDE is set to Deny, Azure SQL logical server or managed instance creation will fail. The details of this failure will be recorded in the Activity log of the resource group.

Related content

• Rotate the transparent data encryption protector for SQL Database
• Remove a transparent data encryption (TDE) protector for SQL Database
• Manage transparent data encryption in SQL Managed Instance with your own key using PowerShell
• Microsoft Defender for SQL