Part 4 – SQL Server TDE and Extensible Key Management Using Azure Key Vault

This post has been republished via RSS; it originally appeared at: SQL Server articles.

Configure SQL Server

This is Part: 4 of a 4-part blog series:

After setting up Azure Active Directory and registering the AAD Application and additionally creating an Azure Key Vault, the next step is to put it all together in SQL Server where you can create credentials (to talk to Azure Key Vault), create an asymmetric key and use that key to configure/encrypt a database with TDE.

Adrian_Rupp_0-1590520284345.png

Refer to B. Frequently Asked Questions to see a note about the minimum permission levels needed for each action in this section.

Step 1: Launch sqlcmd.exe or SQL Server Management Studio (SSMS): If you use SSMS open a query window, turn on SQLCMD mode (Menu>Query > Click SQLCMD Mode)

 

Step 2: Configure SQL Server to use EKM: Execute the following Transact-SQL script to configure the Database Engine to use an EKM provider.

 

-- Enable advanced options.  USE master;  GO  Exec sp_configure 'show advanced options', 1;  GO  RECONFIGURE;  GO  -- Enable EKM provider  Exec sp_configure 'EKM provider enabled', 1;  GO  RECONFIGURE; 

 

Step 3: Register (create) the Connector as an EKM provider with SQL Server: 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.

 

CREATE CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM   FROM FILE = 'C:\Program Files\SQL Server Connector for Microsoft Azure Key Vault\Microsoft.AzureKeyVaultService.EKM.dll';  GO [NOTE] The file path length cannot exceed 256 characters.

 

Step 4: Setup a SQL Server credential for a SQL Server login to use the key vault:?A credential must be added to each login that will be performing encryption using a key from the Key Vault. This might include: 

  • An SQL Server administrator login who will use key vault in order to setup and manage SQL Server encryption scenarios. 
  • Other SQL Server logins such as dedicated Security & Compliance Administrators who might enable Transparent Data Encryption (TDE), or other SQL Server encryption features. 

There is one-to-one mapping between credentials and logins. That is, each login must have a unique credential. 

Modify the Transact-SQL script below in the following ways: 

  • Edit the IDENTITY argument (MyAAD-EKM-AKV-DemoKeyVault) to point to your Azure Key Vault. 
  • If you're using global Azure, replace the IDENTITY argument with the name of your Azure Key Vault from Part: 2. 
  • If you're using a private Azure cloud (ex. Azure Government, Azure China, or Azure Germany), replace the IDENTITY argument with the Vault URI that is returned in Part 2, Step 6. Do not include "https://" in the Vault URI. 
  • Replace the first part of the SECRET argument with the Azure Active Directory Client ID from Part 2. In this example, the Client ID is 9A57CBC54C4C40E2B517EA677E0EFA00. 

 

