Azure SQL Transparent Data Encryption with customer-managed key
Azure SQL Transparent Data Encryption (TDE) with customer-managed key 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 transparent data encryption, 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, backed by FIPS 140-2 Level 2 validated hardware security modules (HSMs). 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, imported, or transferred to the key vault from an on-prem HSM device.
For Azure SQL Database and Azure SQL Data Warehouse, the TDE protector is set at the logical 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 SQL Database logical server and managed instance throughout this document, unless stated differently.
For those using service-managed TDE who would like to start using customer-managed TDE, data remains encrypted during the process of switching over, and there is no downtime nor re-encryption of the database files. Switching from a service-managed key to a customer-managed key only requires re-encryption of the DEK, which is a fast and online operation.
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 Microsoft cannot see nor extract encryption keys;
How customer-managed TDE works
For server to be able to use TDE protector stored in AKV for encryption of the DEK, key vault administrator needs to give the following access rights to the server using its unique AAD identity:
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
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
Key vault and SQL Database/managed instance must belong to the same Azure Active Directory tenant. Cross-tenant key vault and server interactions are not supported. To move resources afterwards, TDE with AKV will have to be reconfigured. Learn more about moving resources.
Soft-delete feature must be enabled on the key vault, to protect from data loss accidental key (or key vault) deletion happens. Soft-deleted resources are retained for 90 days, unless recovered or purged by the customer in the meantime. The recover and purge actions have their own permissions associated in a key vault access policy. Soft-delete feature is off by default and can be enabled via Powershell or CLI. It cannot be enabled via Azure portal.
Grant the SQL Database server or managed instance access to the key vault (get, wrapKey, unwrapKey) using its Azure Active Directory identity. When using Azure portal, the Azure AD identity gets automatically created. When using PowerShell or CLI, the Azure AD identity must be explicitly created and completion should be verified. See Configure TDE with BYOK and Configure TDE with BYOK for Managed Instance for detailed step-by-step instructions when using PowerShell.
When using firewall with AKV, you must enable option Allow trusted Microsoft services to bypass the firewall.
Requirements for configuring TDE protector
TDE protector can be only asymmetric, RSA 2048 or RSA HSM 2048 key.
The key cannot have activation or expiration date set.
The key must be in the Enabled state in the key vault.
If you are 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 here 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 only the key from the key vault in the same region as a TDE protector. System will use
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 (e.g. 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.
Inaccessible TDE protector
When transparent data encryption 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 will start 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.
If the database is inaccessible due to an intermittent networking outage, there is no action required and the databases will come back online automatically.
After access to the key is restored, taking database back online requires additional time and steps, which may vary based on the time elapsed without access to the key and the size of the data in the database:
If key access is restored within 8 hours, the database will auto-heal within next hour.
If key access is restored after more than 8 hours, auto-heal is not possible and bringing the database back can take a significant amount of time depending on the size of the database and requires opening a support ticket. Once the database is back online, previously configured server-level settings such as failover group configuration, point-in-time-restore history, and tags will be lost. Therefore, it's recommended implementing a notification system that allows you to identify and address the underlying key access issues within 8 hours.
Accidental TDE protector access revocation
It may happen that someone with sufficient access rights to the key vault accidentally disables server access to the key by:
revoking key vault's get, wrapKey, unwrapKey permissions from the server
deleting the key
deleting the key vault
changing key vault's firewall rules
deleting the managed identity of the server in Azure Active Directory
Learn more about the common causes for database to become inaccessible.
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 will enable you to reinstate access as soon as possible.
- Action Groups can be defined to send you notifications and alerts based on your preferences, e.g. Email/SMS/Push/Voice, 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.
At any moment there can be not more than one TDE protector set for a server. It's the key marked with "Make the key the default TDE protector" in the Azure portal blade. However, multiple additional keys can be linked to a server without marking them as a TDE protector. These keys are not used for protecting DEK, but can be used during restore from a backup, if backup file is encrypted with the key with the corresponding thumbprint.
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:
<Servername> does not have access to all AKV URIs created between <Timestamp #1> and <Timestamp #2>. Please retry operation after restoring all AKV URIs."
To mitigate it, run the Get-AzSqlServerKeyVaultKey cmdlet for target SQL Database logical server or Get-AzSqlInstanceKeyVaultKey for 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 an Azure SQL database. To learn more about backup recovery for SQL Data Warehouse, see Recover an Azure SQL Data Warehouse. For SQL Server's native backup/restore with managed instance, see Quickstart: Restore a database to a Managed Instance
Additional 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 will be needed to restore the database. If the log file is using a TDE protector stored in Azure Key Vault, this key will be 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 is no configured geo-redundancy for server, it is highly recommended to configure the server to use two different key vaults in two different regions with the same key material. It can be accomplished by creating a TDE protector using the primary key vault co-located in the same region as the server and cloning the key into a key vault in a different Azure region, so that the server has access to a second key vault should the primary key vault experience an outage while the database is up and running.
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 Azure portal to backup and restore key. The key in the secondary key vault in se other region should not be marked as TDE protector, and it's not even allowed.
If there is an outage affecting primary key vault, and only then, 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 will not happen if TDE protector is inaccessible because of revoked access rights, or because key or key vault is deleted, as it may indicate that customer intentionally wanted to restrict server from accessing the key.
Geo-DR and customer-managed TDE
In both active geo-replication and failover groups scenarios, each server involved requires a separate key vault, that must be co-located with the server in the same Azure region. 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 local 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) is not 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:
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 may 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. It should be done on a regular basis to confirm the access permissions for SQL to both key vaults have been maintained.
You may also want to check the following PowerShell sample scripts for the common operations with customer-managed TDE: