Full Tutorial on using Always Encrypted with Azure Key Vault in ASP.NET and ASP.NET Core

Introduction

Despite all Microsoft documentation available on the topic, and against common sense, AzureKeyVaultProvider DOES NEED to be registered using manual code inserted into your project.
Why common sense? Here are few reasons:

1. Using Azure Key Vault (AKV) is a default approach for securing certificates.
2. Web App is hosted in Azure.
3. Connection string does contain the Always Encrypted attribute.
4. Any Azure account does have a Default Active Directory predefined.
5. Web App can be activated as a default AD user and given permissions to AKV.

However, not only that this doesn’t happen by default, but also neither MS documentation nor MS support provide a unitary solution.

Action Plan

Eventually, getting over Microsoft’s poor support services on this particular topic, we managed to debug the proper approach by ourselves and made it bend around the following three mandatory steps:

1. Configure your Azure account:
   1. Create an Azure Key Vault (AKV) bucket (or use an existing one)
   2. Create an Azure Active Directory (AD) dedicated to your Web App
   3. Grant additional Cryptographic Permissions for that user.
2. Upgrade your .NET project:
   1. Add attribute to Connection String(s).
   2. Add AzureKeyVaultProvider NuGet packages.
   3. Add registration code to project start routine.
3. Setup your DB for Always Encrypt:
   1. Use Column Encryption wizard.
   2. Configure DB connection to read encrypted data.

Configure Your Azure Account

There are three things you need to take care of here: create a AKV (recommended) using this Quickstart tutorial or use an existing one, then register your Web app as an Active Directory user and grant this user proper permissions to that AKV.

Make the distinction between AD User account and App Registration when you configure Active Directory and use the latter, as shown in the image below:

Interestingly enough, and against MS Support recommendation, instead of activating your Web App as AD user and use its Principal ID, you actually need to create a completely separate AD user and configure it with proper access to AKV. While creating it, make sure you copy its Application (client) ID and (Client) Secret value (not Secret ID) for later use in this process as shown below:

Once you have the AD user data ready, navigate back to AKV and configure the user with proper permissions by configuring a new Access Policy.

IMPORTANT!! Another wrongfully propagated recommendation from MS documentation is to use the predefined permissions template called Key, Secret & Certificate Management just like seen in the picture below:

In reality, you will also need the Cryptographic Operations set as you can see in the picture below:

Upgrade Your .NET Project

A. First Thing You Need to Update Your Connection String

Go to your your .config file and add the following attribute:

Column Encryption Setting=enabled;

IMPORTANT!! If you are using multiple Connection Strings, for different purposes, and you use Column encryption on each of them, add this attribute to each one of them !!!

For advanced coders, Microsoft allows you to implement your own custom encryption mechanism.

B. Second Step here is to Add the AzureKeyVaultProvider NuGet Package

IMPORTANT!! In .NET, there are two different approaches when working with data, each from a different namespace and using a separate (yet similar) sets of classes:

• System.Data.SqlClient (SDS) and
• Microsoft.Data.SqlClient (MDS)

Each of them uses a different NuGet package for decrypting SQL data using Always Encrypted keys stored in AKV.

C. ADd Code that Executes ONLY at Project Start

Here is where you perform the registration of the AZURE_KEY_VAULT provider name - whose absence causes the infamous exception below:

Failed to decrypt a column encryption key. Invalid key store provider name: 
'AZURE_KEY_VAULT'. A key store provider name must denote either a system 
key store provider or a registered custom key store provider. 
Valid system key store provider names are: 'MSSQL_CERTIFICATE_STORE', 
'MSSQL_CNG_STORE', 'MSSQL_CSP_PROVIDER'. Valid (currently registered) 
custom key store provider names are: . Please verify key store provider 
information in column master key definitions in the database, and verify 
all custom key store providers used in your application are registered properly.

IMPORTANT!! AKV store provider registration code can only be executed once, therefore: if you are using .NET MVC, add this code to Global.asax , respectively the Application_Start event and if you are using .NET MVC Core, add this code to Startup.cs class.

IMPORTANT!! If your code uses both SDS and MDS, you need to register the AKV store provider for both. Performing the registration only for one of them will not suffice!!

Following, we will consider that Entity Framework traditionally uses the (SDS) namespace, while ADO Factory uses (MDS). Therefore notations will be subsequent:

• Register AKV store by calling InitializeAzureKeyVaultProvider4Ado method for ADO usage.
• Register AKV store by calling InitializeAzureKeyVaultProvider4Ef method for EF usage.

Presuming you want to use both cases, please follow the three steps below accordingly.

Step 1

Isolate each set of libraries references relatively to SDS (EF) and MDS (ADO) cases:

using AuthenticationResult = 
      Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationResult;
using ClientCredential = 
      Microsoft.IdentityModel.Clients.ActiveDirectory.ClientCredential;

using SqlColumnEncryptionAzureKeyVaultProviderAdo = 
      Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider.
      SqlColumnEncryptionAzureKeyVaultProvider;
