SQL Server 2008 憑證加密完整實作：建立、備份、還原、加解密與對稱金鑰整合

前言

憑證 同時擁有公開金鑰（加密）與私鑰（解密），支援匯出/匯入檔案，比非對稱金鑰更易跨資料庫移植。本文完整示範憑證的建立、查詢、備份還原、私鑰管理、資料加解密，以及搭配對稱金鑰使用 DecryptByKeyAutoCert 簡化解密流程。

範例一：建立資料庫憑證

USE DB_Encrypt_Demo; GO
CREATE CERTIFICATE cert_Demo
ENCRYPTION BY PASSWORD = 'asdfG!!!'
WITH SUBJECT = 'DB_Encrypt_Demo Database Encryption Certificate',
START_DATE = '2011-03-14', EXPIRY_DATE = '2012-10-20'; GO

範例二：查看憑證

USE DB_Encrypt_Demo; GO
SELECT name, pvt_key_encryption_type_desc, issuer_name
FROM sys.certificates;

範例三：備份與還原憑證

-- 備份
BACKUP CERTIFICATE cert_Demo
TO FILE = 'H:\SqlBackup\certDemo.BAK'
WITH PRIVATE KEY (
    FILE = 'H:\SqlBackup\certDemoPK.BAK',
    ENCRYPTION BY PASSWORD = '1234GH!!!',
    DECRYPTION BY PASSWORD = 'asdfG!!!'); GO

-- 刪除後還原
DROP CERTIFICATE cert_Demo; GO
CREATE CERTIFICATE cert_Demo
FROM FILE = 'H:\SqlBackup\certDemo.BAK'
WITH PRIVATE KEY (
    FILE = 'H:\SqlBackup\certDemoPK.BAK',
    DECRYPTION BY PASSWORD = '1234GH!!!',
    ENCRYPTION BY PASSWORD = 'asdfG!!!'); GO

範例四：管理私鑰

-- 移除私鑰
ALTER CERTIFICATE cert_Demo REMOVE PRIVATE KEY; GO

-- 從備份檔重新加入私鑰
ALTER CERTIFICATE cert_Demo
WITH PRIVATE KEY (
    FILE = 'H:\SqlBackup\certDemoPK.BAK',
    DECRYPTION BY PASSWORD = '1234GH!!!',
    ENCRYPTION BY PASSWORD = 'asdfG!!!'); GO

-- 變更私鑰密碼
ALTER CERTIFICATE cert_Demo
WITH PRIVATE KEY (
    DECRYPTION BY PASSWORD = 'asdfG!!!',
    ENCRYPTION BY PASSWORD = 'mynewpassword!!!13E'); GO

範例五：憑證加解密資料

-- 建立測試表
CREATE TABLE dbo.PWDQuestion (
    CustomerID int PRIMARY KEY,
    PasswordHintQuestion nvarchar(300),
    PasswordHintAnswer varbinary(200)
); GO

-- 加密寫入
INSERT dbo.PWDQuestion (CustomerID, PasswordHintQuestion, PasswordHintAnswer)
VALUES (10, N'您出生的醫院名稱?',
        EncryptByCert(Cert_ID('cert_Demo'), N'北京四合院家中')); GO

-- 解密讀取 (需提供私鑰密碼)
SELECT PasswordHintQuestion,
       CAST(DecryptByCert(Cert_ID('cert_Demo'), PasswordHintAnswer, N'mynewpassword!!!13E') AS varchar(200)) AS PasswordHintAnswer
FROM dbo.PWDQuestion WHERE CustomerID = 10;

範例六：搭配對稱金鑰使用 DecryptByKeyAutoCert

對稱金鑰加密效能佳，但每次解密需先 OPEN SYMMETRIC KEY。SQL Server 提供 DecryptByKeyAutoCert 將「開啟金鑰」與「解密」合為單一陳述式：

-- 建立非對稱金鑰保護對稱金鑰
CREATE ASYMMETRIC KEY asymDemo_V2 WITH ALGORITHM = RSA_512; GO
CREATE SYMMETRIC KEY sym_Demo_V2
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY ASYMMETRIC KEY asymDemo_V2; GO

-- 寫入加密資料
OPEN SYMMETRIC KEY sym_Demo_V2 DECRYPTION BY ASYMMETRIC KEY asymDemo_V2;
INSERT dbo.PWDQuestion (CustomerID, PasswordHintQuestion, PasswordHintAnswer)
VALUES (22, N'您出生的醫院名稱?', EncryptByKey(Key_GUID('sym_Demo_V2'), N'邵逸夫醫院'));
CLOSE SYMMETRIC KEY sym_Demo_V2; GO

-- 單一陳述式解密
SELECT CAST(DecryptByKeyAutoAsymKey(ASYMKEY_ID('asymDemo_V2'), NULL, PasswordHintAnswer) AS varchar)
FROM dbo.PWDQuestion WHERE CustomerID = 22;

小結

1. 憑證具備可攜性（可備份/還原檔案），適合跨資料庫重複使用。
2. 憑證加解密資源消耗大於對稱金鑰，建議用於保護對稱金鑰或小量敏感資料。
3. DecryptByKeyAutoCert / DecryptByKeyAutoAsymKey 可簡化解密程式碼，提升可維護性。