在 EF Core 中使用 Always Encrypted 并将主密钥存储在 Azure Key Vault 中

Question

我有一个使用EF Core访问Azure SQL数据库的.Net Core 3.1项目。我目前正在尝试在一些敏感列上实现Always Encrypted功能。

我成功地加密了该列，并将主密钥存储在Azure Key Vault中，我创建了一个具有对此密钥访问权限的应用程序注册表。

我使用了这个视频来实现：https://www.youtube.com/watch?v=POLTjo7GpRc 然后，我能够在我的C#代码中检索主密钥并启动SQL查询，从加密表中选择一个简单的查询。

我可以以明文（解密）的形式检索到加密列。

我的问题是，如何配置EF Core以从Azure Key Vault检索我的主密钥并在我的实体中返回解密值？ SQL查询有效，但我不知道如何使其与EF Core一起工作。

非常感谢！

- Lekster001

Answer 1

根据我的测试，如果您已经为 SQL Server 配置了 Azure 密钥保管库的 Always Encrypted 功能，请在应用程序中使用 Microsoft.EntityFrameworkCore.SqlServer 实现 EF Core。

例如：

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;"
   },
   ...
   

更多详细信息，请参阅：

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

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

- Jim Xu

---

请问您能告诉我从哪里获取clientId和clientSecret吗？在Azure上找不到类似的东西。 - Mohamad Mousheimish