Edit

Remove a Transparent Data Encryption (TDE) protector using PowerShell

  • Article

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

This article describes how to respond to a potentially compromised TDE protect for Azure SQL Database or Azure Synapse Analytics that is using TDE with customer-managed keys in Azure Key Vault - Bring Your Own Key (BYOK) support. To learn more about BYOK support for TDE, see the overview page.

Caution

The procedures outlined in this article should only be done in extreme cases or in test environments. Review the steps carefully, as deleting actively used TDE protectors from Azure Key Vault will result in database becoming unavailable.

If a key is ever suspected to be compromised, such that a service or user had unauthorized access to the key, it's best to delete the key.

Keep in mind that once the TDE protector is deleted in Key Vault, in up to 10 minutes, all encrypted databases will start denying all connections with the corresponding error message and change its state to Inaccessible.

This how-to guide goes over the approach to render databases inaccessible after a compromised incident response.

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.

Prerequisites

  • You must have an Azure subscription and be an administrator on that subscription.
  • You must have Azure PowerShell installed and running.
  • This how-to guide assumes that you are already using a key from Azure Key Vault as the TDE protector for an Azure SQL Database or Azure Synapse. See Transparent Data Encryption with BYOK Support to learn more.

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

Check TDE Protector thumbprints

The following steps outline how to check the TDE Protector thumbprints still in use by Virtual Log Files (VLF) of a given database. The thumbprint of the current TDE protector of the database, and the database ID can be found by running:

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

The following query returns the VLFs and the TDE Protector respective thumbprints in use. Each different thumbprint refers to different key in Azure Key Vault (AKV):

SELECT * FROM sys.dm_db_log_info (database_id)

Alternatively, you can use PowerShell or the Azure CLI:

The PowerShell command Get-AzureRmSqlServerKeyVaultKey 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 AKV. Only keys no longer used by the database can be safely deleted from Azure Key Vault.

Keep encrypted resources accessible

  1. Create a new key in Key Vault. Make sure this new key is created 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 using the Add-AzSqlServerKeyVaultKey and Set-AzSqlServerTransparentDataEncryptionProtector cmdlets and update it as the server's new TDE protector.

    # add the key from 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. Make sure the server and any replicas have updated to the new TDE protector 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 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 Key Vault using the Remove-AzKeyVaultKey cmdlet.

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

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

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

Make encrypted resources inaccessible

  1. Drop the databases that are being encrypted by the potentially compromised key.

    The database and log files are automatically backed up, so a point-in-time restore of the database can be done at any point (as long as you provide the key). The databases must be dropped before deletion of an active TDE protector to prevent potential data loss of up to 10 minutes of the most recent transactions.

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

  3. Remove the potentially compromised key from Key Vault.

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.

Related content