Remove a Transparent Data Encryption protector in Azure Synapse Analytics

Tip

Microsoft Fabric Data Warehouse is an enterprise scale relational warehouse on a data lake foundation, with a future-ready architecture, built-in AI, and new features. If you're new to data warehousing, start with Fabric Data Warehouse. Existing dedicated SQL pool workloads can upgrade to Fabric to access new capabilities across data science, real-time analytics, and reporting.

• Start a Fabric free trial.
• Migration Assistant for Fabric Data Warehouse.

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

Use this procedure when a customer-managed TDE protector might be compromised. Rotate to a new protector before deleting or disabling the old key so that the dedicated SQL pools remain accessible.

Caution

Deleting or disabling an active TDE protector makes every dedicated SQL pool that depends on it inaccessible. Review the incident-response plan and backup-retention requirements before removing a key.

Note

This article covers standalone dedicated SQL pools (formerly SQL DW). For dedicated SQL pools in a Synapse workspace, see Encryption for Azure Synapse Analytics workspaces.

Deleting a key doesn't invalidate copies of that key that were previously backed up or restored to another key vault. Protect and inventory every copy as part of the incident response.

Prerequisites

• You must have an Azure subscription and be an administrator on that subscription.
• You must have Azure PowerShell installed and running.
• You must be able to manage the logical server, key vault permissions, and keys.
• Install Azure PowerShell or the Azure CLI.
  • For Az module installation instructions, see Install Azure PowerShell. Use the new Azure PowerShell Az module.
  • For installation, see Install Azure CLI.
• Review Customer-managed TDE for Azure Synapse Analytics and Rotate the TDE protector.
• This article assumes that you're already using a key from Azure Key Vault as the TDE protector for Azure Synapse. See Customer-managed TDE for Azure Synapse Analytics to learn more.

Check TDE protector thumbprints

The following steps outline how to check the TDE protector thumbprints that Virtual Log Files (VLF) of a given database still use.

Run the following query to find the thumbprint of the current TDE protector for the database and the database ID:

SELECT [database_id],
       [encryption_state],
       [encryptor_type], /*asymmetric key means Azure Key Vault, certificate means service-managed keys*/
       [encryptor_thumbprint]
 FROM [sys].[dm_database_encryption_keys]

Run the following query to return the VLFs and the TDE protector thumbprints in use. Each different thumbprint refers to a different key in Azure Key Vault:

SELECT * FROM sys.dm_db_log_info (database_id)

Alternatively, you can use PowerShell or Azure CLI:

• The PowerShell command Get-AzSqlServerKeyVaultKey provides the thumbprint of the TDE protector used in the query, so you can see which keys to keep and which keys to delete in Azure Key Vault. Only keys that the database no longer uses can be safely deleted from Azure Key Vault.

• The Azure CLI command az sql server key show provides the thumbprint of the TDE protector used in the query, so you can see which keys to keep and which keys to delete in Azure Key Vault. Only keys that the database no longer uses can be safely deleted from Azure Key Vault.

Keep encrypted resources accessible

PowerShell

1. Create a new key in Azure Key Vault. Ensure you create this new key in a separate key vault from the potentially compromised TDE protector, since access control is provisioned on a vault level.

2. Add the new key to the server by using the Add-AzSqlServerKeyVaultKey and Set-AzSqlServerTransparentDataEncryptionProtector cmdlets, and update it as the server's new TDE protector.

   # add the key from Azure Key Vault to the server  
   Add-AzSqlServerKeyVaultKey -ResourceGroupName <SQLDatabaseResourceGroupName> -ServerName <LogicalServerName> -KeyId <KeyVaultKeyId>
   
   # set the key as the TDE protector for all resources under the server
   Set-AzSqlServerTransparentDataEncryptionProtector -ResourceGroupName <SQLDatabaseResourceGroupName> `
       -ServerName <LogicalServerName> -Type AzureKeyVault -KeyId <KeyVaultKeyId>
   

3. Ensure the server and any replicas update to the new TDE protector by using the Get-AzSqlServerTransparentDataEncryptionProtector cmdlet.

   Note

   It might take a few minutes for the new TDE protector to propagate to all databases and secondary databases under the server.

   Get-AzSqlServerTransparentDataEncryptionProtector -ServerName <LogicalServerName> -ResourceGroupName <SQLDatabaseResourceGroupName>
   

4. Take a backup of the new key in Azure Key Vault.

   # -OutputFile parameter is optional; if removed, a file name is automatically generated.
   Backup-AzKeyVaultKey -VaultName <KeyVaultName> -Name <KeyVaultKeyName> -OutputFile <DesiredBackupFilePath>
   

5. Delete the compromised key from Azure Key Vault by using the Remove-AzKeyVaultKey cmdlet.

   Remove-AzKeyVaultKey -VaultName <KeyVaultName> -Name <KeyVaultKeyName>
   

6. To restore a key to Azure Key Vault in the future, use the Restore-AzKeyVaultKey cmdlet.

   Restore-AzKeyVaultKey -VaultName <KeyVaultName> -InputFile <BackupFilePath>
   

Azure CLI

For command reference, see Azure CLI keyvault.

1. Create a new key in Azure Key Vault. Ensure you create this new key in a separate key vault from the potentially compromised TDE protector, since access control is provisioned on a vault level.

2. Add the new key to the server and update it as the new TDE protector of the server.

   # add the 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>
   

3. Ensure the server and any replicas update to the new TDE protector.

   Note

   It might take a few minutes for the new TDE protector to propagate to all databases and secondary databases under the server.

   az sql server tde-key show --resource-group <SQLDatabaseResourceGroupName> --server <LogicalServerName>
   

4. Take a backup of the new key in Azure Key Vault.

   # --file parameter is optional; if removed, a file name is automatically generated.
   az keyvault key backup --file <DesiredBackupFilePath> --name <KeyVaultKeyName> --vault-name <KeyVaultName>
   

5. Delete the compromised key from Azure Key Vault.

   az keyvault key delete --name <KeyVaultKeyName> --vault-name <KeyVaultName>
   

6. Restore a key to Azure Key Vault in the future.

   az keyvault key restore --file <BackupFilePath> --vault-name <KeyVaultName>
   

Make encrypted resources inaccessible

1. Drop the databases that use the potentially compromised key for encryption.

   The system automatically backs up the database and log files, so you can perform a point-in-time restore of the database at any point (as long as you provide the key). Drop the databases before you delete an active TDE protector to avoid potential data loss of up to 10 minutes of the most recent transactions.

2. Back up the key material of the TDE protector in Azure Key Vault.

3. Remove the potentially compromised key from Azure Key Vault.

Note

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

Related content

• Customer-managed TDE for Azure Synapse Analytics
• Rotate the TDE protector
• Azure Key Vault backup security considerations