How to Configure a TDE database with AlwaysOn using the Azure Key Vault in SQL Server 2016
How to Configure a TDE database with AlwaysOn using the Azure Key Vault in SQL Server 2016
xEkQBbc= Secret The SECRET here is your AAD Client ID (with the hyphens removed) and your AAD Client Secret concatenated together 2db602bd4x4x43228xxfd128c143c8a9FZCz
XY3K8RpZoK12MxF/WFxxAw6aOxxPU2ixxEkQBbc= Key Encryption
Key Name Key Encryption Key Name (KEK) az-kek-sql-001
STEP 2: Setup Credentials Create a credential from your Azure Active Directory Client ID and Secret that you can use to grant an SQL Server account access to your Azure key vault IDENTITY here is the name of your Azure key vault. SECRET here is your AAD Client ID (with the hyphens removed) and your AAD Client Secret concatenated together 123456 USE master; CREATE CREDENTIAL sysadmin_ekm_cred WITH IDENTITY = 'hq-kv-sql-0001', SECRET ='2db602bd4x4x23452xxfd128c143c8a9FZCzXY3K8RpZoK12MxF/WFxxAw6aOxxPU2ixxEkQBbc='FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov
Add the credentials to the SQL Server administrator’s domain login. 1 ALTER LOGIN [UU/ABCDF] ADD CREDENTIAL sysadmin_ekm_cred;
STEP 3: Create asymmetric Key and SQL Login Use the EKM to open the asymmetric KEK Key Encryption Key name 12345678 USE [MASTER] -- Use the EKM to open the asymmetric KEK that was previously created in the Key Vault CREATE ASYMMETRIC KEY TDE_KEY -- Give the asymmetric KEK a name in SQL Server FROM PROVIDER AzureKeyVault_EKM_Prov WITH PROVIDER_KEY_NAME = 'hq-kek-sql-0001', -- The name of the asym-metric KEK in Azure Key Vault CREATION_DISPOSITION = OPEN_EXISTING -- To indicate
Step 4: Create SQL Server Login 12345 -- Create a SQL Server Login associated with the KEK for the Database engine -- to use whenever it loads a database encrypted by TDE CREATE LOGIN TDE_Login FROM ASYMMETRIC KEY TDE_KEY ;GO
1234567 -- Create a SQL credential for the SQL Server Database Engine to use to -- access the Key Vault EKM during database load CREATE CREDENTIAL Azure_EKM_TDE_cred WITH IDENTITY = 'hq-kv-sql-0001', SECRET = '2db602bd4x4x34562xxfd128c143c8a9FZCzXY3K8RpZoK12MxF/WFxxAw6aOxxPU2ixxEkQBbc=' FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov
123456 -- Alter the TDE Login to add this Credential for use by the Database Engine-- to access the Key Vault ALTER LOGIN TDE_Login ADD CREDENTIAL Azure_EKM_TDE_cred ;GO
12345678910 -- Create the database encryption key (DEK) that will be used for TDE. -- The DEK can be created using any SQL Server supported Algorithm -- or Key Length.-- The DEK will be protected by the Asymmetric KEK in the Key VaultUSE iServerReportingDB;GOCREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER ASYMMETRIC KEY TDE_KEY;GO
Enable TDE on SQL Database by Using Transact-SQL Connect to the master database Execute the following SQL statement to encrypt the database 12345 -- Alter the database to enable transparent data encryption.-- This uses the asymmetric KEK you imported from Azure Key Vault to wrap your DEK.ALTER DATABASE iServerReportingDB SET ENCRYPTION ON ;GO
Monitor the progress of encryption, 1234567 SELECT d.name, dek.encryption_stateFROMsys.dm_database_encryption_keys AS dekJOIN sys.databases AS dON dek.database_id = d.database_id;
STEP 5: Add Database to Always On Group On the primary, add the database to the Availability Group using ALTER AVAILABILITY GROUP … ADD DATABASE 123 USE MASTERGOALTER AVAILABILITY GROUP [AG-Name] ADD DATABASE iServerReportingDB
Backup the database (Full and Log backup) Initiate full and log backup on the primary replica using the following SQL statements: 12 BACKUP DATABASE iServerReportingDB TO DISK ='\\hqdbsp18\f$\iServerReportingDB_FULL.bak' WITH STATS=10BACKUP LOG iServerReportingDB TO DISK ='\\hqdbsp18\f$\iServerReportingDB_Log.trn' WITH STATS=10
On the secondary, Create the database iServerReportingDB Move the database backup file to a location where Always On Replica #1 can restore the file Run steps 1 through 4 from the last section Repeat the process for each Always On Replica node Restore the full database backup Restore the log database backup Join the database to the availability group using the ALTER AVAILABILITY GROUP T-SQL 1 Alter database iServerReporting SET HADR AVAILABILITY GROUP=[AG-Name]
My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.
View all posts by Prashanth Jayaram Latest posts by Prashanth Jayaram (see all) Stairway to SQL essentials - April 7, 2021 A quick overview of database audit in SQL - January 28, 2021 How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021
SQLShack
SQL Server training EspañolHow to Configure TDE database with AlwaysOn using the Azure Key Vault in SQL 2016
July 28, 2017 by Prashanth Jayaram One of the recent tasks I undertook on configuring Transparent Data encryption (TDE) using asymmetric key protection with Azure Key Vault with Always On opened a different dimension on securing data for me. Even though it seems slightly complex, if you have the key details, the steps are in fact, really straight forward. I strongly recommend going through this MSDN article on SQL Server Transparent Data Encryption TDE using Azure Key Vault in order to understand Azure Key Vault configuration and IntegrationBackground
In order to encrypt the database encryption key with an asymmetric key, use an asymmetric key that resides on an Extensible Key Management Provider. Extensible Key Management (EKM) is another new feature in SQL Server that adds the ability to store the encryption keys used for TDE on hardware, specifically designed for key security and management. Such devices are called High-Security Modules (HSM), whose vendors are referred to as EKM providers. A good Key Management Vendor should supply you with software libraries that easily add and implement Key Management in SQL Server encryption. In asymmetric encryption, two different keys are used: A public key for encrypting, and a private key for decrypting the data. This type of asymmetric encryption is referred to as Public Key Infrastructure (PKI)- or Public Key Cryptography-based encryption.Azure Key Vault
Azure Key Vault is a secure key management feature that is essential to secure and protect data in the Azure cloud. We use Azure Key Vault to encrypt keys and small secrets like passwords that use keys stored in hardware high-security modules (HSMs). For added assurance, import or generate keys in HSMs, and let Microsoft process your keys in FIPS 140-2 Level 2 validated HSMs (hardware and firmware).Introduction
SQL Server running on an Azure Virtual Machine can use an asymmetric key from the Key Vault. When running SQL Server in an Azure VM, SQL Server can use keys stored in the Azure Key Vault using EKM. This article covers the role of a DBA in setting up SQL 2016 TDE database on Azure VM with Always On, using the Azure Key Vault. It also outlines the prerequisites and required details for seamless implementation of protecting the data using an asymmetric key. Let’s proceed, with the assumption that we have the necessary details from the Azure Key Manager such as: KeyValutName Active Directory Application Client ID Active Directory Client Secret Key Encryption Key Name (KEK) Let’s also add another set of assumptions from the DBA side: Always-On Availability Group site that is already configured For example, Name Description Value ResourceGroupName Key Vault Resource Group Name az-prod-sql-001 VaultName Key Vault Name az-kv-sql-001 AADApp AAD Application Name az-aadapp-kv-001 AADObjectID Azure Active Directory Application Client ID 2db602bd-4x4x-4322-8xxf-d128c143c8a9 AADClientSecret Active Directory Client Secret FZCzXY3K8RpZoK12MxF/WFxxAw6aOxxPU2ixxEkQBbc= Secret The SECRET here is your AAD Client ID (with the hyphens removed) and your AAD Client Secret concatenated together 2db602bd4x4x43228xxfd128c143c8a9FZCz
XY3K8RpZoK12MxF/WFxxAw6aOxxPU2ixxEkQBbc= Key Encryption
Key Name Key Encryption Key Name (KEK) az-kek-sql-001
SQL Server Connector
The SQL Server Connector for Microsoft Azure Key Vault enables SQL Server encryption to use the Azure Key Vault service as an Extensible Key Management (EKM) provider to protect SQL Server encryption keys. Download the SQL Server Connector from the Microsoft Download Center, and follow the steps below to complete the installation. Browse the installation folder Click Next Click the agree terms and license agreement radio button Select the location for libraries to install Click Next and then, Finish By default, the connector installs at C:\Program Files\SQL Server Connector for Microsoft Azure Key Vault. This location can be changed during setup. (If changed, adjust the scripts below.) The Connector is the cryptographic EKM provider DLL that needs to be registered with SQL Server by using the CREATE CRYPTOGRAPHIC PROVIDER statement. The SQL Server Connector installation also allows you to optionally download sample scripts to help with SQL Server encryption.Configure SQL Server
In this section, we shall see how we could add a TDE-encrypted database to Always On Availability Group site that is already configured. Assume that we have configured the Always On Availability Group SQLAG2 that contains three replicas. In the following screenshot, iServerReportingDB is going to be configure for TDE. Let’s see the steps to configure TDE and use of EKM on Always On setup. On the Principal, Setup EKM Setup Credentials Configure TDE Add database to Always On availability group Perform full database backup Perform log backup On the Secondary, the steps are a little different from the procedure above Create the database Setup EKM Setup credentials Configure TDE Restore the database with replace option Restore the log Join the database to Always On availability group Let’s look at all of the above steps in detail and configure TDE with asymmetric key on the Availability Group using Azure Vault Check the database’s encryption configuration 1234567 SELECT d.name, dek.encryption_stateFROMsys.dm_database_encryption_keys AS dekJOIN sys.databases AS dON dek.database_id = d.database_id; This indicates that no databases are configured for TDE. Step 1: EKM Setup 12345678910111213 -- Enable advanced options.USE master; GOsp_configure 'show advanced options', 1 ;GORECONFIGURE ;GO -- Enable EKM providersp_configure 'EKM provider enabled', 1 ;GORECONFIGURE ;GO The below SQL query is used to create a cryptographic provider, using the SQL Server Connector which is an EKM provider for the Azure Key Vault. This example uses the name AzureKeyVault_EKM_Prov. Notice that the DLL location should be the taken from where you installed—this is the Connector path. 123 CREATE CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov FROM FILE = 'C:\Program Files\SQL Server Connector for Microsoft Azure Key Vault\Microsoft.AzureKeyVaultService.EKM.dll';GOSTEP 2: Setup Credentials Create a credential from your Azure Active Directory Client ID and Secret that you can use to grant an SQL Server account access to your Azure key vault IDENTITY here is the name of your Azure key vault. SECRET here is your AAD Client ID (with the hyphens removed) and your AAD Client Secret concatenated together 123456 USE master; CREATE CREDENTIAL sysadmin_ekm_cred WITH IDENTITY = 'hq-kv-sql-0001', SECRET ='2db602bd4x4x23452xxfd128c143c8a9FZCzXY3K8RpZoK12MxF/WFxxAw6aOxxPU2ixxEkQBbc='FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov
Add the credentials to the SQL Server administrator’s domain login. 1 ALTER LOGIN [UU/ABCDF] ADD CREDENTIAL sysadmin_ekm_cred;
STEP 3: Create asymmetric Key and SQL Login Use the EKM to open the asymmetric KEK Key Encryption Key name 12345678 USE [MASTER] -- Use the EKM to open the asymmetric KEK that was previously created in the Key Vault CREATE ASYMMETRIC KEY TDE_KEY -- Give the asymmetric KEK a name in SQL Server FROM PROVIDER AzureKeyVault_EKM_Prov WITH PROVIDER_KEY_NAME = 'hq-kek-sql-0001', -- The name of the asym-metric KEK in Azure Key Vault CREATION_DISPOSITION = OPEN_EXISTING -- To indicate
Step 4: Create SQL Server Login 12345 -- Create a SQL Server Login associated with the KEK for the Database engine -- to use whenever it loads a database encrypted by TDE CREATE LOGIN TDE_Login FROM ASYMMETRIC KEY TDE_KEY ;GO
1234567 -- Create a SQL credential for the SQL Server Database Engine to use to -- access the Key Vault EKM during database load CREATE CREDENTIAL Azure_EKM_TDE_cred WITH IDENTITY = 'hq-kv-sql-0001', SECRET = '2db602bd4x4x34562xxfd128c143c8a9FZCzXY3K8RpZoK12MxF/WFxxAw6aOxxPU2ixxEkQBbc=' FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov
123456 -- Alter the TDE Login to add this Credential for use by the Database Engine-- to access the Key Vault ALTER LOGIN TDE_Login ADD CREDENTIAL Azure_EKM_TDE_cred ;GO
12345678910 -- Create the database encryption key (DEK) that will be used for TDE. -- The DEK can be created using any SQL Server supported Algorithm -- or Key Length.-- The DEK will be protected by the Asymmetric KEK in the Key VaultUSE iServerReportingDB;GOCREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER ASYMMETRIC KEY TDE_KEY;GO
Enable TDE on SQL Database by Using Transact-SQL Connect to the master database Execute the following SQL statement to encrypt the database 12345 -- Alter the database to enable transparent data encryption.-- This uses the asymmetric KEK you imported from Azure Key Vault to wrap your DEK.ALTER DATABASE iServerReportingDB SET ENCRYPTION ON ;GO
Monitor the progress of encryption, 1234567 SELECT d.name, dek.encryption_stateFROMsys.dm_database_encryption_keys AS dekJOIN sys.databases AS dON dek.database_id = d.database_id;
STEP 5: Add Database to Always On Group On the primary, add the database to the Availability Group using ALTER AVAILABILITY GROUP … ADD DATABASE 123 USE MASTERGOALTER AVAILABILITY GROUP [AG-Name] ADD DATABASE iServerReportingDB
Backup the database (Full and Log backup) Initiate full and log backup on the primary replica using the following SQL statements: 12 BACKUP DATABASE iServerReportingDB TO DISK ='\\hqdbsp18\f$\iServerReportingDB_FULL.bak' WITH STATS=10BACKUP LOG iServerReportingDB TO DISK ='\\hqdbsp18\f$\iServerReportingDB_Log.trn' WITH STATS=10
On the secondary, Create the database iServerReportingDB Move the database backup file to a location where Always On Replica #1 can restore the file Run steps 1 through 4 from the last section Repeat the process for each Always On Replica node Restore the full database backup Restore the log database backup Join the database to the availability group using the ALTER AVAILABILITY GROUP T-SQL 1 Alter database iServerReporting SET HADR AVAILABILITY GROUP=[AG-Name]
The AlwaysOn Group status
The below screenshot shows that iServerReportingDB is configured successfully with EKM provider, Azure Key vault.Conclusion
In an Always-On-with-Azure-Key-Vault scenario, enabling TDE on one or more secondary replicas is indeed a tedious task. One has to get the help of Azure Key Manager to get all the required key information. This article articulates every step required to setup and configure asymmetric TDE with Always On using Azure Key Vault. If you are able to configure the setup on Primary then similar steps should be applied to Secondary, with a few steps being different (which are also covered in this article). You will be performing a backup and adding the database to Availability Group in case of the former, whereas you would restore the database and join the database to the Availability Group in the latter: the secondary nodes. Author Recent Posts Prashanth JayaramI’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.
View all posts by Prashanth Jayaram Latest posts by Prashanth Jayaram (see all) Stairway to SQL essentials - April 7, 2021 A quick overview of database audit in SQL - January 28, 2021 How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021