Rotate the Transparent data encryption (TDE) protector

Article
10/02/2024

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.

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:

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

For Az PowerShell module installation instructions, see Install Azure PowerShell. For specific cmdlets, see AzureRM.Sql.

To enable automatic rotation for the TDE protector using PowerShell, see the following script. The <keyVaultKeyId> can be retrieved from Key Vault.

Azure SQL Database

Use the Set-AzSqlServerTransparentDataEncryptionProtector command.

Set-AzSqlServerTransparentDataEncryptionProtector -Type AzureKeyVault -KeyId <keyVaultKeyId> `
   -ServerName <logicalServerName> -ResourceGroup <SQLDatabaseResourceGroupName> `
    -AutoRotationEnabled <boolean>

Azure SQL Managed Instance

Use the Set-AzSqlInstanceTransparentDataEncryptionProtector command.

Set-AzSqlInstanceTransparentDataEncryptionProtector -Type AzureKeyVault -KeyId <keyVaultKeyId> `
   -InstanceName <ManagedInstanceName> -ResourceGroup <ManagedInstanceResourceGroupName> `
    -AutoRotationEnabled <boolean>

For information on installing the current release of Azure CLI, see Install the Azure CLI article.

To enable automatic rotation for the TDE protector using the Azure CLI, see the following script.

Azure SQL Database

Use the az sql server tde-key set command.

az sql server tde-key set --server-key-type AzureKeyVault
                          --auto-rotation-enabled true
                          [--kid] <keyVaultKeyId>
                          [--resource-group] <SQLDatabaseResourceGroupName> 
                          [--server] <logicalServerName>

Azure SQL Managed Instance

Use the az sql mi tde-key set command.

az sql mi tde-key set --server-key-type AzureKeyVault
                      --auto-rotation-enabled true
                      [--kid] <keyVaultKeyId>
                      [--resource-group] <ManagedInstanceGroupName> 
                      [--managed-instance] <ManagedInstanceName>

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.

To enable automatic rotation for the TDE protector at the database level using PowerShell, see the following command. Use the -EncryptionProtectorAutoRotation parameter and set to $true to enable automatic key rotation or $false to disable automatic key rotation.

Set-AzSqlDatabase -ResourceGroupName <resource_group_name> -ServerName <server_name> -DatabaseName <database_name> -EncryptionProtectorAutoRotation:$true

To enable automatic rotation for the TDE protector at the database level using the Azure CLI, see the following command. Use the --encryption-protector-auto-rotation parameter and set to True to enable automatic key rotation or False to disable automatic key rotation.

az sql db update --resource-group <resource_group_name> --server <server_name> --name <database_name> --encryption-protector-auto-rotation True

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.

Portal
PowerShell

Using the Azure portal:

Browse to the Transparent data encryption section for the primary server.
Select the Customer-managed key option and select the key vault and key to be used as the TDE protector.
Check the Auto-rotate key checkbox.
Select Save.
Browse to the Transparent data encryption section for the secondary server.
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.
Uncheck Make this key the default TDE protector.
Select Save.

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

Note

If the same key vault key on the primary server is used as the default TDE protector on the secondary server, ensure Auto-rotate key is enabled for both servers. Failure to do so may lead to the auto-rotation workflows entering an error state and prevent further manual key rotation operations.

The <keyVaultKeyId> can be retrieved from Key Vault.

Use the Add-AzSqlServerKeyVaultKey command to add a new key to the secondary server.

# add the key from Key Vault to the secondary server
Add-AzSqlServerKeyVaultKey -KeyId <keyVaultKeyId> -ServerName <logicalServerName> -ResourceGroup <SQLDatabaseResourceGroupName>

Add the same key in the first step to the primary server.

# add the key from Key Vault to the primary server
Add-AzSqlServerKeyVaultKey -KeyId <keyVaultKeyId> -ServerName <logicalServerName> -ResourceGroup <SQLDatabaseResourceGroupName>

Use Set-AzSqlInstanceTransparentDataEncryptionProtector to set the key as the primary protector on the primary server with auto key rotation set to true.

Set-AzSqlServerTransparentDataEncryptionProtector -Type AzureKeyVault -KeyId <keyVaultKeyId> `
 -ServerName <logicalServerName> -ResourceGroup <SQLDatabaseResourceGroupName> `
 -AutoRotationEnabled $true

Rotate the key vault key in the Key Vault using the command Get-AzKeyVaultKey and Set-AzKeyVaultKeyRotationPolicy.

Get-AzKeyVaultKey -VaultName <keyVaultName> -Name <keyVaultKeyName> | Set-AzKeyVaultKeyRotationPolicy -KeyRotationLifetimeAction @{Action = "Rotate"; TimeBeforeExpiry = "P18M"}

Check if the SQL Server (both primary and secondary) has the new key or key version:

Note

Key rotation can take up to an hour to be applied to the server. Wait at least an hour before executing this command.
```
Get-AzSqlServerKeyVaultKey -KeyId <keyVaultKeyId> -ServerName <logicalServerName> -ResourceGroupName <SQLDatabaseResourceGroupName> 
```

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.

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

Get-AzSqlServerKeyVaultKey -ServerName <logicalServerName> -ResourceGroupName <SQLDatabaseResourceGroupName>

You should see similar results to the following:

ResourceGroupName : <SQLDatabaseResourceGroupName> 
ServerName        : <logicalServerName> 
ServerKeyName     : <keyVaultKeyName> 
Type              : AzureKeyVault 
Uri               : https://<keyvaultname>.vault.azure.cn/keys/<keyName>/<GUID> 
Thumbprint        : <thumbprint> 
CreationDate      : 12/13/2022 8:56:32 PM

Execute the same Get-AzSqlServerKeyVaultKey command on the secondary server:

