SQL Server 資料加密完整體系：非對稱金鑰、憑證、對稱金鑰、TDE 與通行片語深度解析

SQL Server 提供階層式加密架構，從服務主金鑰 (SMK) 到資料庫主金鑰 (DMK)，再到非對稱金鑰、憑證、對稱金鑰，以及透明資料加密 (TDE)。本文系統梳理各層級加密物件的建立、管理、備份還原與實戰應用，並附完整 T-SQL 範本。

加密階層架構總覽

服務主金鑰 (SMK) - 執行個體層級，安裝時自動建立，受 Windows DPAPI 保護
  └─ 資料庫主金鑰 (DMK) - 資料庫層級，由 SMK 加密，可選密碼保護
       ├─ 非對稱金鑰 - 公私鑰對，RSA 算法，高安全低效能
       ├─ 憑證 - 含公開/私鑰、主體資訊、有效期，可備份匯出，可移植性佳
       ├─ 對稱金鑰 - 單一金鑰加解密，AES/Triple DES，高效能，適合大量資料
       └─ 透明資料加密 (TDE) - 資料庫檔案層級即時加密，應用程式零修改
通行片語 - 無需建立物件，即時衍生金鑰，適合臨時/簡單加密需求

一、非對稱金鑰

建立與管理

-- 建立 RSA_2048 非對稱金鑰，受密碼保護
CREATE ASYMMETRIC KEY AsymKey_App
    WITH ALGORITHM = RSA_2048
    ENCRYPTION BY PASSWORD = N'Str0ngP@ss!';

-- 檢視
SELECT name, algorithm_desc, pvt_key_encryption_type_desc
FROM sys.asymmetric_keys;

-- 修改私鑰密碼
ALTER ASYMMETRIC KEY AsymKey_App
WITH PRIVATE KEY (
    DECRYPTION BY PASSWORD = N'Str0ngP@ss!',
    ENCRYPTION BY PASSWORD = N'NewP@ssw0rd!'
);

-- 刪除
DROP ASYMMETRIC KEY AsymKey_App;

加解密實戰

-- 建立測試表，加密欄位用 VARBINARY
CREATE TABLE dbo.SensitiveData (
    ID INT IDENTITY PRIMARY KEY,
    EncryptedSSN VARBINARY(256) NOT NULL,
    CreatedAt DATETIME2 DEFAULT SYSDATETIME()
);

-- 加密插入 (公鑰加密)
INSERT INTO dbo.SensitiveData (EncryptedSSN)
VALUES (ENCRYPTBYASYMKEY(ASYMKEY_ID(N'AsymKey_App'), N'A123456789'));

-- 解密查詢 (需私鑰密碼)
SELECT 
    ID,
    CAST(DECRYPTBYASYMKEY(ASYMKEY_ID(N'AsymKey_App'), EncryptedSSN, N'NewP@ssw0rd!') AS NVARCHAR(20)) AS SSN
FROM dbo.SensitiveData;

特性：RSA 加密資料長度受限 (RSA_2048 最大 245 bytes)，適合小量高敏感資料 (金鑰、密碼、憑證)，不適合大欄位。

二、憑證

建立與備份還原 (可跨資料庫/伺服器移植)

-- 建立憑證
CREATE CERTIFICATE Cert_AppData
    ENCRYPTION BY PASSWORD = N'CertP@ss!'
    WITH SUBJECT = N'Application Data Encryption Certificate',
         START_DATE = '2024-01-01',
         EXPIRY_DATE = '2030-12-31';

-- 備份到檔案 (含私鑰)
BACKUP CERTIFICATE Cert_AppData
TO FILE = N'D:\Backup\Cert_AppData.cer'
WITH PRIVATE KEY (
    FILE = N'D:\Backup\Cert_AppData.pvk',
    ENCRYPTION BY PASSWORD = N'BakP@ss!'
);

-- 異地還原
CREATE CERTIFICATE Cert_AppData
FROM FILE = N'D:\Backup\Cert_AppData.cer'
WITH PRIVATE KEY (
    FILE = N'D:\Backup\Cert_AppData.pvk',
    DECRYPTION BY PASSWORD = N'BakP@ss!'
);

加解密實戰

-- 加密
INSERT INTO dbo.PWDQuestion (CustomerID, Question, Answer)
VALUES (1001, N'母親娘家姓?', ENCRYPTBYCERT(CERT_ID(N'Cert_AppData'), N'陳'));

-- 解密
SELECT 
    Question,
    CAST(DECRYPTBYCERT(CERT_ID(N'Cert_AppData'), Answer, N'CertP@ss!') AS NVARCHAR(100)) AS Answer
FROM dbo.PWDQuestion
WHERE CustomerID = 1001;

優勢：可備份/還原跨伺服器部署、支援有效期管理、比非對稱金鑰易管理。

三、對稱金鑰 (大量資料首選)

建立：以憑證/非對稱金鑰保護對稱金鑰

-- 1. 建立保護用憑證
CREATE CERTIFICATE Cert_SymProtector
    ENCRYPTION BY PASSWORD = N'ProtP@ss!'
    WITH SUBJECT = N'Symmetric Key Protector';

-- 2. 建立 AES_256 對稱金鑰，受憑證保護
CREATE SYMMETRIC KEY SymKey_AES256
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Cert_SymProtector;

使用模式：開啟 → 加解密 → 關閉

-- 加密寫入
OPEN SYMMETRIC KEY SymKey_AES256
    DECRYPTION BY CERTIFICATE Cert_SymProtector;

INSERT INTO dbo.LargeTable (EncryptedCol)
VALUES (ENCRYPTBYKEY(KEY_GUID(N'SymKey_AES256'), N'大量敏感文字資料...'));

CLOSE SYMMETRIC KEY SymKey_AES256;

-- 解密讀取
OPEN SYMMETRIC KEY SymKey_AES256
    DECRYPTION BY CERTIFICATE Cert_SymProtector;

SELECT 
    ID,
    CAST(DECRYPTBYKEY(EncryptedCol) AS NVARCHAR(MAX)) AS DecryptedData
FROM dbo.LargeTable
WHERE ID = 12345;

CLOSE SYMMETRIC KEY SymKey_AES256;

自動開啟解密 (SQL 2008+)：單一陳述式完成

-- 以非對稱金鑰保護對稱金鑰
CREATE ASYMMETRIC KEY Asym_SymProtect WITH ALGORITHM = RSA_2048;
CREATE SYMMETRIC KEY Sym_Auto WITH ALGORITHM = AES_256
    ENCRYPTION BY ASYMMETRIC KEY Asym_SymProtect;

-- 一次查詢自動開啟並解密
SELECT 
    CAST(DECRYPTBYKEYAUTOASYMKEY(ASYMKEY_ID(N'Asym_SymProtect'), NULL, EncryptedCol) AS NVARCHAR(MAX))
FROM dbo.LargeTable;

四、透明資料加密 (TDE) - Enterprise Edition

對資料庫檔案 (MDF/LDF/備份) 即時加密，應用程式完全無感。

啟用 TDE 完整流程

-- 1. master 建立 DMK (若無)
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'DMK_P@ss!';

-- 2. 建立 TDE 保護憑證
CREATE CERTIFICATE TDE_Cert
    WITH SUBJECT = N'TDE Certificate for Production DB';

-- 3. 目標資料庫建立 DEK (資料庫加密金鑰)
USE [ProductionDB];
CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;

-- 4. 開啟加密
ALTER DATABASE [ProductionDB] SET ENCRYPTION ON;

-- 5. 驗證
SELECT name, is_encrypted FROM sys.databases WHERE name = N'ProductionDB';

⚠️ 關鍵警告：憑證備份決定生死

-- 必須立即備份 TDE 憑證與私鑰！
BACKUP CERTIFICATE TDE_Cert
TO FILE = N'D:\SecureBackup\TDE_Cert.cer'
WITH PRIVATE KEY (
    FILE = N'D:\SecureBackup\TDE_Cert.pvk',
    ENCRYPTION BY PASSWORD = N'OffsiteP@ss!'
);

遺失憑證 = 永久無法還原/附加資料庫。異地備份、離線保存、定期演練還原是鐵律。

五、通行片語 - 最簡單加密

-- 無需建立物件，直接加密
INSERT INTO dbo.Secrets (Data)
VALUES (ENCRYPTBYPASSPHRASE(N'MySecretPhrase!', N'機密內容'));

-- 解密
SELECT CAST(DECRYPTBYPASSPHRASE(N'MySecretPhrase!', Data) AS NVARCHAR(MAX))
FROM dbo.Secrets;

適用：臨時加密、應用層管理金鑰、不想在 DB 建立金鑰物件。缺點：無金鑰輪換機制、片語外洩即全盤洩露、無稽核追蹤。

加密策略選擇決策表

金鑰管理最佳實務

1. 分層保護：SMK → DMK → 憑證/非對稱 → 對稱 → 資料
2. 定期輪換：對稱金鑰 90-180 天、憑證 1-2 年、TDE 憑證依合規週期
3. 備份與離線：所有金鑰/憑證備份加密存放於異地離線儲存
4. 最小權限：應用布號僅授 VIEW DEFINITION、CONTROL 於必要金鑰
5. 稽核：啟用 AUDIT 追蹤 OPEN/CLOSE KEY、BACKUP/RESTORE CERTIFICATE

SQL Server 加密體系完整且成熟，關鍵在於依據資料敏感度分級、選擇正確層級、建立完整金鑰生命週期管理流程。TDE 解決合規「靜態加密」勾選框，欄位級加密解決真正的資料保護需求，兩者互補而非互斥。