linq - SQL列级加密的EF Core解密

Question

我和我的团队正在尝试从打开了列级加密的数据库(Azure SQL)解密值；证书存储在 key 保管库中。

预期结果:

var someLinqQuery = _contex.Users.First();
someLinqQuery.SSN = "000-00-0000";

实际结果:

var someLinqQuery = _context.Users.First();
someLinqQuery.SSN = "the var binary (encrypted) version of the ssn";

FWIW，这在使用原始 sql 时可以正常工作。但我们希望选择不这样做，并加密更多数据。

我们这里还有天蓝色的 keystore 代码:

 //Key Vault Code Below
    private static ClientCredential _clientCredential;

    public static void InitializeAzureKeyVaultProvider()
    {
        if (!isActivated)
        {
            _clientCredential = new ClientCredential(_applicationId, _clientKey);

            SqlColumnEncryptionAzureKeyVaultProvider azureKeyVaultProvider = new SqlColumnEncryptionAzureKeyVaultProvider(GetToken);

            Dictionary<string, SqlColumnEncryptionKeyStoreProvider> providers = new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>
        {
            { SqlColumnEncryptionAzureKeyVaultProvider.ProviderName, azureKeyVaultProvider }
        };

            SqlConnection.RegisterColumnEncryptionKeyStoreProviders(providers);
            isActivated = true;

        }
    }

    public static async 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");
        }
        AccessToken = result.AccessToken;
        return result.AccessToken;
    }

这是在启动类中加载的。我还将代码移动到应用程序中的其他位置，结果相同。

我的问题是，在 .net core 2.1.x 和 EF core 2.1.x 上，这可以使用 LINQ 吗？我需要升级吗？

Answer 1

根据我的研究，如果我们想使用 Always Encryption(列加密)，我们需要使用 Microsoft.Data.SqlClient .更多详情请引用document .此外，Microsoft.EntityFrameworkCore.SqlServer 3.x 依赖于 Microsoft.Data.SqlClient ，所以我建议你使用 EF core 3.x

例如。我在控制台应用程序中进行了测试。

Configure database

应用

一种。安装 SDK

<PackageReference Include="Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider" Version="1.1.0" />
<PackageReference Include="Microsoft.EntityFrameworkCore" Version="3.1.2" />
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="3.1.2" />
<PackageReference Include="Microsoft.IdentityModel.Clients.ActiveDirectory" Version="5.2.7" />

湾数据模型

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。数据库上下文

private static Boolean isInitialized;
public TestContext(DbContextOptions<TestContext> options) : base(options) {
         if(! isInitialized) { InitializeAzureKeyVaultProvider(); isInitialized = true; }

    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
       // your sql server connnection string
        var constr = "Server=<>;Initial Catalog=<>;User ID=<>;Password=<>;Column Encryption Setting=enabled";
       SqlConnection connection = new SqlConnection(constr);


        optionsBuilder.UseSqlServer(connection);
    }
    public DbSet<Patient> Patients { get; set; }
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Patient>().ToTable("Patients");
    }

    private static string clientId = "";
    private static string clientSecret = "";
    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  static async  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.测试

 static void Main(string[] args)
  {
      Console.WriteLine("Hello World!");
      DbContextOptions<TestContext> options = new DbContextOptions<TestContext>();
      var db =new TestContext(options);
      var results =db.Patients.ToListAsync().Result;
      foreach (var r in results) {
          Console.WriteLine(r.SSN);


      }

      Console.ReadLine();
  }