Bring Your Own Key to Azure SQL Database TDE New UI

In a recent post I wrote about a new functionality for Azure SQL Database’s TDE feature: Bring Your Own Key. At the time the only way to bring your own key was through PowerShell and T-SQL. Thankfully, that has changed. A recent update added a UI for managing your TDE keys using Azure Key Vault. Check out the quick, easy details below on setup and configuration.

Before you start, you’ll need to have a logical SQL Server and an Azure Key Vault provisioned. I’ll be using the built-in sample database, AdventureWorksLT, since we don’t really need any data for our exercise.

You can see the encryption_state = 3 for this database, meaning the database is encrypted and the encryptor_thumbprint is 0x55E756B46BD747E7A44CA43878E0B5482B6AA28E.

SELECT
	db_name(database_id) AS database_name,
	*
FROM sys.dm_database_encryption_keys

In the Azure Portal navigate to your SQL Server. On the navigation go to Transparent Data Encryption in the Security section. Then change the toggle next to Use your own key to Yes.

This exposes a series of options. If you have an existing key and want to simply enter the key identifier in the format of https://{keyvaultname}.vault.azure.net/keys/{keyname}/{versionguid} then select Enter key identifier. Otherwise, stay on Select a key. After selecting your Key Vault and either an existing key or generating a new key, click Save.

Upon completion you can check the encryptor_thumbprint has changed, previous it was 0x55E756B46BD747E7A44CA43878E0B5482B6AA28E and now it is 0xDA74B3129590A970EE1C8A66581450C80AD050D4.

Checking in Key Vault will also show the TDE key that was created in the previous step (unless you used an existing key then there will obviously be no new keys in Key Vault).

Finally, reverting the SQL DB setting back to No will also revert the encryptor_thumbprint back to the original, service managed, key.

Troubleshooting

The most common error message that will be encountered is no doubt the following:

Failed to save Transparent Data Encryption settings for server: {serverName}. Error message: The provided Key Vault uri ‘https://bradschacht-keyvault.vault.azure.net/keys/SQLTDE/261a05ca90e946f597c60d84a623f862’ is not valid. Please ensure the vault has the right Recovery Level other than ‘Purgeable’.

This same message is seen in the troubleshooting section of my post on BYOK configuration using PowerShell. Currently the Soft Delete Enabled property is not exposed in the Azure Portal, it is only accessible through PowerShell. So break out a couple quick commands and get that setting changed to True using the following two commands (after logging in with Azure PowerShell of course).

($resource = Get-AzureRmResource -ResourceId (Get-AzureRmKeyVault -VaultName "YourKeyVaultNameHere").ResourceId).Properties | Add-Member -MemberType "NoteProperty" -Name "enableSoftDelete" -Value "true"
Set-AzureRmResource -resourceid $resource.ResourceId -Properties $resource.Properties

Optionally, you can run Get-AzureRmKeyVault to see the properties and verify that Soft Delete Enabled is set to True.

$vault = Get-AzureRmKeyVault -VaultName "YourKeyVaultNameHere"
$vault.EnableSoftDelete

Bradley Schacht

Bradley Schacht is a Cloud Solution Architect on the state and local government team with Microsoft based in Jacksonville, FL. He has co-authored 3 SQL Server books including "SQL Server 2014 Professional Administration". As a former consultant and trainer, he uses his experience on many parts of the Microsoft BI and data platform to help customers deliver the best possible solutions. Bradley frequently presents at community events around the country. He is a contributor to sites such as SQLServerCentral.com and an active member of the Jacksonville SQL Server User Group (JSSUG).

You may also like...

Leave a Reply