Rotate the Transparent data encryption (TDE) protector

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

This article describes key rotation for a server using a TDE protector from Azure Key Vault. Rotating the logical 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, because this only decrypts and re-encrypts the database's data encryption key, not the entire database.

This article discusses both automated and manual methods to rotate the TDE protector on the server.

Important considerations when rotating the TDE protector

  • When the TDE protector is changed/rotated, old backups of the database, including backed-up log files, aren't 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 Azure Key Vault (AKV), so database backups can be restored.
  • Even when switching from customer managed key (CMK) to service-managed key, keep all previously used keys in AKV. This ensures database backups, including backed-up log files, can be restored with the TDE protectors stored in AKV.
  • Apart from old backups, transaction log files might also require access to the older TDE protector. To determine if there are any remaining logs that still require the older key, after performing key rotation, use the sys.dm_db_log_info dynamic management view (DMV). This DMV returns information on the virtual log file (VLF) of the transaction log along with its encryption key thumbprint of the VLF.
  • Older keys need to be kept in AKV and available to the server based on the backup retention period configured as back of backup retention policies on the database. This helps ensure any Long Term Retention (LTR) backups on the server can still be restored using the older keys.

Note

A paused dedicated SQL pool in Azure Synapse Analytics must be resumed before key rotations.

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 (TDE) for dedicated SQL pools inside Synapse workspaces, see Azure Synapse Analytics encryption.

Important

Do not delete previous versions of the key after a rollover. When keys are rolled over, some data is still encrypted with the previous keys, such as older database backups, backed-up log files and transaction log files.

Prerequisites

  • This how-to guide assumes that you're already using a key from Azure Key Vault as the TDE protector for Azure SQL Database or Azure Synapse Analytics. See Transparent data encryption with BYOK Support.
  • You must have Azure PowerShell installed and running.

Tip

Recommended but optional - create the key material for the TDE protector in a hardware security module (HSM) or local key store first, and import the key material to Azure Key Vault. Follow the instructions for using a hardware security module (HSM) and Key Vault to learn more.

Go to the Azure portal

Automatic key rotation

Automatic rotation for the TDE protector can be enabled when configuring the TDE protector for the server or the database, from the Azure portal or using the below PowerShell or the Azure CLI commands. Once enabled, the server or database 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.

Automatic rotation in a server, database, or managed instance can be used with automatic key rotation in Azure Key Vault to enable end-to-end zero touch rotation for TDE keys.

Note

If the server or managed instance has geo-replication configured, prior to enabling automatic rotation, additional guidelines need to be followed as described here.

Using the Azure portal:

  1. Browse to the Transparent data encryption section for an existing server or managed instance.
  2. Select the Customer-managed key option and select the key vault and key to be used as the TDE protector.
  3. Check the Auto-rotate key checkbox.
  4. Select Save.

Screenshot of auto rotate key configuration for Transparent data encryption.

Automatic key rotation at the database level

Automatic key rotation can also be enabled at the database level for Azure SQL Database. This is useful when you want to enable automatic key rotation for only one or a subset of databases on a server. For more information, see Identity and key management for TDE with database level customer-managed keys.

For Azure portal information on setting up automatic key rotation at the database level, see Update an existing Azure SQL Database with database level customer-managed keys.

Automatic key rotation for geo-replication configurations

In an Azure SQL Database geo-replication configuration where the primary server is set to use TDE with CMK, the secondary server also needs to be configured to enable TDE with CMK with the same key used on the primary.

Using the Azure portal:

  1. Browse to the Transparent data encryption section for the primary server.

  2. Select the Customer-managed key option and select the key vault and key to be used as the TDE protector.

  3. Check the Auto-rotate key checkbox.

  4. Select Save.

    Screenshot of auto rotate key configuration for transparent data encryption in a geo-replication scenario on the primary server.

  5. Browse to the Transparent data encryption section for the secondary server.

  6. Select the Customer-managed key option and select the key vault and key to be used as the TDE protector. Use the same key as you used for the primary server.

  7. Uncheck Make this key the default TDE protector.

  8. Select Save.

    Screenshot of auto rotate key configuration for transparent data encryption in a geo-replication scenario on the secondary server.

When the key is rotated on the primary server, it's automatically transferred to the secondary server.

Use different keys for each server

It's possible to configure the primary and secondary servers with a different key vault key when configuring TDE with CMK in the Azure portal. It's not evident in the Azure portal that the key used to protect the primary server is also the same key that protects the primary database that has been replicated to the secondary server. However, you can use PowerShell, the Azure CLI, or REST APIs to obtain details about keys that are used on the server. This shows that auto rotated keys are transferred from the primary server to the secondary server.

Here's an example of using PowerShell commands to check for keys that are transferred from the primary server to the secondary server after key rotation.

  1. Execute the following command on the primary server to display the key details of a server:

    Get-AzSqlServerKeyVaultKey -ServerName <logicalServerName> -ResourceGroupName <SQLDatabaseResourceGroupName> 
    
  2. You should see similar results to the following:

    ResourceGroupName : <SQLDatabaseResourceGroupName> 
    ServerName        : <logicalServerName> 
    ServerKeyName     : <keyVaultKeyName> 
    Type              : AzureKeyVault 
    Uri               : https://<keyvaultname>.vault.azure.net/keys/<keyName>/<GUID> 
    Thumbprint        : <thumbprint> 
    CreationDate      : 12/13/2022 8:56:32 PM
    
  3. Execute the same Get-AzSqlServerKeyVaultKey command on the secondary server:

    Get-AzSqlServerKeyVaultKey -ServerName <logicalServerName> -ResourceGroupName <SQLDatabaseResourceGroupName> 
    
  4. If the secondary server has a default TDE protector using a different key than the primary server, you should see two (or more) keys. The first key being the default TDE protector, and the second key is the key used in the primary server used to protect the replicated database.

  5. When the key is rotated on the primary server, it's automatically transferred to the secondary server. If you were to run the Get-AzSqlServerKeyVaultKey again on the primary server, you should see two keys. The first key is the original key, and the second key, which is the current key that was generated as part of the key rotation.

  6. Running the Get-AzSqlServerKeyVaultKey command on the secondary server should also show the same keys that are present in the primary server. This confirms that the rotated keys on the primary server are automatically transferred to the secondary server, and used to protect the database replica.

Manual key rotation

Manual key rotation uses the following commands to add a new key, which could be under a new key name or even another key vault. Using this approach supports adding the same key to different key vaults to support high-availability and geo-dr scenarios. Manual key rotation can also be done using the Azure portal.

With manual key rotation, when a new key version is generated in key vault (either manually or via automatic key rotation policy in key vault), the same must be manually set as the TDE protector on the server.

Note

The combined length for the key vault name and key name cannot exceed 94 characters.

Using the Azure portal:

  1. Browse to the Transparent data encryption menu for an existing server or managed instance.
  2. Select the Customer-managed key option and select the key vault and key to be used as the new TDE protector.
  3. Select Save.

Screenshot of manually rotate key configuration for Transparent data encryption.

Switch TDE protector mode

Using the Azure portal to switch the TDE protector from Microsoft-managed to BYOK mode:

  1. Browse to the Transparent data encryption menu for an existing server or managed instance.
  2. Select the Customer-managed key option.
  3. Select the key vault and key to be used as the TDE protector.
  4. Select Save.