By: Mohammed Moinudheen | Updated: 2021-04-09 | Comments | Related: > Azure SQL Database
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.
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.
You will get a confirmation to delete, click Yes.
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.
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.
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.
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.
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.
On connecting to the database using SSMS, you will get this error.
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.
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.
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.
You can go back to the Azure SQL database to check the status and the database will be online after a few minutes.
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.
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.
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.
After few minutes, you can go back to the Azure SQL database to check the status and the database will be 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
About the author
Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2021-04-09
