MS SQL Server 2016版本如何启用Always Encrypted功能？

Question

在MS SQL Server 2016中启用Always Encrypted功能时，常见的技术问题是如何正确配置加密密钥并确保应用程序兼容。具体来说，用户可能遇到的问题是：如何生成并安全存储列主密钥（CMK）和列加密密钥（CEK），同时确保客户端应用程序能够访问这些密钥以执行加密和解密操作？此外，由于Always Encrypted在服务器端不进行解密，某些SQL操作（如排序、JOIN或聚合函数）可能会受到限制，这需要开发者调整查询逻辑或数据设计以适应这一限制。最后，驱动程序版本是否支持Always Encrypted也是一个关键点，若驱动过旧，可能导致连接失败或数据无法正确处理。

Answer 1

1. 基础概念：Always Encrypted与密钥管理

在MS SQL Server 2016中启用Always Encrypted功能时，理解列主密钥（CMK）和列加密密钥（CEK）的生成与存储至关重要。以下是基础步骤：

1. 创建CMK：使用SQL Server Management Studio (SSMS)或T-SQL语句，在数据库中定义CMK，并将其存储在安全的位置（如Windows证书存储或Azure Key Vault）。
2. 生成CEK：为每个需要加密的列创建CEK，并通过CMK进行保护。
3. 配置客户端访问：确保客户端应用程序能够正确加载CMK以解密CEK。

CREATE COLUMN MASTER KEY CMK_Sample
WITH (
    KEY_STORE_PROVIDER_NAME = 'MSSQL_CERTIFICATE_STORE',
    KEY_PATH = 'CurrentUser/My/'
);

2. 技术问题分析：密钥存储与访问

用户常遇到的问题是如何安全存储CMK并确保客户端可以访问这些密钥。以下是常见问题及解决方案：

问题	原因	解决方案
无法找到CMK	CMK未正确存储或路径错误	验证CMK的KEY_PATH是否正确指向证书存储
客户端无法访问CMK	权限不足或网络问题	检查客户端是否有访问证书存储的权限

此外，对于Azure Key Vault用户，需确保已配置正确的访问策略...

3. 查询限制与优化

由于Always Encrypted在服务器端不执行解密操作，某些SQL操作会受到限制。以下是受影响的操作及其解决方法：

• 排序：若排序字段加密，需将数据拉取到客户端后排序。
• JOIN：仅支持基于确定性加密的列进行JOIN。
• 聚合函数：无法直接对随机化加密列应用SUM、AVG等。

以下是一个调整后的查询示例：

SELECT * FROM Table1 t1
INNER JOIN Table2 t2
ON EncryptByKey(KEY_GUID('CEK'), t1.Column1) = t2.Column1;

4. 驱动程序兼容性

驱动程序版本是成功实现Always Encrypted的关键因素。过旧的驱动可能导致连接失败或数据处理异常。以下是推荐的驱动版本：

• Microsoft ODBC Driver for SQL Server - 版本17及以上
• .NET Framework Data Provider for SQL Server - 版本4.6.1及以上

确保客户端应用程序使用的驱动版本符合要求...

5. 流程图：启用Always Encrypted的步骤

graph TD;
            A[开始] --> B{选择密钥存储};
            B --Windows证书存储--> C[创建CMK];
            B --Azure Key Vault--> D[配置AKV];
            C --> E[创建CEK];
            D --> E;
            E --> F[配置客户端访问];
            F --> G[测试查询];