Get-AzSqlServerKeyVaultKey -ServerName <logicalServerName> -ResourceGroupName <SQLDatabaseResourceGroupName>

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

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

Use the Add-AzKeyVaultKey command to add a new key to the key vault.

# add a new key to Key Vault
Add-AzKeyVaultKey -VaultName <keyVaultName> -Name <keyVaultKeyName> -Destination <hardwareOrSoftware>

For Azure SQL Database, use:


# add the new key from Key Vault to the server
Add-AzSqlServerKeyVaultKey -KeyId <keyVaultKeyId> -ServerName <logicalServerName> -ResourceGroup <SQLDatabaseResourceGroupName>
  
# set the key as the TDE protector for all resources under the server
Set-AzSqlServerTransparentDataEncryptionProtector -Type AzureKeyVault -KeyId <keyVaultKeyId> `
   -ServerName <logicalServerName> -ResourceGroup <SQLDatabaseResourceGroupName>

For Azure SQL Managed Instance, use:

# add the new key from Key Vault to the managed instance
Add-AzSqlInstanceKeyVaultKey -KeyId <keyVaultKeyId> -InstanceName <ManagedInstanceName> -ResourceGroup <ManagedInstanceResourceGroupName>
  
# set the key as the TDE protector for all resources under the managed instance
Set-AzSqlInstanceTransparentDataEncryptionProtector -Type AzureKeyVault -KeyId <keyVaultKeyId> `
   -InstanceName <ManagedInstanceName> -ResourceGroup <ManagedInstanceResourceGroupName>

Use the az keyvault key create command to add a new key to the key vault.

# add a new key to Key Vault
az keyvault key create --name <keyVaultKeyName> --vault-name <keyVaultName> --protection <Software>

For Azure SQL Database, use:

# add the new key from Key Vault to the server
az sql server key create --kid <keyVaultKeyId> --resource-group <SQLDatabaseResourceGroupName> --server <logicalServerName>

# set the key as the TDE protector for all resources under the server
az sql server tde-key set --server-key-type AzureKeyVault --kid <keyVaultKeyId> --resource-group <SQLDatabaseResourceGroupName> --server <logicalServerName>

For Azure SQL Managed Instance, use:

# add the new key from Key Vault to the managed instance
az sql mi key create --kid <keyVaultKeyId> --resource-group <Managed InstanceResourceGroupName> --managed-instance <ManagedInstanceName>

# set the key as the TDE protector for all resources under the managed instance
az sql mi tde-key set --server-key-type AzureKeyVault --kid <keyVaultKeyId> --resource-group <ManagedInstanceResourceGroupName> --managed-instance <ManagedInstanceName>

Switch TDE protector mode

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

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

Azure SQL Database

To switch the TDE protector from Microsoft-managed to BYOK mode, use the Set-AzSqlServerTransparentDataEncryptionProtector command.

Set-AzSqlServerTransparentDataEncryptionProtector -Type AzureKeyVault `
     -KeyId <keyVaultKeyId> -ServerName <logicalServerName> -ResourceGroup <SQLDatabaseResourceGroupName>

To switch the TDE protector from BYOK mode to Microsoft-managed, use the Set-AzSqlServerTransparentDataEncryptionProtector command.

Set-AzSqlServerTransparentDataEncryptionProtector -Type ServiceManaged `
     -ServerName <logicalServerName> -ResourceGroup <SQLDatabaseResourceGroupName>

Azure SQL Managed Instance

To switch the TDE protector from Microsoft-managed to BYOK mode, use the Set-AzSqlInstanceTransparentDataEncryptionProtector command.

Set-AzSqlServerTransparentDataEncryptionProtector -Type AzureKeyVault `
     -KeyId <keyVaultKeyId> <ManagedInstanceName> -ResourceGroup <ManagedInstanceResourceGroupName>

To switch the TDE protector from BYOK mode to Microsoft-managed, use the Set-AzSqlInstanceTransparentDataEncryptionProtector command.

Set-AzSqlServerTransparentDataEncryptionProtector -Type ServiceManaged `
     -InstanceName <ManagedInstanceName> -ResourceGroup <ManagedInstanceResourceGroupName>e>

Azure SQL Database

The following examples use az sql server tde-key set.

To switch the TDE protector from Microsoft-managed to BYOK mode:

az sql server tde-key set --server-key-type AzureKeyVault --kid <keyVaultKeyId> --resource-group <SQLDatabaseResourceGroupName> --server <logicalServerName>

To switch the TDE protector from BYOK mode to Microsoft-managed:

az sql server tde-key set --server-key-type ServiceManaged --resource-group <SQLDatabaseResourceGroupName> --server <logicalServerName>

Azure SQL Managed Instance

The following examples use az sql mi tde-key set.

To switch the TDE protector from Microsoft-managed to BYOK mode:

az sql mi tde-key set --server-key-type AzureKeyVault --kid <keyVaultKeyId> --resource-group <ManagedInstanceResourceGroupName> --managed-instance <ManagedInstanceName>

To switch the TDE protector from BYOK mode to Microsoft-managed:

az sql mi tde-key set --server-key-type ServiceManaged --resource-group <ManagedInstanceResourceGroupName> --managed-instance <ManagedInstanceName>

If there's a security risk, learn how to remove a potentially compromised TDE protector: Remove a potentially compromised key.
Get started with Azure Key Vault integration and Bring Your Own Key support for TDE: Turn on TDE using your own key from Key Vault using PowerShell.

Rotate the Transparent data encryption (TDE) protector

Important considerations when rotating the TDE protector

Prerequisites

Automatic key rotation

Automatic key rotation at the database level

Automatic key rotation for geo-replication configurations

Use different keys for each server

Manual key rotation

Switch TDE protector mode

Related content

Additional resources