如何让SQL Server及Azure SQL Database自动处理列的加密与解密？

当然可以实现指定列的自动加密存储、自动解密读取，结合你的场景（列无索引、不用于WHERE子句），以下是两种最适配的原生方案：

1. Always Encrypted（推荐生产环境使用） #

这是SQL Server和Azure SQL官方提供的列级加密方案，完全由数据库驱动自动处理加解密，不需要修改任何业务逻辑代码，完美匹配你的需求。

配置流程： #

• 创建加密密钥
  先在数据库中创建列主密钥（CMK）和列加密密钥（CEK）。Azure SQL推荐用Azure Key Vault托管CMK，本地SQL Server可以用证书或Windows证书存储：

  -- Azure SQL 示例：用Azure Key Vault创建CMK
  CREATE COLUMN MASTER KEY MyCMK
  WITH (
      KEY_STORE_PROVIDER_NAME = 'AZURE_KEY_VAULT',
      KEY_PATH = 'https://your-key-vault.vault.azure.net/keys/your-cmk-key'
  );
  
  -- 创建列加密密钥CEK
  CREATE COLUMN ENCRYPTION KEY MyCEK
  WITH VALUES (
      COLUMN_MASTER_KEY = MyCMK,
      ALGORITHM = 'RSA_OAEP',
      ENCRYPTED_VALUE = 0x... -- 该值可通过SSMS图形界面生成，无需手动编写
  );
  

• 加密目标列
  因为你的列不需要用于查询，选择随机化加密（安全性更高）：

  ALTER TABLE YourTargetTable
  ALTER COLUMN YourSecretColumn NVARCHAR(255) -- 保持原数据类型
  ENCRYPTED WITH (
      COLUMN_ENCRYPTION_KEY = MyCEK,
      ENCRYPTION_TYPE = RANDOMIZED,
      ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
  ) NULL;
  

• EF Core连接配置
  只需在连接字符串中添加Column Encryption Setting=Enabled，SqlClient驱动会自动处理加解密：

  "ConnectionStrings": {
      "AppDbContext": "Server=tcp:your-azure-sql.database.windows.net,1433;Initial Catalog=YourDb;User ID=DbUser;Password=YourStrongPwd;Encrypt=True;Column Encryption Setting=Enabled"
  }
  

  本地SQL Server只需确保客户端能访问CMK的存储位置（比如本地证书）即可。

2. 手动列级加密（自定义密钥管理） #

如果需要更灵活的密钥控制，也可以用SQL Server内置的加密函数，通过EF Core的ValueConverter封装实现自动加解密，业务代码无感知。

实现步骤： #

• 创建加密密钥链

  -- 创建数据库主密钥
  CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourSuperStrongPassword123!';
  -- 创建证书
  CREATE CERTIFICATE ColumnEncryptCert WITH SUBJECT = 'Secret Column Encryption Cert';
  -- 创建对称密钥（用于实际加密列数据）
  CREATE SYMMETRIC_KEY SecretColumnSymKey WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE ColumnEncryptCert;
  

• 修改列类型
  将目标列改为VARBINARY(MAX)（因为加密后的数据为二进制）：

  ALTER TABLE YourTargetTable
  ALTER COLUMN YourSecretColumn VARBINARY(MAX) NULL;
  

• EF Core配置自动转换
  在DbContext的OnModelCreating中配置值转换器，自动完成加密解密：

  protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
      modelBuilder.Entity<YourEntity>()
          .Property(e => e.YourSecretColumn)
          .HasConversion(
              plainText => EncryptValue(plainText),
              encryptedData => DecryptValue(encryptedData)
          );
  }
  
  private byte[] EncryptValue(string plainText)
  {
      if (string.IsNullOrWhiteSpace(plainText)) return null;
      using var connection = Database.GetDbConnection();
      connection.Open();
      using var command = connection.CreateCommand();
      command.CommandText = @"
          OPEN SYMMETRIC KEY SecretColumnSymKey DECRYPTION BY CERTIFICATE ColumnEncryptCert;
          SELECT EncryptByKey(Key_GUID('SecretColumnSymKey'), @plainText);
      ";
      command.Parameters.Add(new SqlParameter("@plainText", plainText));
      return (byte[])command.ExecuteScalar();
  }
  
  private string DecryptValue(byte[] encryptedData)
  {
      if (encryptedData == null) return null;
      using var connection = Database.GetDbConnection();
      connection.Open();
      using var command = connection.CreateCommand();
      command.CommandText = @"
          OPEN SYMMETRIC KEY SecretColumnSymKey DECRYPTION BY CERTIFICATE ColumnEncryptCert;
          SELECT DecryptByKey(@encryptedData);
      ";
      command.Parameters.Add(new SqlParameter("@encryptedData", encryptedData));
      return (string)command.ExecuteScalar();
  }
  

方案对比 #

内容的提问来源于stack exchange，提问作者David Thielen