By: Mohammed Moinudheen | Updated: 2020-11-18 | Comments (2) | Related: > Azure SQL Database
Problem
I see that new Azure SQL DB deployments have Transparent Data Encryption (TDE) enabled by default. On checking the configuration, we can see that Azure service managed keys are used for this purpose. Is there any way we can use our own customer managed keys for this purpose?
Solution
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 purpose. 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 this tip, we will go through the steps for setting up a customer managed key. 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.
Azure SQL DB TDE using Service Managed Key
By using the service managed key, Azure will take care of key management and rotation seamlessly with no user intervention required. However, this is something some customers with sensitive or business critical data may not be comfortable with as both data and key used for encryption would be handled by Azure internally.
Azure SQL DB TDE using Customer Managed Key
With the customer managed key also known as Bring Your Own Key (BYOK), we as customers, have total control over the key used for encryption purposes. Some businesses have very strict policies around handling encryption due to criticality of the data and this feature would help to overcome any trust issues that may arise due to hosting data in the cloud. At the same time, this adds additional responsibility on us if we use this feature as we would need to take care of the extra step of key management based on the organization policies and requirements. We would need to take care of tasks like backing up the keys, rotating the keys periodically, auditing the key usage and reporting. If by any chance, the Azure SQL database loses its contact to the Azure key vault, the Azure SQL database becomes inaccessible leading to an outage.
Configuring the TDE protector using Service Managed Key or Customer Managed Key
This configuration setting is done on the logical server level for the Azure SQL database. All of the databases inherit this property from the server level. You can see the setting as shown on the Azure portal.
As you can see, transparent data encryption is enabled using the service managed key. With this, Azure will automatically manage key generation for encryption and also manage key rotations. There is very little user intervention required.
Before Configuring the TDE Protector using Customer Managed Key
As a first step, for using a customer managed key, you need to set up an Azure Key Vault if you don't have one already. For creating an Azure key vault, you can search for the 'Key vaults' service as shown. To learn more about Azure key vault, you can refer to this tip.
Once done, you have the option to create a new one by clicking on the +Add option.
Create new Azure Key Vault
Once you click on the +Add option to create a new key vault, you will need to input the values as shown.
As you can see the soft-delete option is enabled by default. This feature will help us to recover objects especially on accidental deletion. To learn more related to the soft-delete option, you can refer to this tip. Enter the details as per your requirement and click on 'Review + create' to complete the configuration. Once this is done, you will see confirmation of successful validation.
Click on 'Create', which will complete the deployment. You will see confirmation of a successful deployment on the notifications sections as shown.
Configuring the TDE protector using Customer Managed Key
For creating a customer managed key, click on the option 'Customer-managed key' on the server blade as shown and then select 'Change key'.
In the next section, you will need to create a new vault or select an existing one. In this demo, we will use the existing key vault that we created in the previous step. Also, make sure to create a new key that we can use for TDE as shown.
Create a Key
Once you click on the 'Create new' option, you will see this screen. You have the option to either generate a new key, import a key or restore one from backup. In this demo, we will use the 'Generate' option. Next, just enter a name for the key as per your requirement. You may leave the rest of the options in this screen to the default settings for this demo and click on 'Create'. In production, you may need to review the settings and make the selections as per your organization policies.
On creation, you will see a notification as shown below.
In the next step, make sure to select the key version as shown. Once done, click on the 'Select' option.
After the key selection is done, you can see the key is available to be used for TDE. You can review and click on the 'Save' option.
Once the setting is saved, the TDE protector will be the custom managed key which we just created. As this setting is at the logical server level, it will affect all the Azure SQL databases on that server.
Checking Status of TDE on the Azure SQL Database
You can check the status of TDE on the Azure SQL database similar to how you checked for the logical server.
You can see that the transparent data encryption is enabled and to the Azure SQL database it is kind of seamless. It doesn't really matter how the TDE protector is configured, whether it is using a service-managed or customer-managed key. However, if it is using a customer-managed key like this, it is safer to have few policies in place to safeguard these custom keys. Any issue with access to the keys, will make the Azure SQL database inaccessible.
Steps to safe guard Azure SQL DB while using Customer Managed TDE Protector
Make sure soft-delete option is enabled on the Azure key vault so the keys are protected.
Ensure to back up the keys that are used. You can search for 'Key vaults' as shown earlier in the tip and go to the Azure key vault that we created and click on the 'keys' settings.
Once you click on the key, in this case 'CustomTDEKey', you will see this screen. Click on 'Download Backup'.
Once done, you will see this warning. Make sure to save this backup safely.
These are just few steps to safe guard. However, we need to keep in mind that all of this needs to be managed by the user and this adds an additional overhead. Another consequence of any misstep with this customer managed key is loss of database access which will not be acceptable.
Next Steps
- In this tip, you learnt about Transparent Data Encryption (TDE)
- In this tip, you learnt regarding TDE using service-managed key
- In this tip, you learnt regarding TDE using customer-managed key
- You learnt regarding Azure Key Vaults
- You could try the demo regarding configuring TDE using customer-managed key
- You also came across few steps on safe guarding the custom keys created
- 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: 2020-11-18
