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.

	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}{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.


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 ‘’ 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"

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 and an active member of the Jacksonville SQL Server User Group (JSSUG).

You may also like...

1 Response

  1. January 24, 2020

    […] has been in the singleton database version of Azure SQL Database for a while longer and you can read about how to use that here. The experience between the two is very similar, but let’s focus on the Managed Instance side […]

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.