sqlserver 启用 Always Encrypted 始终加密

SQL Server 2016 首次引入的新特性 Always Encrypted 允许用户的敏感数据在应用端加密、解密，保证存储在云端数据库中的数据永远保持加密状态（只有调用SQL Server的应用才能读写），即便是云服务提供商也看不到数据库中的明文数据，最大限制保证用户数据安全，彻底解决客户对云服务提供商的信任问题。它对应用程序和用户是透明的，由安装了驱动的客户端自动完成。

如何启用某张表的始终加密

  • 第1步，创建测试数据库并调整排序规则，示例 ae_demo

注：中文环境推荐 Chinese_PRC_BIN2（区分大小写和重音），英文环境推荐 Latin1_General_BIN2

USE master
IF DB_ID('ae_demo') IS NULL
CREATE DATABASE ae_demo
GO

--.修改排序规则
ALTER DATABASE ae_demo COLLATE Chinese_PRC_BIN2
  • 第2步，创建 列主密钥（Column Master Key, 简称CMK），示例 AE_ColumnMasterKey

注：生产环境建议用SSMS向导生成CMK+CEK，避免手动或脚本踩坑。密钥存储建议“Windows 证书存储 - 本地计算机”，注意确定当前密钥对应的指纹

注：密钥路径为“本地计算机”的注册表地址为（HKLM\SOFTWARE\Microsoft\SystemCertificates\MY\Certificates），密钥路径为“当前用户”的注册表地址为（HKCU\Software\Microsoft\SystemCertificates\CA\Certificates）

  • 第3步，创建 列加密密钥（Column Encryption Key, 简称CEK），示例 AE_ColumnEncryptionKey

注：这里要关联上一步创建的列主密钥（AE_ColumnMasterKey）

  • 第4步，创建 Always Encryped 测试表

注：被加密的列如果是字符类型（char/varchar/nchar/nvarchar）则必须指定排序规则（中文推荐 Chinese_PRC_BIN2，英文推荐 Latin1_General_BIN2），而非字符串类型（int/datetime/money等）则不需要指定

USE ae_demo
IF OBJECT_ID('dbo.Employees', 'U') IS NOT NULL
DROP TABLE dbo.Employees
GO

CREATE TABLE dbo.Employees (
  id bigint IDENTITY(1000,1) NOT NULL PRIMARY KEY,
  UserName nvarchar(100),
  iPhone varchar(18) COLLATE Chinese_PRC_BIN2 
    ENCRYPTED WITH (
      ENCRYPTION_TYPE = DETERMINISTIC,   --.确定性密钥
      ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
      COLUMN_ENCRYPTION_KEY = AE_ColumnEncryptionKey
    ) NOT NULL,
  Salary money 
    ENCRYPTED WITH (
      ENCRYPTION_TYPE = RANDOMIZED,    --.随机密钥
      ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
      COLUMN_ENCRYPTION_KEY = AE_ColumnEncryptionKey
    ) NOT NULL
)

-- 直接INSERT会报 操作数类型冲突
-- INSERT INTO Employees (UserName, iPhone, Salary) VALUES ('tom', '13012345678', 6000)
SELECT * FROM Employees
  • 第5步，确认密钥信息，并查看哪些表启用了始终加密
--.查看密钥信息
SELECT cmk.name as cmk_name, cek.name as cek_name,cek.create_date as cek_create_date,cmk.key_store_provider_name,cmk.key_path
FROM sys.column_encryption_key_values cev
INNER JOIN sys.column_master_keys cmk ON cev.column_master_key_id = cmk.column_master_key_id
INNER JOIN sys.column_encryption_keys cek ON cev.column_encryption_key_id = cek.column_encryption_key_id

--.查看哪些表启用了始终加密
SELECT schema_name(st.schema_id) as schema_name, st.name AS table_name, sc.name AS column_name, sc.encryption_type_desc
FROM sys.columns sc INNER JOIN sys.tables st ON sc.object_id = st.object_id 
WHERE sc.encryption_type IS NOT NULL

测试与验收

证书的导出与导入

  • 第1步，在数据库服务器上运行 certlm.msc 打开“证书-本地计算机”控制台，依次选择“个人 → 证书”确认证书，示例 Always Encrypted Certificate

注：通过ssms生成的证书，会同时创建到个人及中间证书颁发机构，但以个人\证书为准。若这里有多个同名证书，可根据指纹来确定具体使用哪个。

  • 第2步，选中该证书，右键选择“所有任务 → 导出”

  • 第3步，导出过程参考如下：

  • 第4步，在第三方SSMS客户端或应用程序服务器上运行 certlm.msc 打开“证书-本地计算机”控制台，然后导入证书文件

通过 SSMS 客户端测试

  • 第1步，登录到第三方安装了SSMS客户端的服务器（示例 192.168.31.103），将数据库服务器的证书导入本机，过程可参考上面的章节
  • 第2步，打开 SSMS 连接到实例（示例 192.168.31.104）的时候，在“始终加密”标签页勾选“启用 Always Encrypted (列加密)”

