SQL Server 透明數據加密（TDE）與列級加密實戰：從主鑰到證書的完整鏈路

SQL Server 加密體系呈層級結構：**Windows DPAPI → 服務主鑰 (SMK) → 數據庫主鑰 (DMK) → 證書/非對稱鑰/對稱鑰 → 數據**。本文通過完整腳本演示：創建 DMK、非對稱鑰、對稱鑰、證書，並以對稱鑰加密列數據為例，展示 ENCRYPTBYKEY / DECRYPTBYKEY 實戰流程。

加密層級架構

Windows DPAPI (保護 SMK)
    ↓
Service Master Key (SMK) - 安裝時自動生成
    ↓ 保護
Database Master Key (DMK) - CREATE MASTER KEY
    ↓ 保護
┌─ Certificate (證書) ──→ 對稱鑰
├─ Asymmetric Key (非對稱鑰) ──→ 對稱鑰
└─ Symmetric Key (對稱鑰) ─────→ 數據 (ENCRYPTBYKEY)
支持算法：AES_128/192/256, TRIPLE_DES, DESX (棄用), RC4 (棄用)
非對稱：RSA_512/1024/2048

完整實戰腳本

1. 創建數據庫主鑰 (DMK)

CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'Passw0rd'; -- 受 SMK 保護
GO

2. 創建三類鑰材

-- 非對稱鑰 (RSA_1024)
CREATE ASYMMETRIC KEY asyc_key_enc 
WITH ALGORITHM = RSA_1024 
ENCRYPTION BY PASSWORD = N'Pass@word';
GO

-- 對稱鑰 (Triple_DES，密碼保護)
CREATE SYMMETRIC KEY symc_key_enc 
WITH ALGORITHM = TRIPLE_DES 
ENCRYPTION BY PASSWORD = N'Pass@word';
GO

-- 證書 (自簽名，有效期至 2099)
CREATE CERTIFICATE cert_ENC 
WITH SUBJECT = 'certificate for ENC', 
EXPIRY_DATE = '20990101';
GO

3. 對稱鑰的多重保護方式（三選一或組合）

-- 3.1 由非對稱鑰保護
CREATE SYMMETRIC KEY symc_key_enc_byAsyc 
WITH ALGORITHM = AES_128 
ENCRYPTION BY ASYMMETRIC KEY asyc_key_enc;
GO

-- 3.2 由對稱鑰保護（需先 OPEN 父鑰）
OPEN SYMMETRIC KEY symc_key_enc DECRYPTION BY PASSWORD = N'Pass@word';
CREATE SYMMETRIC KEY symc_key_enc_bySymc 
WITH ALGORITHM = DES 
ENCRYPTION BY SYMMETRIC KEY symc_key_enc;
CLOSE SYMMETRIC KEY symc_key_enc;
GO

-- 3.3 由證書保護（推薦：證書可備份/還原/輪換）
CREATE SYMMETRIC KEY symc_key_enc_byCert 
WITH ALGORITHM = AES_128 
ENCRYPTION BY CERTIFICATE cert_ENC;
GO

4. 列級加密與解密演示（以 3.1 非對稱鑰保護的對稱鑰為例）

-- 建表與測試數據
CREATE TABLE tb (IDN INT, Name VARCHAR(20));
INSERT INTO tb VALUES (123456789,'BigBrother'),(090807001,'SpiderMan'),(336655789,'SuperMan');
GO

-- 新增加密列 (VARBINARY 存儲密文)
ALTER TABLE tb ADD Ency_IDN VARBINARY(128);
GO

-- 加密：OPEN 鑰 → ENCRYPTBYKEY(KEY_GUID('鑰名'), 明文二進制) → CLOSE 鑰
OPEN SYMMETRIC KEY symc_key_enc_byAsyc 
DECRYPTION BY ASYMMETRIC KEY asyc_key_enc 
WITH PASSWORD = N'Pass@word';

UPDATE tb 
SET Ency_IDN = ENCRYPTBYKEY(KEY_GUID('symc_key_enc_byAsyc'), CONVERT(VARBINARY, IDN));

CLOSE SYMMETRIC KEY symc_key_enc_byAsyc;
GO

-- 解密：OPEN 鑰 → DECRYPTBYKEY(密文) → CONVERT 回目標類型 → CLOSE 鑰
OPEN SYMMETRIC KEY symc_key_enc_byAsyc 
DECRYPTION BY ASYMMETRIC KEY asyc_key_enc 
WITH PASSWORD = N'Pass@word';

SELECT 
    IDN, 
    Ency_IDN, 
    CONVERT(INT, DECRYPTBYKEY(Ency_IDN)) AS Decr_IDN 
FROM tb;

CLOSE SYMMETRIC KEY symc_key_enc_byAsyc;
GO

四對加密/解密函數速查

關鍵最佳實踐

1. 被加密列必須為 VARBINARY，長度 ≥ 明文長度 + 算法填充開銷（AES_128 建議明文長 + 16 字節）。
2. 鑰材備份鏈：DMK 備份 → 證書備份 (WITH PRIVATE KEY) → 對稱鑰無需單獨備份（受證書保護）。災難恢復時按序還原。
3. 避免棄用算法：RC4, DES, DESX 在新版本已移除/禁止，統一用 AES 系列。
4. OPEN/CLOSE 成對使用，會話級別生效，存儲過程結束自動關閉，但顯式 CLOSE 更安全。
5. 索引加密列：加密後列失去語義有序性，若需篩選，考慮保留明文哈希列（如 SHA2_256）建索引，查詢時比對哈希。

列級加密保護靜態數據機密性，配合 TDE（透明數據加密）保護整庫文件，構建纵深防禦體系。