Intro - SQL Server Transparent Data Encryption and Extensible Key Management Using Azure Key Vault

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:

1. SQL Server Connector for Microsoft Azure Key Vault (aka: SQL Server Connector)
2. Azure Active Directory (aka: AAD)
3. Azure Key Vault (aka: AKV)
4. 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:

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: 
  • the Azure Portal (Part: AP2 & Part: AP3)

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

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: