Recover Deleted TDE Key for Azure SQL Database


By:   |   Updated: 2021-04-09   |   Comments   |   Related: > Azure


Problem

I configured my Azure SQL database to use Transparent Data Encryption (TDE) using a customer managed key also known as Bring Your Own Key (BYOK). I noticed that I am having issues accessing the customer managed key in the Azure key vault. Would there be any impact to my Azure SQL DB in that case?

Solution

In a previous tip, we described the steps to use a customer managed key for TDE. As we know, TDE helps to encrypt data at rest. To learn more about TDE, you can refer this tip by Ray Barley.

Internally, for TDE, a symmetric key called the Database Encryption Key (DEK) is used for encryption purposes. This DEK is protected by the TDE protector which is either a service managed key or the customer managed key in Azure Key Vault. In the case of a service managed key, we need not worry about availability or any sort of issues with the key as this is all internally sorted by Azure. However, in the case of customer managed key, we need to ensure that the customer managed key in the key vault is available all the time for the database. If not, the database will become inaccessible leading to an outage.

In this tip, we will go through the steps to simulate a scenario where access is lost to the customer managed key in the key vault, the impact it causes the Azure SQL DB and the steps performed to restore access. This tip assumes that you already have resources in your Azure environment and you are familiar with the Azure portal. If you are new to Azure, you can subscribe to a trial account here, which will give you access to Azure services for a year and a credit of USD $200. You can refer to this earlier tip to set up a demo database using a customer managed key.

Configure Azure SQL DB TDE to use Customer Managed Key

In order to try out this demo, refer to this tip to configure Azure SQL DB to use a customer managed key.

Once you configured the customer managed key, you will see this screen on the logical SQL Server blade. All the databases will inherit this property as this setting is at the logical server level.

azure transparent data encyrption

Delete customer managed key from Key vault to see the impact on the Azure SQL database

If you refer to the earlier tip, you will know that the customer managed key will be present in Azure Key vault. Steps on how to create the new key vault and the customer managed key are described in detail in this earlier tip.

In this section, we will delete the key which we are using for TDE from the key vault. Click on the Delete option as shown.

delete azure key

You will get a confirmation to delete, click Yes.

confirm delete

Change in status on logical SQL Server on deleting the customer managed key from Key Vault

Within a few minutes, you will notice the change in status on the logical SQL Server.

azure key access lost

If you check further on the TDE blade on the overview section of the logical SQL Server, you will see additional details as shown. You will see three steps to mitigate this issue.

azure sql db database issue with missing key

On Step 1, there is a link to a troubleshooting guide which describes the possible reasons for this error. However, we know the reason for this error is because we deleted the customer managed key from Key vault.

On Step 2, there are few options to either retry the existing key or select a backup key. We will try retrying the existing key option as we have used only one key as a customer managed key for TDE in this demo.

retry existing key

Make the selections as shown and click on the Revalidate key option. Once done, the revalidation process initiates and on the notifications tab, you will see this error.

key validation error

From the error message you know that this is due to the missing key from the key vault. You can validate the reason for this failure from the documentation link in step 1.

Change in status on Azure SQL DB on deleting the customer managed key from Key Vault

Click on the database overview, to check the database status. You can see the status is Inaccessible.

azure sql db inaccesible

On connecting to the database using SSMS, you will get this error.

login error message

From the error message, it doesn’t say the reason for the failure other than a logon failure message. It appears to be a generic error message.

Option 1 - Steps to restore database access - Retrieve the deleted key from Key Vault using PowerShell

You can check if the soft delete option is enabled on Key Vault by running the below command. Replace the names with your vault names.

Get-AzKeyVault -VaultName "CustomManagedKeyVault" 

From the output, you can see the soft delete option is enabled.

powershell command

In the next step, we can run this command to review the list of deleted keys.

Get-AzKeyVaultKey -VaultName CustomManagedKeyVault -InRemovedState

From the output, you can see the objects that were deleted. You can see the key will be deleted after 90 days.

powershell command

We can run this command to recover the key. Make sure to replace the correct parameter values.

Undo-AzKeyVaultKeyRemoval -VaultName CustomManagedKeyVault -Name CustomTDEKey

Once this command is run, you will see that the deleted key is retrieved on Azure Key Vault.

key has been retrieved

You can go back to the Azure SQL database to check the status and the database will be online after a few minutes.

database is online

Option 2 - Steps to restore database access - Retrieve the deleted key from Key Vault using Portal

When you click on the key vault option as shown, you will see this window.

manage deleted keys

As we saw earlier, the soft delete option is enabled, we will be able to recover the key before it gets purged permanently. Click on the Manage deleted keys option.

manage deleted keys

Here, you can see the key we used for TDE, select this key and click on the Recover option. You will see that the recovery is successful on the notifications tab.

And once you refresh the Key vault, you will be able to confirm the deleted key is recovered.

key has been restored

After few minutes, you can go back to the Azure SQL database to check the status and the database will be online.

database is online
Next Steps
  • In this tip, you learned about Transparent data encryption (TDE)
  • In this tip, you learned regarding TDE using customer-managed key
  • You learned about Azure Key Vaults
  • You could try the demo by configuring TDE to use customer-managed key
  • You can delete the customer managed key and try out the options to recover the deleted key
  • To learn more about Microsoft Azure, refer this link on MSSQLTips


Last Updated: 2021-04-09


get scripts

next tip button



About the author
MSSQLTips author Mohammed Moinudheen Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

View all my tips
Related Resources



Comments For This Article





download





Recommended Reading

Adding Users to Azure SQL Databases

Connect to On-premises Data in Azure Data Factory with the Self-hosted Integration Runtime - Part 1

Transfer Files from SharePoint To Blob Storage with Azure Logic Apps

Reading and Writing data in Azure Data Lake Storage Gen 2 with Azure Databricks

Create a Python Wheel File to Package and Distribute Custom Code














get free sql tips
agree to terms