How to Implement SQL Server Always Encrypted with "ErikEJ.EntityFramework.SqlServer" and Azure KeyVault in ASP.NET MVC 5?

Question

I have an existing ASP.NET MVC 5 web application that uses Entity Framework 6 and targets .NET Framework 4.7.2. I want to implement SQL Server Always Encrypted using the "ErikEJ.EntityFramework.SqlServer" provider, which utilizes Microsoft.Data.SqlClient, and integrate it with Azure KeyVault for secure key management.

I'd like to know the step-by-step process to achieve this. Specifically, I need guidance on how to configure the "ErikEJ.EntityFramework.SqlServer" provider, set up Azure KeyVault, and update my application code to work seamlessly with Always Encrypted.

Any help or pointers to resources would be greatly appreciated. Thank you!

I tried installing the Provider and followed the instructions as documented here at https://github.com/ErikEJ/EntityFramework6PowerTools/blob/community/src/ErikEJ.EntityFramework.SqlServer/readme.md. I am unable to find hot to configure the KeyVault in this web application.

Answer 1

I have successfully configured and obtained an encrypted secret value from Azure Key Vault within my ASP.NET web application, which utilizes Entity Framework 6 in combination with the "ErikEJ.EntityFramework.SqlServer" provider for Always Encrypted integration.

Step 1: Set Up Azure Key Vault

• Create an Azure Key Vault in the Azure Portal
• Add a new key (column encryption key) to the Key Vault, which will be used to protect your sensitive data.
• Configure permissions in Azure Key Vault to allow your web application to access the key. Assign the necessary access policy

Step 2: Update NuGet Packages In your project, make sure you have the following NuGet packages installed:

• Microsoft.Data.SqlClient
• EntityFramework (EF6)
• ErikEJ.EntityFramework.SqlServer

Step 3: Update Entity Framework and Connection String Configuration In your web.config

Configure Entity Framework to use the "ErikEJ.EntityFramework.SqlServer" Provider:

<entityFramework>
  <providers>
    <provider invariantName="ErikEJ.EntityFramework.SqlServer" type="ErikEJ.EntityFramework.SqlServer.SqlProviderServices, ErikEJ.EntityFramework.SqlServer" />
  </providers>
</entityFramework>

Update your connection string to use the Key Vault secret for the database connection.

<connectionStrings>
  <add name="MyDbContext" 
       connectionString="Server=myserver.database.windows.net;Database=mydb;User ID=myuser;Password=YourSecretFromKeyVault;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
       providerName="System.Data.SqlClient" />
</connectionStrings>

Replace "YourSecretFromKeyVault" with the secret name that you stored in Azure Key Vault.

Step 4: Configure Always Encrypted in Entity Framework

using System.Data.Entity;
using ErikEJ.EntityFramework.SqlServer;

public class MyDbContext : DbContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.HasColumnEncryptionKey("YourColumnEncryptionKey", SqlColumnEncryptionCspProvider.ProviderName);
    }
}

Replace "YourColumnEncryptionKey" with the name of the column encryption key you have created in SQL Server.

Step 5: Create a Helper for Key Vault Access Create a helper class to access secrets from Azure Key Vault

using Microsoft.Azure.KeyVault;
using Microsoft.Azure.Services.AppAuthentication;
using System;

public class KeyVaultHelper
{
    private static readonly string KeyVaultEndpoint = "https://your-keyvault-name.vault.azure.net/";
    private static readonly KeyVaultClient KeyVaultClient;

    static KeyVaultHelper()
    {
        var azureServiceTokenProvider = new AzureServiceTokenProvider();
        KeyVaultClient = new KeyVaultClient(
            new KeyVaultClient.AuthenticationCallback(azureServiceTokenProvider.KeyVaultTokenCallback));
    }

    public static string GetSecret(string secretName)
    {
        try
        {
            var secretBundle = KeyVaultClient.GetSecretAsync(KeyVaultEndpoint, secretName).Result;
            var secretValue = secretBundle.Value;

            // Print the retrieved secret to the console
            Console.WriteLine($"Key Vault Secret": {secretValue}");

            return secretValue;
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error retrieving secret: {ex.Message}");
            throw;
        }
    }
}

Result