SQL Server Backup Encryption 資料庫備份的加密方式

2021-03-14

筆記 SQL Server 如何將備份檔進行加密，用以保護資料庫備份檔不被任意還原。

說明

加密的起手式就是先建立 Server Master Key (Database Master Key)，接著在建立憑證，最後產生對稱式金鑰來進行加密，不論是 TDE、Always Encrypt 或者備份加密，都是依循此基本原理。

而將備份檔進行加密的好處在於避免備份檔流出時，惡意第三方可以任意地將資料庫還原，使資料流出，而在作業成本上則是多了需要管理加解密備份檔的所使用的憑證。

建立 Server Master Key

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd';

不同於對稱式或非對稱式用於加密資料庫欄位，Master Key 是跟隨在各 Database 上，用於備份檔加密的 Master Key 是跟隨在 Master Database。

SQL Server Instance 只能擁有單一個 Master Key，重複建立的話會有錯誤訊息。而要確認是否已經擁有 Master Key 可以使用下列指令查詢：

SELECT * FROM sys.symmetric_keys;

如果有建立 Master Key 可以查詢到 ##MS_DatabaseMasterKey##，此外 ##MS_ServiceMasterKey## 則是建立 Instance 時會自動建立的 Key。

建立憑證

加密備份檔的憑證也是儲存於 Master Database。

CREATE CERTIFICATE AdventureWorksCert
    WITH SUBJECT = 'AdventureWorks Database Backup Certificate';
GO

憑證也可以自行使用密碼加密，但會無法用於加密備份檔，因此使用密碼加密的情境適用於加密資料庫欄位或者待驗證是否可以用於 TDE (Transparent Data Encryption) 加密上。

CREATE CERTIFICATE AdventureWorksCertWP
	ENCRYPTION BY PASSWORD = 'Pa$$w0rdCert'  
    WITH SUBJECT = 'AdventureWorks Backup Certificate With Passwords';

備份憑證

在使用憑證進行備份檔的加密之前，務必先將憑證進行備份，以免憑證遺失造成備份檔無法復原，此外如果沒有備份憑證，再將備份檔進行加密的時候，系統也會主動的警示憑證尚未備份。

BACKUP CERTIFICATE AdventureWorksCert TO FILE = 'C:\Temp\AdventureWorksCert.cert'
WITH PRIVATE KEY (
FILE = 'C:\Temp\AdventureWorksCert.key',
ENCRYPTION BY PASSWORD = 'Pa$$w0rd') -- 指定憑證私鑰檔案的密碼

自行使用密碼加密的憑證，備份的方式如下

BACKUP CERTIFICATE AdventureWorksCertWP TO FILE = 'C:\Temp\AdventureWorksCertWP.cert'
WITH PRIVATE KEY (
FILE = 'C:\Temp\AdventureWorksCertWP.key',
ENCRYPTION BY PASSWORD = 'Pa$$w0rd', -- 指定憑證私鑰檔案的密碼
DECRYPTION BY PASSWORD = 'Pa$$w0rdCert') -- 建立憑證時所採用的密碼

備份資料庫並使用憑證加密

加密備份檔所使用的憑證有一個限制，就是憑證必須是以 Server Master Key 所加密的，如果是以密碼加密的憑證無法用於加密備份檔上。如上述的憑證 AdventureWorksCertWP ，如果用於加密備份檔，可以見錯誤訊息：

使用憑證對資料庫備份檔加密的方式如下：

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Temp\AdventureWorks.bak'
WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = AdventureWorksCert)

如果使用 SSMS GUI 來操作的話，必須勾選備份至新的媒體集才能夠進行備份檔的加密。

還原憑證

CREATE CERTIFICATE AdventureWorksCert
FROM FILE = 'C:\Temp\AdventureWorksCert.cert'
WITH PRIVATE KEY (FILE = 'C:\Temp\AdventureWorksCert.key',
DECRYPTION BY PASSWORD = 'Pa$$w0rd'); -- 使用憑證私鑰檔案的密碼

還原資料庫

如果有正確還原加密備份檔的憑證，則在還原資料庫的時候不需要特別操作，反之如果沒有還原加密備份檔的憑證，再還原資料庫時就會出現錯誤訊息。

RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backup\AdventureWorks.bak';

小結

加密備份檔的方式並不複雜，只需要做好憑證檔的管理，以免無法正確還原。此外用於備份檔加密的憑證檔，必須使用 Server Master Key 加密而無法另行以指定密碼的方式產生，否則會無法加密備份檔。

此外建立憑證時會有預設的到期日期，如果憑證到期了並不會影響資料庫的備份檔案還原，這一點可以放心 😏

參考資料

CREATE CERTIFICATE (Transact-SQL)

BACKUP CERTIFICATE (Transact-SQL)

Linkedin Learning

相關連結

SQL Server 閃電般快速查詢指南⚡

SQL Server 周邊工具彙整筆記

SQL Server 學習資源筆記

後續應用

比較 TDE 以及 Always Encrypt 的加密方式