using SqlColumnEncryptionAzureKeyVaultProviderEf = 
      Microsoft.SqlServer.Management.AlwaysEncrypted.
      AzureKeyVaultProvider.SqlColumnEncryptionAzureKeyVaultProvider;

using SqlColumnEncryptionKeyStoreProviderAdo = 
      Microsoft.Data.SqlClient.SqlColumnEncryptionKeyStoreProvider;
using SqlColumnEncryptionKeyStoreProviderEf = 
      System.Data.SqlClient.SqlColumnEncryptionKeyStoreProvider;

using SqlConnectionAdo = Microsoft.Data.SqlClient.SqlConnection;
using SqlConnectionEf = System.Data.SqlClient.SqlConnection;

Step 2

Initialize Azure Active Directory credentials as defined earlier while App registration process (that is Application (client) ID and (Client) Secret value):

static readonly string s_clientId = "---";
static readonly string s_clientSecret = "---"; 
private static ClientCredential _clientCredential;

Step 3

Add the two separate initialization methods InitializeAzureKeyVaultProvider4Ef and InitializeAzureKeyVaultProvider4Ado mentioned earlier:

For EF

private static void InitializeAzureKeyVaultProvider4Ef()
{
    _clientCredential = new ClientCredential(s_clientId, s_clientSecret);

    SqlColumnEncryptionAzureKeyVaultProviderEf azureKeyVaultProvider = 
             new SqlColumnEncryptionAzureKeyVaultProviderEf(GetToken);

    Dictionary<string, SqlColumnEncryptionKeyStoreProviderEf> providers = 
          new Dictionary<string, SqlColumnEncryptionKeyStoreProviderEf>();

    providers.Add(SqlColumnEncryptionAzureKeyVaultProviderEf.ProviderName, 
                  azureKeyVaultProvider);
    SqlConnectionEf.RegisterColumnEncryptionKeyStoreProviders(providers);
}

For ADO

private static void InitializeAzureKeyVaultProvider4Ado()
{
    _clientCredential = new ClientCredential(s_clientId, s_clientSecret);

    SqlColumnEncryptionAzureKeyVaultProviderAdo azureKeyVaultProviderAdo = 
             new SqlColumnEncryptionAzureKeyVaultProviderAdo(GetToken);

    Dictionary<string, SqlColumnEncryptionKeyStoreProviderAdo> providers = 
               new Dictionary<string, SqlColumnEncryptionKeyStoreProviderAdo>();

    providers.Add(SqlColumnEncryptionAzureKeyVaultProviderAdo.ProviderName, 
                  azureKeyVaultProviderAdo);
    SqlConnectionAdo.RegisterColumnEncryptionKeyStoreProviders(providers);
}

Depending on your project legacy state, you will actually need to use two different implementation tracks. However, your quickest option would be to downgrade MDS from its latest version (3.0.0) to 1.2.0 because it offers you linearity with SDS by using the same callback function for obtaining the authentication token.

For any version in the range of v.1.0.0 up to v.1.2.0, use this callback:

private async static Task<string> GetToken
              (string authority, string resource, string scope)
{
    var authContext = new AuthenticationContext(authority);
    AuthenticationResult result = await authContext.AcquireTokenAsync
                                  (resource, _clientCredential);

    if (result == null)
throw new InvalidOperationException("Failed to obtain the access token");
    return result.AccessToken;
}

For any version starting v.2.0.0 and above, you can find its extensive solution on MS documentation on the topic, thanks to SqlClient team highly active on their public Git repository.

Setup Your DB for Always Encrypt

Use the Column Encryption feature either by using SSMS wizard triggered from Tables right-click Contextual Menu (recommended) OR by using code samples described by MS.
For all common cases, I recommend using SSMS wizard described in this tutorial and shown in the image below:

IMPORTANT!! When using this Column encryption feature, it will change the selected column's collation.

IMPORTANT!! This approach also offers you live decreption of the encrypted columns, so you can switch back and forth - BUT, decrypting a column does not change its collation, so some query operations such as text comparison will not work.

However, if you want to tackle a custom approach OR you want to master the process, follow this link.

Getting back to the day-to-day debug and maintenance processes, you need to configure your DB connection to read encrypted data in plain text.

Obviously, app problems do not disappear once you encrypt data and therefore, for maintenance purposes, you can still debug the plain view data if you tick the Enable Always Encrypted option from the Always Encrypted tab of the Connect to Server form, also described in the above tutorial and visible in the image below:

IMPORTANT!! Worry not about developers accessing plain data at their discretion!! When performing the connection, SSMS requires an Azure user credentials who has been previously set with proper access to the specific KeyVault. Therefore security control is in place.

Conclusions

The obvious benefits of using Microsoft's feature for Column encryption are immediate:

• You have an instantly reversible mechanism at hand.
• You can still read the plain text though encrypted for the rest of the world.
• You can change the encryption key at any time.
• Works with Azure Key Vault by default.
• It works integrated with Active Directory by default.

I know the many developers are still using column encryption mechanisms developed by themselves - I've been one of those. And I also understand why some of them are still using the same because they got lost in documentation - but that's history now, isn't it? :)

History

• 21^st February, 2023: Initial version