Rotate the Transparent Data Encryption protector in Azure Synapse Analytics

Applies to: Azure Synapse Analytics dedicated SQL pools (formerly SQL DW)

This article describes key rotation for a SQL server that uses a TDE protector from Azure Key Vault. Rotating the logical TDE protector for a server means switching to a new supported key that protects the databases on the server. Depending on the configuration, the TDE protector can be backed by a supported asymmetric (RSA) or symmetric (AES) key stored in Azure Key Vault or Azure Key Vault Managed HSM. Key rotation is an online operation and should only take a few seconds to complete, because this operation 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

• Retain old key versions for at least the database backup retention period. Restoring an older backup requires the protector that encrypted it.
• Retain old keys even if you switch back to a service-managed protector.
• A paused dedicated SQL pool must be resumed before rotation.
• The combined key vault name and key name can't exceed 94 characters.

Prerequisites

• This article assumes that you're already using a key from Azure Key Vault as the TDE protector for Azure SQL Database or Azure Synapse Analytics. For more information, see Customer-managed TDE for Azure Synapse Analytics.
• You must have Azure PowerShell installed and running.

Automatic key rotation

Enable automatic rotation for the TDE protector when you configure the TDE protector for the server or the database. You can enable automatic rotation from the Azure portal or by using the following PowerShell or Azure CLI commands. When you enable automatic rotation, the server or database continuously checks the key vault for new versions of the key used as the TDE protector. If the server or database detects a new version of the key, it automatically rotates the TDE protector to the latest key version within 24 hours.

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

Azure portal

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 use as the TDE protector.
3. Select the Auto-rotate key checkbox.
4. Select Save.

PowerShell

For Az PowerShell module installation instructions, see Install Azure PowerShell.

To enable automatic rotation for the TDE protector by using PowerShell, see the following script. You can retrieve the <keyVaultKeyId> from Azure Key Vault.

Use Set-AzSqlServerTransparentDataEncryptionProtector:

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

Azure CLI

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

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

Use az sql server tde-key set:

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

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. You can also use the Azure portal to manually rotate keys.

When you manually rotate keys and create a new key version in Key Vault (either manually or through an automatic key rotation policy), you must manually set that key version as the TDE protector on the server.

Azure portal

1. Open Transparent data encryption on the SQL server.
2. Select Customer-managed key.
3. Select the Customer-managed key option and select the key vault and key to use as the new TDE protector.
4. Select Save.

PowerShell

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

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

Use the following commands:

• Add-AzSqlServerKeyVaultKey
• Set-AzSqlServerTransparentDataEncryptionProtector

# add the new key from Azure 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>

Azure CLI

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

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

Use the following commands:

• az sql server key create
• az sql server tde-key set

# add the new key from Azure 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>

Switch TDE protector mode

Azure portal

Use 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 use as the TDE protector.
4. Select Save.

PowerShell

• 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 CLI

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>
  

Related content

• Customer-managed TDE for Azure Synapse Analytics
• Remove a Transparent Data Encryption protector
• Azure Key Vault key rotation