使用ADO.NET SqlClient访问SQL Server加密列明文及密钥复用方案咨询

核心认知：对称密钥的会话级特性 #

SQL Server的对称密钥开启状态是会话绑定的，一旦连接关闭（或会话销毁），密钥会自动关闭，无法跨会话保持开启。这是SQL Server的设计机制，因此无法实现"连接关闭后密钥仍开启"的需求，但可以通过优化调用方式减少开销，并模拟Always Encrypted的便捷性。

一、替换SMO，用原生T-SQL减少数据库开销 #

你当前用SMO遍历所有对称密钥并打开的方式会产生额外的元数据查询开销（比如database.SymmetricKeys.Count和遍历操作都会访问系统视图），直接执行T-SQL是更高效的方式：

using System.Data.SqlClient;

// 封装成复用方法
public static void OpenSymmetricKey(SqlConnection connection, string keyName, string certName)
{
    if (connection.State != ConnectionState.Open)
    {
        connection.Open();
    }
    // 按需指定要打开的密钥，避免盲目打开所有
    using var cmd = new SqlCommand(
        $"OPEN SYMMETRIC KEY {keyName} DECRYPTION BY CERTIFICATE {certName}",
        connection);
    cmd.ExecuteNonQuery();
}

// 调用示例
using var conn = new SqlConnection(connectionString);
OpenSymmetricKey(conn, "YourTargetSymmetricKey", "CLECertificate");
// 后续执行加密/解密查询...

如果需要打开多个密钥，可在同一个SqlCommand中执行多条语句，减少数据库往返次数。

二、模拟Always Encrypted的便捷调用体验 #

要实现类似Column Encryption Setting=Enabled的自动处理效果，可通过以下两种方式：

1. 自定义连接包装类 #

封装一个自动打开密钥的SqlConnection子类，在连接打开时自动执行密钥开启逻辑：

public class EncryptedSqlConnection : SqlConnection
{
    private readonly string _targetKeyName;
    private readonly string _certName;

    public EncryptedSqlConnection(string connectionString, string keyName, string certName) 
        : base(connectionString)
    {
        _targetKeyName = keyName;
        _certName = certName;
    }

    public override void Open()
    {
        base.Open();
        OpenTargetSymmetricKey(this);
    }

    private void OpenTargetSymmetricKey(SqlConnection conn)
    {
        using var cmd = new SqlCommand(
            $"OPEN SYMMETRIC KEY {_targetKeyName} DECRYPTION BY CERTIFICATE {_certName}",
            conn);
        cmd.ExecuteNonQuery();
    }
}

// 调用时直接用包装类，业务代码无需关心密钥开启
using var conn = new EncryptedSqlConnection(connectionString, "YourTargetSymmetricKey", "CLECertificate");
// 直接执行查询，密钥已自动打开
using var cmd = new SqlCommand("SELECT DecryptByKey(EncryptedColumn) FROM YourTable", conn);
var reader = cmd.ExecuteReader();

2. 使用SqlClient拦截器（.NET Core/.NET 5+） #

利用SqlClient的拦截器功能，在连接打开时自动注入密钥开启逻辑：

using Microsoft.Data.SqlClient;
using System.Data.Common;

public class SymmetricKeyInterceptor : DbConnectionInterceptor
{
    private readonly string _targetKeyName;
    private readonly string _certName;

    public SymmetricKeyInterceptor(string keyName, string certName)
    {
        _targetKeyName = keyName;
        _certName = certName;
    }

    public override void ConnectionOpened(DbConnection connection, ConnectionEndEventData eventData)
    {
        if (connection is SqlConnection sqlConn)
        {
            using var cmd = sqlConn.CreateCommand();
            cmd.CommandText = $"OPEN SYMMETRIC KEY {_targetKeyName} DECRYPTION BY CERTIFICATE {_certName}";
            cmd.ExecuteNonQuery();
        }
        base.ConnectionOpened(connection, eventData);
    }
}

// 注册拦截器（全局生效）
SqlClientFactory.Instance.AddInterceptor(new SymmetricKeyInterceptor("YourTargetSymmetricKey", "CLECertificate"));

// 后续正常使用SqlConnection即可，拦截器自动处理密钥开启
using var conn = new SqlConnection(connectionString);
conn.Open();
// 执行加密/解密查询...

三、关键注意事项 #

• 权限控制：执行OPEN SYMMETRIC KEY的登录账号需要具备VIEW DEFINITION权限（针对目标密钥和证书），以及CONTROL或OPEN权限。
• 连接池影响：SQL Server连接池回收连接时会重置会话状态（包括关闭对称密钥），因此每次从连接池获取连接后都需要重新开启密钥，上述两种便捷方式已自动处理此问题。
• 按需开启：仅打开当前查询需要的密钥，减少不必要的资源占用。

内容的提问来源于stack exchange，提问作者A H