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

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)

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: