SQL Server中如何使用对称密钥加密保护敏感数据？

在SQL Server中，对称密钥加密是保护表中敏感字段的常用手段。它使用同一个密钥进行加密和解密，计算开销小，适合批量数据处理。下面介绍完整的配置与使用流程。

一、准备密钥体系

对称密钥依赖数据库主密钥（Master Key）和证书或密码来保护自身。首先需要在目标数据库创建主密钥：

-- 创建数据库主密钥，使用密码加密
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPwd_123!';

-- 可选：创建证书用于加密对称密钥
CREATE CERTIFICATE Cert_ProtectKey
WITH SUBJECT = 'Certificate for Symmetric Key';

二、创建对称密钥

使用 CREATE SYMMETRIC KEY 语句生成密钥，并指定加密算法与保护方式。推荐采用AES_256算法：

-- 基于证书创建对称密钥
CREATE SYMMETRIC KEY SymKey_Demo
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE Cert_ProtectKey;

三、加密和解密列数据

写入或读取加密列时，必须先打开密钥。以下示例演示如何加密敏感信息并还原：

-- 建测试表
CREATE TABLE UserSecret (
    Id INT IDENTITY,
    Name NVARCHAR(50),
    IdCard VARBINARY(256)
);

-- 打开对称密钥后插入加密数据
OPEN SYMMETRIC KEY SymKey_Demo
DECRYPTION BY CERTIFICATE Cert_ProtectKey;

INSERT INTO UserSecret (Name, IdCard)
VALUES ('张三', ENCRYPTBYKEY(KEY_GUID('SymKey_Demo'), '110105199001011234'));

-- 查询并解密
SELECT Id, Name,
       CAST(DECRYPTBYKEY(IdCard) AS NVARCHAR(50)) AS IdCard_Plain
FROM UserSecret;

CLOSE SYMMETRIC KEY SymKey_Demo;

核心函数说明

• KEY_GUID：获取密钥的GUID，供加密函数引用。
• ENCRYPTBYKEY：接受密钥GUID和明文，返回二进制密文。
• DECRYPTBYKEY：在密钥打开状态下将密文还原为明文。

四、备份与恢复注意事项

主密钥和证书必须单独备份，否则数据库还原到新实例后将无法解密已有数据：

-- 备份证书及其私钥
BACKUP CERTIFICATE Cert_ProtectKey
TO FILE = 'C:bakCert_ProtectKey.cer'
WITH PRIVATE KEY (
    FILE = 'C:bakCert_ProtectKey.pvk',
    ENCRYPTION BY PASSWORD = 'BackupPwd_456!'
);

注意：对称密钥本身随数据库移动，但保护它的证书私钥需人工迁移，二者缺一不可。

五、常见误区

有些开发者把密钥密码写进存储过程明文里，这等于未加密。应将密钥保护交给证书，并限制证书与密钥的查看权限，仅授权给必要账号。

合理运用对称密钥加密，能在几乎不影响业务性能的前提下，显著降低敏感数据泄露风险。

SQL_Serversymmetric_keyencryption修改时间：2026-07-31 10:12:23