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.
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: