Welcome to my blog series on setting up SQL Server TDE to use Azure Key Vault.
I’m a Program Manager with the Azure SQL Security Team / Product Group and I will walk you through the process of configuring the various services, features and products to encrypt your SQL Server databases.
Setting up TDE to use Azure Key Vault (AKV) can be a complex process which has been made even more challenging due to limited documentation, challenging online instructions and multiple steps using 4 different products:
- SQL Server Connector for Microsoft Azure Key Vault (aka: SQL Server Connector)
- Azure Active Directory (aka: AAD)
- Azure Key Vault (aka: AKV)
- SQL Server (SQL Server 2008 or later)
In this blog I will guide you through setup of TDE and Extensible Key Management (EKM) using Azure Key Vault via either the Azure Portal or PowerShell and of course SQL Server (SQLCMD).
[NOTE]
This blog pertains to:
* SQL Server On-Prem
* Azure SQL VM (IaaS) instances
This blog is NOT applicable for:
* SQL Server Managed Instance (MI)
* Azure SQL DB (PaaS)
Part - Intro of a 4 part blog series:
This blog will walk you through 4 steps and two separate ways of setting up TDE using EKM (Extensible key Management) using Azure Key Vault:
Note; EKM is the model/API by which a 3rd party can register a custom provider with SQL Server to do key management. SQL Connector is the provider that you can install on your SQL Server.
Part 1: Install SQL Server Connector for Microsoft Azure Key Vault (aka: SQL Server Connector) (Blog Part 1)
Part 2: Configure Azure Active Directory (aka: AAD) - select either path (Azure Portal or PowerShell – not both)
a. Azure Portal: this path will walk through each screen in the Azure Portal (Blog Part: AP2)
or
b. PowerShell: This path will walk through each PowerShell command (Blog Part: PS2)
Part 3: Configure Azure Key Vault (aka: AKV)
a. Azure Portal: this path will walk through each screen in the Azure Portal (Blog Part: AP3)
or
b. PowerShell: This path will walk through each PowerShell command (Blog Part: PS2)
Note: The entire process can be scripted in PowerShell (including the SQL Server portion)
that takes advantage of the variables used in PowerShell that are passed on to SQLCMD.
Part 4: The SQL Server configuration steps are the same regardless of the method of setting up Azure Active Directory (AAD) and Azure Key Vault (AKV). Configuring SQL Server database with TDE to use Azure Key Vault will be a final blog post (Blog Part 4) that builds off the previous blogs.
Before You Start
To use Azure Key Vault with your SQL Server, there are a few prerequisites:
- You must have an Azure subscription
- Install the latest Azure PowerShell (5.2.0 or higher).
- If using PowerShell - Install Azure AD PowerShell module
- Install-Module AzureAD
- Familiarize yourself with the principals of EKM storage using the Azure Key Vault by reviewing Extensible Key Management Using Azure Key Vault (SQL Server).
- Have the appropriate version of the Visual Studio C++ redistributable installed based on the version of SQL Server that you are running:
|
SQL Server Version |
Redistributable Install Link |
|
2008, 2008 R2, 2012, 2014 |
|
|
2016, 2017, 2019 |
To continue (after complying with the prerequisites):
- Follow the Part: 1 blog that describes how to download and install the SQL Server Connector for Microsoft Azure Key Vault.
- Next select one path to use either:
or
-
- PowerShell (Part: PS2), follow the step-by-step instructions for either option (not both).
- Once Azure Active Directory and Azure Key Vault are setup then follow the final blog for step-by-step configuration of SQL Server TDE (Part: 4).
Next Step
Now that you have completed the basic configuration. Follow the path to either setup Azure Active Directory (AAD) and Azure Key Vault (AKV) using the Azure Portal or PowerShell method (you only need to use one or the other, not both).
| SQL Server Transparent Data Encryption and Extensible Key Management Using Azure Key Vault – Intro (this document) | |
|
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 |
|
Conclusion
Configuring SQL Server TDE with EKM to use Azure Key Vault is a complex process that can be simplified using a few PowerShell and SQLCMD scripts.?Please take your time and work though the blogs one step at a time. Skipping a step may cause undesirable results. Please share your comments as you work through the different parts.
You can find the example PowerShell solution in the attached scripts below.
See you at the next blog (Part: 1)
Adrian
See Also
Extensible Key Management Using Azure Key Vault
SQL Server Connector Maintenance & Troubleshooting
PowerShell Basics: How to Create an Azure AD App Registration
Download the PowerShell and SQLCMD scripts here: