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.
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:
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:
[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.
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]
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!
|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
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:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.