SQL Server Always Encrypted with .NET Core not compatible

Question

I'm trying to use the Always Encrypted feature of SQL Server 2016 with .NET Core and seems like it can not be used (yet). Trying to import the Microsoft.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvider from Nuget, I get an error stating it is not compatible:

Package Microsoft.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvider 1.0.201501028 is not compatible with netstandard1.6 (.NETStandard,Version=v1.6)

Any ideas on how/where to get a compatible version?

Answer 1

Always Encrypted is now supported in .Net Core 3.1 LTS.

You have to use the Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider nuget package

    Install-Package Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider -Version 1.1.1

Make sure you have a Keyvault setup.

FOR DEBUGGING your account in VS has to have sufficent rights to access the keyvault. (When published the app itself has to have sufficent rights : see https://learn.microsoft.com/en-us/azure/key-vault/managed-identity) Get and List permissions alone might not be sufficient.

Then in program.cs :

using Microsoft.AspNetCore.Hosting;
using Microsoft.Azure.KeyVault;
using Microsoft.Azure.Services.AppAuthentication;
using Microsoft.Data.SqlClient;
using Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Configuration.AzureKeyVault;
using Microsoft.Extensions.Hosting;
//namespaces etc omitted
 public static IHostBuilder CreateHostBuilder(string[] args) =>
     Host.CreateDefaultBuilder(args)
         .ConfigureAppConfiguration((context, config) =>
         {
             var keyVaultEndpoint = GetKeyVaultEndpoint();
             if (!string.IsNullOrEmpty(keyVaultEndpoint))
             {
                 var azureServiceTokenProvider = new AzureServiceTokenProvider();
                 var keyVaultClient = new KeyVaultClient(
                     new KeyVaultClient.AuthenticationCallback(
                         azureServiceTokenProvider.KeyVaultTokenCallback));
                 config.AddAzureKeyVault(keyVaultEndpoint, keyVaultClient, new DefaultKeyVaultSecretManager());
                 SqlColumnEncryptionAzureKeyVaultProvider sqlColumnEncryptionAzureKeyVaultProvider = new SqlColumnEncryptionAzureKeyVaultProvider(new KeyVaultClient.AuthenticationCallback(
                         azureServiceTokenProvider.KeyVaultTokenCallback));
                 SqlConnection.RegisterColumnEncryptionKeyStoreProviders(customProviders: new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>(capacity: 1, comparer: StringComparer.OrdinalIgnoreCase)
                 {
                     { 
                         SqlColumnEncryptionAzureKeyVaultProvider.ProviderName, sqlColumnEncryptionAzureKeyVaultProvider
                     }
                 });                     
             }
         })
         .ConfigureWebHostDefaults(webBuilder =>
         {
             webBuilder.UseStartup<Startup>();
         });
    private static string GetKeyVaultEndpoint() => "https://YOURKEYVAULT.vault.azure.net/";
}

In StartUp.cs ConfigureServices:

using Microsoft.Data.SqlClient;
//Code omitted
services.AddDbContext<EnitiesModel>(options =>
            options.UseSqlServer(new SqlConnection(Configuration.GetConnectionString("EntitiesModel"))));

Make sure your connectionstring contains the Column Encryption Setting=Enabled parameter:

 "ConnectionStrings": {
"EntitiesModel": "Server=SOMESERVER.database.windows.net;Database=SOMEDB;Trusted_Connection=False;Encrypt=True;Integrated Security=False;
MultipleActiveResultSets=true;persist security info=True;user id=SOMEDBACCOUNT;password=SOMEPASSWORD;
Column Encryption Setting=enabled;"   
  }

Small gotcha : If you used DB scaffolding make sure the Model connectionstring has the Column Encryption Setting aswell! (if you did not change it, it is standard inside the DBModel class after scaffolding with a VS warning)

This should get you up and running...

Answer 2

Disclaimer: I am a Program Manager at Microsoft

Always Encrypted is currently not supported in .NET Core. It is on our roadmap, we don't have a timeline for it yet.

This is now supported. See answers below.

Answer 3

It's now supported in .NET Core 3.0 Preview 5, which provides a new SqlClient supporting Always Encrypted and more. See this comment for more info.

For the Key Vault provider, you need to use Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider instead.

Answer 4

A variant of the Program.cs from Tim's answer above, but for apps registered with Azure App Registration:

namespace Sample
{
   public class Program
   {
       public static void Main(string[] args)
       {
           CreateHostBuilder(args).Build().Run();
       }

       public static IHostBuilder CreateHostBuilder(string[] args) =>
     Host.CreateDefaultBuilder(args)
         .ConfigureAppConfiguration((context, config) =>
         {
             var keyVaultEndpoint = GetKeyVaultEndpoint();
             if (!string.IsNullOrEmpty(keyVaultEndpoint))
             {
                 var azureServiceTokenProvider = new AzureServiceTokenProvider(keyVaultEndpoint);
                 var keyVaultClient = new KeyVaultClient(
                     new KeyVaultClient.AuthenticationCallback(
                         azureServiceTokenProvider.KeyVaultTokenCallback));
                 
                 SqlColumnEncryptionAzureKeyVaultProvider sqlColumnEncryptionAzureKeyVaultProvider = new SqlColumnEncryptionAzureKeyVaultProvider(new KeyVaultClient.AuthenticationCallback(
                         azureServiceTokenProvider.KeyVaultTokenCallback));
                 SqlConnection.RegisterColumnEncryptionKeyStoreProviders(customProviders: new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>(capacity: 1, comparer: StringComparer.OrdinalIgnoreCase)
                 {
                     {
                         SqlColumnEncryptionAzureKeyVaultProvider.ProviderName, sqlColumnEncryptionAzureKeyVaultProvider
                     }
                 });
             }
         })
        .ConfigureWebHostDefaults(webBuilder =>
        {
            webBuilder.UseStartup<Startup>();
        });

       private static string GetKeyVaultEndpoint() => "RunAs=App;AppId=<app ID>;TenantId=<tenant ID>.onmicrosoft.com;AppKey=<app secret>";
   }
 }