EF Core 解密 SQL 列级加密

Question

EF Core 解密 SQL 列级加密

linqencryption.net-coreentity-framework-coreazure-sql-database

4

我和我的团队正在尝试解密启用了列级加密并将证书存储在密钥保管库中的数据库(Azure SQL)中的值。

期望结果：

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";

就我所知，使用原始 SQL 的方法可以正常工作。但我们希望有其他选项，能够加密更多的数据。

另外，这里提供了 Azure KeyVault 代码：

 //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? 我需要升级吗？

- r_work_account

请尝试使用BitConverter.ToString进行解密。 - Jim Xu

@JimXu，这只是将加密值转换为字节数组的字符串表示形式。请参考以下示例：'var model = new UserModel{ SSN = BitConverter.ToString(a.SSN) };''a' 值来自 foreach 循环。我有什么遗漏吗？ - r_work_account

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Jim Xu · Accepted Answer

根据我的研究，如果我们想要使用Always Encryption（列加密），我们需要使用Microsoft.Data.SqlClient。更多详情请参考文档。此外，Microsoft.EntityFrameworkCore.SqlServer 3.x依赖于Microsoft.Data.SqlClient，因此我建议您使用EF core 3.x。

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

Configure database

Application

a. Install 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" />

b. data model

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. database context

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. test

 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();
  }