Muokkaa

Jaa

Azure SQL transparent data encryption with customer-managed key

  • Artikkeli

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, optionally 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-premises HSM device.

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.

Note

  • This article applies to Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics (dedicated SQL pools (formerly SQL DW)). For documentation on transparent data encryption for dedicated SQL pools inside Synapse workspaces, see Azure Synapse Analytics encryption.
  • To provide Azure SQL customers with two layers of encryption of data at rest, infrastructure encryption (using AES-256 encryption algorithm) with platform managed keys is being rolled out. This provides an addition layer of encryption at rest along with TDE with customer-managed keys, which is already available. For Azure SQL Database and Azure SQL Managed Instance, all databases, including the master database and other system databases, will be encrypted when infrastructure encryption is turned on. At this time, customers must request access to this capability. If you are interested in this capability, contact AzureSQLDoubleEncryptionAtRest@service.microsoft.com.

Note

Microsoft Entra ID was previously known as Azure Active Directory (Azure AD).

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 can't see nor extract encryption keys;

Important

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.

How customer-managed TDE works

Diagram showing setup and functioning of the customer-managed TDE.

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.

Note

It may take around 10 minutes for any permission changes to take effect for the key vault. This includes revoking access permissions to the TDE protector in AKV, and users within this time frame may still have access permissions.

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're 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

Important

Both soft-delete and purge protection must be enabled on the key vault when configuring customer-managed TDE on a new or existing server or managed instance.

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, or RSA HSM 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).

Note

Azure SQL and SQL Server on Azure VM support using an RSA key stored in a Managed HSM as TDE protector. Azure Key Vault Managed HSM is a fully managed, highly available, single-tenant, standards-compliant cloud service that enables you to safeguard cryptographic keys for your cloud applications, using FIPS 140-2 Level 3 validated HSMs. Learn more about Managed HSMs and the configuration or RBAC permissions needed for SQL Server through the article, Set up SQL Server TDE Extensible Key Management by using Azure Key Vault.

An issue with Thales CipherTrust Manager versions prior to v2.8.0 prevents keys newly imported into Azure Key Vault from being used with Azure SQL Database or Azure SQL Managed Instance for customer-managed TDE scenarios. More details about this issue can be found here. For such cases, please wait 24 hours after importing the key into key vault to begin using it as TDE protector for the server or managed instance. This issue has been resolved in Thales CipherTrust Manager v2.8.0.

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.

Note

To allow greater flexibility in configuring customer-managed TDE, Azure SQL Database and Azure SQL Managed Instance in one region can now be linked to key vault in any other region. The server and key vault do not have to be co-located in the same 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.

Note

Setting TDE with CMK using manual or automated rotation of keys will always use the latest version of the key that is supported. The setup does not allow using a previous or lower version of keys. Always using the latest key version complies with the Azure SQL security policy that disallows previous key versions that might be compromised. The previous versions of the key might be needed for database backup or restore purposes, especially for long-term retention backups, where the older key versions must be preserved. For geo-replication setups, all keys required by the source server need to be present on the target server.

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.

Note

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 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:

Note

  • If key access is restored within 30 minutes, the database will autoheal within the next hour.
  • If key access is restored after more than 30 minutes, autoheal of the database isn't possible. Bringing back the database requires extra steps on the Azure portal and can take a significant amount of time depending on the size of the database.
  • Once the database is back online, previously configured server-level settings that might include failover group configuration, tags, and database-level settings such as elastic pools configuration, read scale, auto pause, point-in-time-restore history, long term retention policy, and others will be lost. Therefore, it's recommended that customers implement a notification system that identifies loss of encryption key access within 30 minutes. Once the 30 minutes window has expired, we recommend validating all server and database level settings on the recovered database.

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

TDE BYOK Inaccessible Database.

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.net (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/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.

Important

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 pane. 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: "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

With the AKV providing multiple layers of redundancy, TDEs using a customer managed key can take advantage of AKV availability and resilience, and rely fully on the AKV redundancy solution.

AKV's multiple redundancy layers ensure key access even if individual service components fail or Azure regions or availability zones are down. For more information, see Azure Key Vault availability and redundancy.

AKV offers the following components of availability and resilience that are provided automatically without user intervention:

Note

For all pair regions, AKV keys are replicated to both regions and there are Hardware Security Modules (HSM) in both regions that can operate on those keys. For more information, see Data replication. This applies to both Standard and Premium Azure Key Vault service tiers, and software or hardware keys.

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.

Diagram showing failover groups and geo-dr.

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.

Important

Earlier versions of built-in policies for customer-managed TDE containing the AuditIfNotExist effect have been deprecated. Existing policy assignments using the deprecated policies are not affected and will continue to work as before.

Related content