SQL Server 2008 透明資料加密 (TDE) 實作實戰：從備份檔加密到還原驗證

實作 TDE 保護資料庫備份檔 (.bak/.mdf/.ldf) 防止外洩被掛載竊取。本文以 Northwind 範例資料庫示範完整流程：建立金鑰階層、啟用加密、驗證備份檔加密、跨實例還原測試。

實作步驟

1. 建立主要金鑰與憑證 (master)：

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Str0ngP@ssw0rd!';
CREATE CERTIFICATE TDE_Cert WITH SUBJECT = 'TDE Certificate for Northwind';

2. 目標資料庫建立 DEK 並啟用加密：

USE Northwind;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;
ALTER DATABASE Northwind SET ENCRYPTION ON;

3. 驗證加密狀態：

SELECT name, is_encrypted FROM sys.databases WHERE name = 'Northwind';
-- is_encrypted = 1

4. 建立未加密備份對照：

BACKUP DATABASE Northwind TO DISK = 'C:\Backup\Northwind_UNENCRYPTED.bak' WITH INIT;
-- 用記事本開啟搜尋 'Aria Cruz' 可見明文

5. 啟用 TDE 後建立加密備份：

BACKUP DATABASE Northwind TO DISK = 'C:\Backup\Northwind_TDE.bak' WITH INIT;
-- 用記事本開啟搜尋 'Aria Cruz' 亂碼不可讀

跨實例還原測試 (關鍵驗證)

1. 於**另一實例**匯入憑證 (含私鑰)：

-- 來源實例先備份憑證
BACKUP CERTIFICATE TDE_Cert
TO FILE = 'C:\Certs\TDE_Cert.cer'
WITH PRIVATE KEY (
    FILE = 'C:\Certs\TDE_Cert.pvk',
    ENCRYPTION BY PASSWORD = 'Str0ngP@ssw0rd!'
);

-- 目標實例建立主要金鑰 (若無)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Str0ngP@ssw0rd!';
-- 匯入憑證
CREATE CERTIFICATE TDE_Cert
FROM FILE = 'C:\Certs\TDE_Cert.cer'
WITH PRIVATE KEY (
    FILE = 'C:\Certs\TDE_Cert.pvk',
    DECRYPTION BY PASSWORD = 'Str0ngP@ssw0rd!'
);

2. 還原加密備份：RESTORE DATABASE Northwind FROM DISK = 'C:\Backup\Northwind_TDE.bak'; — 成功。
3. 若**未匯入憑證**嘗試還原 → 錯誤：Msg 33111: Cannot find server certificate with thumbprint ...

效能觀測

• 啟用 TDE 後 CPU 額外負擔 ~3~5% (AES-NI 硬體加速下更低)。
• 備份壓縮率下降 (加密資料熵高)；建議 WITH COMPRESSION 先壓後加 (SQL 2008 支援)。

金鑰管理清單

• 憑證私鑰備份離線、異地保存。
• 主要金鑰密碼存入密碼管理系統。
• 定期輪換憑證 (建議每年) 並同步更新所有 DR 站點。
• 納入災難復原演練：模擬憑證遺失、重建金鑰階層、還原加密備份。

結語

TDE 以最小改動提供靜態加密，核心在於**金鑰階層建立正確**與**憑證備份/輪換/異地保存流程落地**。跨實例還原測試是驗證整個保護鏈完整性的唯一標準。