Using Always Encrypted with EF Core and storing Master Key in Azure Key Vault

I have a .Net Core 3.1 project using EF Core to access data in an Azure SQL Database. I'm currently trying to implement the Always Encrypted feature on some sensitive column.

I successfully encrypted the column, storing the Master Key in Azure Key Vault where I created an App Registration with access to this key.

I used this video in order to achieve this: https://www.youtube.com/watch?v=POLTjo7GpRc

I then was able to retrieve the Master Key in my c# code and launch an SQL query, a simple select from the encrypted table.

I could retrieve the encrypted column in clear text(decrypted), fine.

My question now, how can I configure EF Core to retrieve my Master Key in Azure Key Vault and return the decrypted value in my Entities? Sql query works but I don't know how to make it work with EF Core.

Thanks a lot!

According to my test, if you have configured Always Encrypted for your SQL Server with Azure key vault, please use Microsoft.EntityFrameworkCore.SqlServer to implement EF core in your application.

For example

1. Configure Always Encrypted

2. Create Azure AD application and configure access policy for the application in Azure key vault.

3. Configure application

   a. install sdk

    ItemGroup>
   <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="3.1.1" />
   <PackageReference Include="Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider" Version="1.1.0" />
   <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="3.1.1">
   <PrivateAssets>all</PrivateAssets>
   <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
   </PackageReference>
   <PackageReference Include="Microsoft.IdentityModel.Clients.ActiveDirectory" Version="5.2.7" />
   <PackageReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Design" Version="3.1.1" />
   </ItemGroup>
   
   

   b. Create Modle

   public class Patient
   {
   
     public int PatientId { get; set; }
     public string SSN { get; set; }
     public string FirstName { get; set; }
     public string LastName { get; set; }
     public DateTime BirthDate { get; set; }
   }
   

   c. create DbContext

   public class TestContext :DbContext
   {
      private static Boolean isInitialized;
      public  TestContext(DbContextOptions<TestContext> options) : base(options)
      {
          if(! isInitialized) { InitializeAzureKeyVaultProvider(); isInitialized = true; }
   
   
      }
   
   
      public DbSet<Patient> Patients { get; set; }
      protected override void OnModelCreating(ModelBuilder modelBuilder)
      {
          modelBuilder.Entity<Patient>().ToTable("Patients");
      }
   
      private static string clientId = "the ad application appid";
      private static string clientSecret = "the ad application appSecret";
      private static ClientCredential _clientCredential;
   
      private static void  InitializeAzureKeyVaultProvider()
      {
          _clientCredential = new ClientCredential(clientId, clientSecret);
   
          SqlColumnEncryptionAzureKeyVaultProvider azureKeyVaultProvider =
            new SqlColumnEncryptionAzureKeyVaultProvider(GetToken);
   
          Dictionary<string, SqlColumnEncryptionKeyStoreProvider> providers =
            new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>();
   
          providers.Add(SqlColumnEncryptionAzureKeyVaultProvider.ProviderName, azureKeyVaultProvider);
          SqlConnection.RegisterColumnEncryptionKeyStoreProviders(providers);
      }
   
      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;
      }
   }
   

   d. Register the DbContext. Add the following code in Startup.cs. public void ConfigureServices(IServiceCollection services) { services.AddDbContext<TestContext>(options => options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection"))); services.AddControllersWithViews(); }

   e. add connectionString in appsettings.json

   {
   "ConnectionStrings": {
   "DefaultConnection": "Server=tcp:<your server name>.database.windows.net,1433;
   Initial Catalog=<db name>;
   Persist Security Info=False;
   User ID=<user>;
   Password=<password>;
   Column Encryption Setting=enabled;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
   },
   ...
   

For more details, please refer to

https://learn.microsoft.com/en-us/aspnet/core/data/ef-mvc/intro?view=aspnetcore-3.1

https://learn.microsoft.com/en-us/sql/connect/ado-net/sql/sqlclient-support-always-encrypted?view=sql-server-ver15