注：无 Enclave 只用勾选“启用始终加密 (列加密)”，有 Enclave (SQL2019+) 同时勾选“启用安全 Enclave”

  • 第3步，先打开一个查询窗口，然后依次选择“查询 -> 查询选项”，在新打开的窗口勾选“启用 Always Encrypted 参数化”并确定

  • 第4步，在查询窗口通过查询参数的方式进行INSERT（直接 INSERT 会报错）
USE ae_demo
GO 

--.直接 INSERT 会报错 操作数类型冲突
INSERT INTO Employees (UserName, iPhone, Salary) VALUES ('tom', '13012345678', 6000)

--.建议使用查询参数进行INSERT
DECLARE @UserName nvarchar(100) = 'tom'
DECLARE @iPhone varchar(18) = '13012345678'
DECLARE @Salary money = 6000
INSERT INTO Employees (UserName, iPhone, Salary) VALUES (@UserName, @iPhone, @Salary)

--.查看数据
SELECT * FROM Employees

  • 第5步，在启用了 Always Encrypted 参数化的查询窗口，可以查看明文数据，而在未启用的查询窗口只能看到密文数据

通过 APP 应用程序测试

  • 第1步，登录到应用程序服务器，将数据库服务器的证书导入本机，过程可参考上面的章节
  • 第2步，在应用程序的代码中，修改连接字符串，添加 Column Encryption Setting = Enabled;（否则读取密文）即可支持 Always Encrypted
#.NET连接字符串
"Data Source=192.168.31.101;Initial Catalog=ae_demo;Integrated Security=True;Column Encryption Setting=Enabled"

#.JDBC连接字符串
"jdbc:sqlserver://192.168.31.101:1433;databaseName=ae_demo;integratedSecurity=true;columnEncryptionSetting=Enabled;"

附录

限制条件

  • 必须使用查询参数：为加密列提供值时（如在 WHERE 子句中过滤或 INSERT 数据），必须使用参数化查询，不能直接使用字面量或常量
--.不支持
SELECT * FROM Employees where iPhone = '13012345678'

--.支持
DECLARE @iPhone varchar(18) = '13012345678'
SELECT * FROM Employees where iPhone = @iPhone
  • 排序规则限制：数据库排序规则必须是Latin1_General_BIN2或Chinese_PRC_BIN2（区分大小写），并且字符型（char/varchar/nchar/nvarchar）加密列的排序规则必须和库默认完全一致，非字符类型 (int/datetime/money) 无排序规则限制
  • 确定性加密（ENCRYPTION_TYPE=DETERMINISTIC）仅支持基于相等比较的操作（如 WHERE column = @value），不支持 LIKE、>、< 等模式匹配或范围查询，安全性较低，容易被频率分析攻击
USE ae_demo
TRUNCATE TABLE Employees
GO

--.准备3条测试数据
DECLARE @UserName nvarchar(100) = 'tom'
DECLARE @iPhone varchar(18) = '13012345678'
DECLARE @Salary money = 6000
INSERT INTO Employees (UserName, iPhone, Salary) VALUES (@UserName, @iPhone, @Salary)
GO

DECLARE @UserName nvarchar(100) = 'sam'
DECLARE @iPhone varchar(18) = '13112345678'
DECLARE @Salary money = 8000
INSERT INTO Employees (UserName, iPhone, Salary) VALUES (@UserName, @iPhone, @Salary)
GO

