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.
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.
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.
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.
Step 5: Add Credential to your Windows Login
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.
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.
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.
Step 9: Alter the new Login: Alter the new Login and map the EKM credential to the new login.
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)
Step 11: Create a database encryption Key Create an ENCRYPTION KEY using the ASYMMETRIC KEY (EKMSampleASYKey)
Step 12: Encrypt the test database Enable TDE by setting 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.
Step 14: Cleanup test objects
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: