[SQL] 備份加密 - 備份

SQL Server 2014 功能，僅針對備份檔案進行加密，確保離線資料安全，該篇根據官方文章 - 建立加密的備份 的操作筆記

備份檔案加密完整 Script

-- Step1：在 master database 上建立 master key
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd';
GO

-- Step2：建立憑證
CREATE CERTIFICATE AdventureWorksCert
    WITH SUBJECT = 'AdventureWorks Database Backup Certificate';
GO

-- Step3：進行備份檔案加密
BACKUP DATABASE AdventureWorks2022
TO DISK = 'C:\Temp\AdventureWorks2022.bak'
WITH
    INIT ,
    FORMAT ,
    COMPRESSION ,
    ENCRYPTION 
	(
		ALGORITHM = AES_256, 
		SERVER CERTIFICATE = AdventureWorksCert
	) ,
    STATS = 10 

Step1：在 master database 上建立 master key

master key 是一個用來保護憑證私密金鑰和資料庫中非對稱金鑰的對稱金鑰。 建立 master key　時，系統會利用 AES_256 演算法和使用者提供的密碼來加密主要金鑰

• 參考資料
• CREATE MASTER KEY
• Create a Database Master Key

Step2：建立憑證

使用 CREATE CERTIFICATE 語法建立憑證，有兩個重點參數要注意

＊ENCRYPTION BY PASSWORD

指定用來加密私密金鑰的密碼，不指定情況下會用 master key 來當成預設密碼

假如自行指定 ENCRYPTION BY PASSWORD，而非使用 master key，使用該憑證進行備份檔案加密時，會有下述錯誤訊息

無法使用 憑證 'CustomPasswordCert'，因為其私密金鑰不存在，或未受到資料庫主要金鑰保護。SQL Server 必須能夠自動存取這項作業所用之 憑證 的私密金鑰。BACKUP DATABASE 正在異常結束。

 

＊EXPIRY_DATE

憑證到期日期，為 UTC 時間，若未明確指定，預設為 START_DATE 加一年的日期

使用憑證進行加密的備份也會檢查到期日，而且將不會允許使用已過期憑證來建立新備份，但將會允許使用已過期的憑證來還原。

-- 確認 UTC 時間
SELECT GETUTCDATE()

-- 建立不久後就過期憑證
CREATE CERTIFICATE AdventureWorksCert_EXPIRY_DATE
WITH 
      SUBJECT = 'AdventureWorks Database Backup Certificate' ,
      EXPIRY_DATE = '2023-09-02 03:00:00.000';
GO

進行備份時就會出現下述錯誤訊息

為備份加密所指定的憑證已過期。BACKUP DATABASE 正在異常結束。

• 參考資料
• CREATE CERTIFICATE

Step3：進行備份檔案加密

BACKUP DATABASE 搭配 ENCRYPTION 參數來進行備份加密，進行備份加密時，假如還沒有備份憑證，會有下列提醒文字

警告: 用來加密資料庫加密金鑰的憑證尚未備份。您應該立即備份此憑證和與此憑證關聯的私密金鑰。萬一憑證無法使用時，或者您必須還原資料庫或將它附加到另一部伺服器時，就必須有憑證和私密金鑰的備份，否則就無法開啟資料庫。

備份檔案加密不支援附加至現有備份組，下述備份語法故意移除 Format 參數，進行備份時就會出現錯誤訊息

-- 移除 Fomrat 參數
BACKUP DATABASE AdventureWorks2022
TO DISK = 'C:\Temp\AdventureWorks2022.bak'
WITH
    INIT ,
    -- FORMAT ,
    COMPRESSION ,
    ENCRYPTION
	(
		ALGORITHM = AES_256 ,
		SERVER CERTIFICATE = AdventureWorksCert
	) ,
    STATS = 10

無法執行備份，因為要求的是 'ENCRYPTION'，但媒體之前是以不相容的結構格式化。若要附加此媒體集，請省略 'ENCRYPTION'，或在您的 BACKUP 陳述式中使用 WITH FORMAT 來建立新媒體集。如果在現有媒體集上使用 WITH FORMAT，將覆寫其所有備份組。