During the SQL PASS Summit 2015, we released a custom key store provider that enables support for column master keys stored in Azure Key Vault to Nuget.org . As you may recall, an earlier blog post discussed the process of creating a custom key store provider using Azure Key Vault as an example key store. That article remains a great source for those who wish to implement their own provider for a key store not supported directly by Microsoft. In this article, however, we will go over the streamlined process for integrating the Azure Key Vault column master key provider NuGet package into your client-side application.
Setup
Azure Key Vault
The first thing you’ll need is access to an Azure Key Vault with the correct permissions. We’ll walk you through the steps here, but for a complete view on setting this up, you can always take a look at Azure Key Vault’s Getting Started page. These instructions will use the Azure PowerShell 1.0 Preview , which can be installed by running the following commands in an Administrator-elevated PowerShell window:
Set-ExecutionPolicy RemoteSigned
|
Sign in to your Azure account:
Login-AzureRmAccount
|
Create a new Azure Key Vault:
New-AzureRmKeyVault
|
By default, when you create a new Azure Key Vault, it will have certain default vault-level permissions, however to use keys with Azure Key Vault, we’ll need to have all of create, get, sign, verify, wrapKey, and unwrapKey permissions. These permissions are required for creating a new column master key in Azure Key Vault and using it to set up encryption via SQL Server Management Studio, which we’ll go over later in this post. To grant those permissions, run the following command in the Azure PowerShell window:
|
Note: The permissions listed above are required for using the Always Encrypted wizard in SQL Server Management Studio. If you wish to use the "New Column Master Key" window (accessed via right-clicking on the "Always Encrypted Keys" folder under "Security"), you'll also need the list permission.
SQL Server/Azure SQL DB
Create a table containing some sensitive data that you wish to be encrypted. For this demo, we’ll using a similar sample schema as in a previous article on the SSMS Encryption wizard :
CREATE TABLE [dbo].[Patients](
|
Configure Encryption on your Table
As detailed in earlier articles on Always Encrypted, you can use two different methods in SQL Server Management Studio 2016 CTP3 (or later) to create your column master key and column encryption key. The easiest way to do this is by using the new Always Encrypted wizard, detailed in SSMS Encryption Wizard - Enabling Always Encrypted in a Few Easy Steps .
On the Column Selection step, choose Deterministic encryption for the SSN column and Randomized for BirthDate. Make sure a new generated key is selected for both columns, which will be configured in the next step.
When you get to the Master Key Configuration step in the wizard, select Azure Key Vault as your key store provider, sign in with the Azure account you used to create the vault, and select it from the drop-down below.
If you right-click on your newly-generated column master key and script it, you’ll see something like the following:
CREATE COLUMN MASTER KEY [CMK_Auto1]
GO
|
Note that the key store provider name is “AZURE_KEY_VAULT”. This string must match the one you use to register the Azure Key Vault provider with ADO.net in a later step. Don’t worry; I’ll remind you once we get there.
Development and Configuration Changes
Generate Application ID and Secret
In order to allow your client application to access and use the keys in your Azure Key Vault, we need to provision a Client ID and Secret that your app will use to authenticate. To do this, head to the Azure Portal and log in.
Select “Active Directory” in the left sidebar, choose the Active Directory you wish to use (or create a new one if it doesn’t exist), then click “Applications”
Add a new application by filling out the modal window that appears.
Enter a name, select “Web Application” as the type, and enter any URL for the Sign-On URL and App ID URI. These must include “http://”, but do not need to be real pages. For this example, I’m using “http://AkvProviderDemo.ms” for both.
Go to the “Configure” tab and generate a new client key (also called a “secret”) by selecting a duration from the dropdown, then saving the configuration. Copy the client ID and secret out to a text file , as they’ll be used later in this blog post!
Now, grant the Client ID you just generated access to your key vault by running the following in the Azure PowerShell window:
Set-AzureRmKeyVaultAccessPolicy
|
This set of permissions are necessary for the Azure Key Vault provider to decrypt the column encryption key and therefore enable normal functionality for the client application.
Integrate the Azure Key Vault Provider into your Application
Now, you’re ready to actually package the Azure Key Vault key store provider into your client application so you can begin to benefit from all the setup work you’ve done. To demonstrate this step, we’ll be using a simple sample application that was used in earlier articles about Always Encrypted.
First, you’ll need to install the Microsoft.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvider and Microsoft.IdentityModel.Clients.ActiveDirectory NuGet packages either by using the Package manager console or the NuGet Package Manager built into Visual Studio, both of which can be found under Tools -> NuGet Package Manager.
Package manager console:
Install-Package Microsoft.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvider
|
Package Manager:
In order to inform the ADO.net driver that you’d like to use a key store provider that didn’t ship with .NET, you now need to register the Azure Key Vault provider with the driver. To do this, add the follow code to the startup of your application:
|
Four things to note about this method:
-
It’s reading the Client ID and Client Secret (those strings you copied from the Azure Portal) from the app.config file in your project. You'll need to add the following entries in order to enable this:
<appSettings>
<add key="AuthClientId" value="<your client id>" />
<add key="AuthClientSecret" value="<your client secret/key>" />
</appSettings>
- We constructed the ClientCredential object using the Client ID and Secret. This is just one (perhaps the most straightforward) method to create the ClientCredential, although username and password and two-factor authentication methods can also be used. It’s all up to you!
- Remember when I mentioned the "AZURE_KEY_VAULT" key store provider name? To make it easy to use the default name, it’s hardcoded into SqlColumnEncryptionKeyStoreProvider as a static property. When you register a provider with ADO.net, it knows which one to use for which column master keys by this string, so be careful if you decide to change the defaults!
- The constructor for SqlColumnEncryptionAzureKeyVaultProvider takes a delegate as an argument. This function is the one that will provide an access token for your vault to the driver anytime an Azure Key Vault operation is required.
|
At this point, you may be wondering why none of these steps were necessary for the case where column master keys are protected in the Windows certificate store. .NET 4.6 ships with the SqlColumnEncryptionCertificateStoreProvider class, which contains all the logic necessary for accessing the certificate store of the machine where that code is running. Providers that are included in .NET are pre-registered with the ADO.NET driver, saving you the effort of doing so yourself.
When you use a certificate as the column master key, the execution context of the application is used to access the certificate installed in either the Local Machine or that context's Current User certificate store. Because authorization is handled at a lower level than the application, there’s no need for you to implement an authentication delegate or add credentials to app.config.
One thing that remains the same between the Windows certificate store and Azure Key Vault providers is that your connection string still needs to contain “Column Encryption Setting = Enabled” in order for your client application to know to try to transparently handle encryption.
Conclusion
And that’s it! Once you have the Client IDs and Secrets provisioned, integrating Azure Key Vault into your existing client-side application is as easy as installing some NuGet packages, adding a couple entries to your app.config, and pasting in 20 lines of code.
Check out the downloadable sample Visual Studio project to experiment further with the Always Encrypted capabilities, like transparently encrypting new entries, reading existing rows, and querying your table via equality WHERE clauses.
Have questions or feedback? Please, let us know in the comments!