[IMPORTANT]  You must remove the hyphens from the App (Client) ID. Complete the second part of the SECRET argument with Client Secret from Part: 2. In this example the Client Secret from Part: 2 is (Example:?08:k?[:XEZFxcwIPvVVZhTjHWXm7w1?m). The final string for the SECRET argument will be a long sequence of letters and numbers, with no hyphens. USE master;  CREATE CREDENTIAL sysadmin_ekm_cred   WITH IDENTITY    = 'ConstosoKeyVaultKeyVault',  -- for public Azure -- WITH IDENTITY = 'ContosoEKMKeyVault.vault.usgovcloudapi.net', -- for Azure Government -- WITH IDENTITY = 'ContosoEKMKeyVault.vault.azure.cn',-- for Azure China -- WITH IDENTITY = 'ContosoEKMKeyVault.vault.microsoftazure.de', -- for Azure Germany          --<----Application (Client) ID ---><--AAD App (Client) ID Secret--> --SECRET = '9A57CBC54C4C40E2B517EA677E0EFA0008:k?[:XEZFxcwIPvVVZhTjHWXm7w1?m'   SECRET = '<AppID GUID with no dashes from AAD><Client Secret From AAD>' FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM; 

 

Step 5: Add Credential to your Windows Login

 

-- Add the credential to the SQL Server administrator's domain login   ALTER LOGIN [<domain>\<login>]  ADD CREDENTIAL sysadmin_ekm_cred; 

 

Step 6: Open your Azure Key Vault key in SQL Server: Whether you created a new key, or imported an asymmetric key as described in Part:PS2 or Part:AP3, you will need to open the key. Open the key by providing your key name in the following  Transact-SQL script.

  • Replace EKMSampleASYKey with the name you'd like the key to have in SQL Server
  • Replace ConstosoKeyVaultRSAKey with the name of your key in Azure Key Vault.

 

CREATE ASYMMETRIC KEY EKMSampleASYKey   FROM PROVIDER [AzureKeyVault_EKM]  WITH PROVIDER_KEY_NAME = 'ConstosoKeyVaultRSAKey',  CREATION_DISPOSITION = OPEN_EXISTING; 

 

Step 7: Create a new Login from ASYMMETRIC KEY in SQL Server: The new login can now be created using the asymmetric key creates in step 6.

 

--Create a Login that will associate the asymmetric key to this login CREATE LOGIN TDE_Login FROM ASYMMETRIC KEY EKMSampleASYKey;

 

Step 8: Create a new Login from ASYMMETRIC KEY in SQL Server: Drop the credential mapping from Step 5 so the credential can be mapped to the new login.

 

--Now drop the credential mapping from the original association ALTER LOGIN [<domain>\<login>] DROP CREDENTIAL sysadmin_ekm_cred;

 

Step 9: Alter the new Login: Alter the new Login and map the EKM credential to the new login.

 

--Now drop the credential mapping from the original association ALTER LOGIN TDE_Login ADD CREDENTIAL sysadmin_ekm_cred;

 

Step 10: Create Test Database: Create a test database that will be encrypted with the Azure Key Vault (Key). (Or use an existing database - make sure you have a good/current unencrypted backup before turning on TDE)

 

--Create a test db that will be encrypted with the Azure KeyVault (Key) CREATE DATABASE TestTDE

 

Step 11: Create a database encryption Key Create an ENCRYPTION KEY using the ASYMMETRIC KEY (EKMSampleASYKey)

 

--Create an ENCRYPTION KEY using the ASYMMETRIC KEY (EKMSampleASYKey) CREATE DATABASE ENCRYPTION KEY   WITH ALGORITHM = AES_256   ENCRYPTION BY SERVER ASYMMETRIC KEY EKMSampleASYKey; 

 

Step 12:  Encrypt the test database Enable TDE by setting ENCRYPTION ON 

 

--Enable TDE by setting ENCRYPTION ON ALTER DATABASE TestTDE   SET ENCRYPTION ON; 

 

Step 13: Validate TDE is ON and has encrypted the database: SQL Server has a DMV that will show if encryption has been enabled ad the state of the encrypted database.

 

select db_name(database_id) AS DB        ,percent_complete AS percent_complete        ,encryption_state AS encryption_state from sys.dm_database_encryption_keys /* 0 = No database encryption key present, no encryption 1 = Unencrypted 2 = Encryption in progress 3 = Encrypted 4 = Key change in progress 5 = Decryption in progress 6 = Protection change in progress */

 

Step 14: Cleanup test objects

 

-- CLEAN UP USE Master ALTER DATABASE [TestTDE] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE DROP DATABASE [TestTDE] ALTER LOGIN [TDE_Login] DROP CREDENTIAL [sysadmin_ekm_cred] DROP LOGIN [TDE_Login] --ALTER LOGIN [domain\login] DROP CREDENTIAL [sysadmin_ekm_cred] DROP CREDENTIAL [sysadmin_ekm_cred] USE MASTER DROP ASYMMETRIC KEY [EKMSampleASYKey] DROP CRYPTOGRAPHIC PROVIDER [AzureKeyVault_EKM]

 

Conclusion

Configuring SQL Server with the SQL Connector, registering an Azure Active Directory application, creating RSA key in Azure Key Vault  and creating the SQL credentials, asymmetric key, and enabling TDE are the final steps to enable TDE to use Extensible Key Management (EKM) and Azure Key Vault (AKV) for encryption.

 

Live secure and prosper!

 

Adrian

Next steps

SQL Server TDE with EKM Using Azure Key Vault – Intro 

SQL Server Connector for Microsoft Azure Key Vault (aka: SQL Server Connector) – Part: 1

Azure Portal Method

PowerShell Method

Set up an Azure Active Directory Service Principal – Part: AP2 

Setup Azure Active Directory Service Principal and  Azure Key Vault (one script) – Part: PS2  

This script combines Part:AP2 & Part:AP3

Create an Azure Key Vault – Part: AP3

Configure SQL Server TDE EKM using AKV – Part: 4  (this document) 

 

Download the scripts for PowerShell and SQLCMD here:

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

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