DECLARE @UserName nvarchar(100) = 'joy'
DECLARE @iPhone varchar(18) = '13212345678'
DECLARE @Salary money = 5000
INSERT INTO Employees (UserName, iPhone, Salary) VALUES (@UserName, @iPhone, @Salary)
GO
比较项 确定性加密 随机加密
SELECT查询
(全部支持)		 --.支持
SELECT UserName,iPhone FROM Employees		 --.支持
SELECT UserName,Salary FROM Employees
等值查询 --.支持
DECLARE @iPhone varchar(18) = '13012345678'
SELECT * FROM Employees where iPhone = @iPhone		 --.不支持
DECLARE @Salary money = 6000
SELECT * FROM Employees where Salary = @Salary
IN --.支持
DECLARE @num1 varchar(18) = '13012345678'
DECLARE @num2 varchar(18) = '13212345678'
SELECT * FROM Employees where iPhone IN(@num1,@num2)		 --.不支持
DECLARE @num1 money = 5000
DECLARE @num2 money = 6000
SELECT * FROM Employees where Salary IN(@num1,@num2)
GROUP BY --.支持
SELECT iPhone,COUNT(iPhone) FROM Employees GROUP BY iPhone		 --.不支持
SELECT Salary,SUM(Salary) FROM Employees GROUP BY Salary
DISTINCT --.支持
SELECT DISTINCT iPhone FROM Employees		 --.不支持
SELECT DISTINCT Salary FROM Employees
创建索引 --.支持
CREATE INDEX idx_iPhone ON Employees(iPhone)		 --.不支持
CREATE INDEX idx_Salary ON Employees(Salary)
> <
(全不支持)		 --.不支持
DECLARE @iPhone varchar(18) = '13012345678'
SELECT * FROM Employees where iPhone > @iPhone		 --.不支持
DECLARE @Salary money = 5000
SELECT * FROM Employees where Salary > @Salary
BETWEEN
(全不支持)		 --.不支持
DECLARE @beg varchar(18) = '13012345678'
DECLARE @end varchar(18) = '13212345678'
SELECT * FROM Employees WHERE iPhone BETWEEN @beg AND @end		 --.不支持
DECLARE @beg money = 5000
DECLARE @end money = 7000
SELECT * FROM Employees WHERE Salary BETWEEN @beg AND @end
LIKE
(全不支持)		 --.不支持
DECLARE @iPhone varchar(18) = '130%'
SELECT * FROM Employees where iPhone like @iPhone		 --.不支持
DECLARE @Salary money = 5000
SELECT * FROM Employees where Salary like @Salary
ORDER BY
(全不支持)		 --.不支持
SELECT * FROM Employees ORDER BY iPhone DESC		 --.不支持
SELECT * FROM Employees ORDER BY Salary DESC
适用场景 需要where查询就用确定性加密
比如：身份证号/手机号		 只需展示就用随机化加密
比如：薪资/余额
  • 随机加密（ENCRYPTION_TYPE=RANDOMIZED）安全性更高，但不支持除SELECT和UPDATE之外的任何运算
  • 不支持排序分组：不支持任何涉及加密列计算的SQL子句，如排序（ORDER BY）、分组（GROUP BY）等
  • 禁止混合数据运算：查询语句不能同时涉及加密数据和纯文本数据的计算。例如，直接将纯文本与加密列比较、使用 INSERT..SELECT / SELECT..INTO 将纯文本数据复制到加密列
  • 不支持表值参数：针对加密列，不能使用表值参数（Table-Valued Parameters）
  • 不支持的列属性：具有IDENTITY、ROWGUIDCOL属性的列，进行分区的列，包含默认约束或检查约束的列，使用稀疏列，被全文索引包含的列
  • 不支持的数据类型：不支持 xml, timestamp/rowversion, image, ntext, text, sql_variant, hierarchyid, geography, geometry, alias, user-defined
  • 约束限制：完全不支持外键，仅当使用确定性加密时，列才能作为主键或唯一约束
  • 索引与统计信息：随机加密列上不能创建索引，也不能被统计信息引用。确定性加密列可以创建索引，但意义有限（仅支持相等查找）
  • 不兼容的特性：复制、分布式查询（如链接服务器）、跨数据库查询（对来自不同数据库的加密列进行联接）以及内存优化表（若引用其列），但支持 AlwaysOn

如何禁用始终加密

  • 如果要禁用某个库的始终加密，其顺序与启用顺序刚好相反：

注：不建议删除证书控制台中的证书，留着也不碍眼。

--.第1步,找到所有启用了始终加密的表
USE ae_demo
GO
SELECT schema_name(st.schema_id) as schema_name, st.name AS table_name, sc.name AS column_name, sc.encryption_type_desc
FROM sys.columns sc INNER JOIN sys.tables st ON sc.object_id = st.object_id 
WHERE sc.encryption_type IS NOT NULL 

--.第2步,首先取消这些表的始终加密, 示例删除这些表
IF OBJECT_ID('dbo.Employees', 'U') IS NOT NULL
DROP TABLE dbo.Employees
GO

--.第3步,然后删除 CEK
IF EXISTS ( SELECT * FROM sys.column_encryption_keys WHERE name = 'AE_ColumnEncryptionKey')
DROP COLUMN ENCRYPTION KEY AE_ColumnEncryptionKey
GO

--.第4步,最后删除 CMK
IF EXISTS ( SELECT * FROM sys.column_master_keys WHERE name = 'AE_ColumnMasterKey')
DROP COLUMN MASTER KEY AE_ColumnMasterKey
GO

遇到的问题

  • 问题1：通过查询参数 INSERT 报错“无法预定义语句。在执行批处理时出现错误。错误消息为: Internal error.”
  • 原因1：这是因为加密列（Chinese_PRC_BIN2）与当前库（Chinese_PRC_CI_AS）的排序规则不一样
SELECT name, collation_name as column_collation, DATABASEPROPERTYEX(DB_NAME(), 'Collation') as database_collation
FROM sys.columns WHERE object_id = OBJECT_ID('Employees') AND name = 'iPhone'
  • 解决1：由于始终加密必须使用二进制代码点（_BIN2）排序规则，所以只能修改该库的排序规则，改为 Chinese_PRC_BIN2。如果加密列使用的是 Latin1_General_BIN2 则该库的排序规则需要改为 Latin1_General_BIN2
ALTER DATABASE ae_demo SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE ae_demo COLLATE Chinese_PRC_BIN2
ALTER DATABASE ae_demo SET MULTI_USER
Copyright © www.sqlfans.cn 2021 - 2026 All Right Reserved更新时间： 2026-09-08 14:40:43

results matching ""

    No results matching ""