摘要

在SQL Server安全系列專題月報分享中，我們已經分享了：如何使用對稱密鑰實現SQL Server列加密技術、使用非對稱密鑰實現SQL Server列加密、使用混合密鑰實現SQL Server列加密技術、列加密技術帶來的查詢性能問題以及相應解決方案、行級別安全解決方案和SQL Server 2016 dynamic data masking實現隱私數據列打碼技術這六篇文章，文章詳情可以參見往期月報。本期月報我們分享使用證書做數據庫備份加密的最佳實踐。

問題引入

談及數據庫安全性問題，如何預防數據庫備份文件洩漏，如何防止脫庫安全風險，是一個非常重要的安全防範課題。這個課題的目的是萬一用戶數據庫備份文件洩漏，也要保證用戶數據的安全。在SQL Server中，2014版本之前，業界均採用的TDE技術來實現與防範脫庫行為，但是TDE的原理是需要將用戶所有的數據進行加密後落盤，讀取時解密。這種寫入時加密，讀取時解密的行為，必然會導致用戶查詢性能的降低和CPU使用率的上升（具體對性能和CPU影響，可以參見這片測試文章SQL Server Transparent Data Encryption (TDE) Performance Comparison）。那麼，我們一個很自然的問題是：有沒有一種技術，既可以保證備份文件的安全，又能夠兼顧到用戶查詢性能和CPU資源的消耗呢？這個技術就是我們今天要介紹的數據庫備份加密技術，該技術是SQL Server 2014版本首次引入，企業版本和標準版支持備份加密，Web版和Express版支持備份加密文件的還原。

具體實現

創建測試數據庫

為了測試方便，我們專門創建了測試數據庫BackupEncrypted。

-- create test database
IF DB_ID('BackupEncrypted') IS NOT NULL
 DROP DATABASE BackupEncrypted
GO
CREATE DATABASE BackupEncrypted
ON PRIMARY
(NAME = BackupEncrypted_data,
 FILENAME = N'E:\SQLDATA\DATA\BackupEncrypted_data.mdf',
 SIZE = 100MB, FILEGROWTH = 10MB),
FILEGROUP SampleDB_MemoryOptimized_filegroup CONTAINS MEMORY_OPTIMIZED_DATA
 ( NAME = BackupEncrypted_MemoryOptimized,
 FILENAME = N'E:\SQLDATA\DATA\BackupEncrypted_MemoryOptimized')
LOG ON
 ( NAME = BackupEncrypted_log,
 FILENAME = N'E:\SQLDATA\DATA\BackupEncrypted_log.ldf',
 SIZE = 100MB, FILEGROWTH = 10MB)
GO

創建測試表

在測試數據庫下，創建一張用於測試的表testTable，並插入一條隨機數據。

USE [BackupEncrypted]
GO
-- create test table and insert one record
IF OBJECT_ID('dbo.testTable', 'U') IS NOT NULL
 DROP TABLE dbo.testTable
GO
CREATE TABLE dbo.testTable
( 

 id UNIQUEIDENTIFIER default NEWID(),
 parent_id UNIQUEIDENTIFIER default NEWSEQUENTIALID()
);
GO
SET NOCOUNT ON;
INSERT INTO dbo.testTable DEFAULT VALUES;
GO
SELECT * FROM dbo.testTable ORDER BY id;

該條數據內容如下截圖：

創建Master Key和證書

創建Master Key和證書，用於加密數據庫備份文件。

USE master
GO
-- If the master key is not available, create it. 
IF NOT EXISTS (SELECT * 
 FROM sys.symmetric_keys
 WHERE name LIKE '%MS_DatabaseMasterKey%') 
BEGIN
 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKey*'; 
END 
GO
USE master
GO
-- create certificate
CREATE CERTIFICATE MasterCert_BackupEncrypted
AUTHORIZATION dbo
WITH SUBJECT = 'Backup encryption master certificate',
START_DATE = '02/10/2017',
EXPIRY_DATE = '12/30/9999'
GO

備份證書

首先，將證書和證書密鑰文件備份到本地，最好它們脫機保存到第三方主機，以免主機意外宕機，導致證書文件丟失，從而造成已加密的備份文件無法還原的悲劇。

USE master
GO
EXEC sys.xp_create_subdir 'C:\Tmp'
-- then backup it up to local path
BACKUP CERTIFICATE MasterCert_BackupEncrypted 
TO FILE = 'C:\Tmp\MasterCert_BackupEncrypted.cer' 

WITH PRIVATE KEY (
 FILE = 'C:\Tmp\MasterCert_BackupEncrypted.key',
 ENCRYPTION BY PASSWORD = 'aa11@@AA')
;

加密完全備份

創建完Master Key和證書文件後，我們就可以做數據庫完全備份加密操作。

USE master;
GO
-- do full backup database with encryption
BACKUP DATABASE [BackupEncrypted] 
TO DISK = N'C:\Tmp\BackupEncrypted_FULL.bak' 
WITH COMPRESSION, ENCRYPTION (
 ALGORITHM = AES_256, 
 SERVER CERTIFICATE = MasterCert_BackupEncrypted),
 STATS = 10;
GO

加密差異備份

數據庫差異備份加密，備份操作前，我們插入一條數據，以供後續的測試數據校驗。

USE [BackupEncrypted]
GO
-- insert another record
SET NOCOUNT ON;
INSERT INTO dbo.testTable DEFAULT VALUES;
GO
SELECT * FROM dbo.testTable ORDER BY id;
USE master;
GO
--Differential backup with encryption
BACKUP DATABASE [BackupEncrypted]
TO DISK = N'C:\Tmp\BackupEncrypted_DIFF.bak'
WITH CONTINUE_AFTER_ERROR,ENCRYPTION ( 

 ALGORITHM = AES_256, 
 SERVER CERTIFICATE = MasterCert_BackupEncrypted),
 STATS = 10,
 DIFFERENTIAL;
GO

差異備份操作前，校驗表中的兩條數據如下圖所示：

加密日誌備份

數據庫事物日誌備份加密，備份前，我們照樣插入一條數據，以供後續測試數據校驗。

USE BackupEncrypted
GO
-- insert another record
SET NOCOUNT ON;
INSERT INTO dbo.testTable DEFAULT VALUES;
GO
SELECT * FROM dbo.testTable ORDER BY id;
USE master;
GO
-- backup transaction log with encryption
BACKUP LOG [BackupEncrypted]
TO DISK = N'C:\Tmp\BackupEncrypted_log.trn'
WITH CONTINUE_AFTER_ERROR,ENCRYPTION (
 ALGORITHM = AES_256, 
 SERVER CERTIFICATE = MasterCert_BackupEncrypted),
 STATS = 10;
GO

日誌備份操作前，校驗表中的三條數據如下圖所示：

查看備份歷史

數據完全備份、差異備份和日誌備份結束後，查看備份歷史記錄。

use msdb
GO
-- check backups
SELECT 
 b.database_name,
 b.key_algorithm,
 b.encryptor_thumbprint,
 b.encryptor_type,
 b.media_set_id,
 m.is_encrypted, 
 b.type,
 m.is_compressed,
 bf.physical_device_name
FROM dbo.backupset b
INNER JOIN dbo.backupmediaset m 
 ON b.media_set_id = m.media_set_id
INNER JOIN dbo.backupmediafamily bf 
 on bf.media_set_id=b.media_set_id
WHERE database_name = 'BackupEncrypted'
ORDER BY b.backup_start_date DESC

備份歷史信息展示如下：

從截圖中數據我們可以看出，三種備份都採用了證書做備份加密。

查看備份文件信息

備份歷史檢查完畢後，在清理測試環境之前，檢查備份文件元數據信息，可以成功查看，沒有任何報錯。

USE master
GO
-- before clean environment, try to get backup files meta info, will be success
RESTORE FILELISTONLY FROM DISK='C:\Tmp\BackupEncrypted_FULL.bak'
RESTORE HEADERONLY FROM DISK='C:\Tmp\BackupEncrypted_FULL.bak'
RESTORE FILELISTONLY FROM DISK='C:\Tmp\BackupEncrypted_DIFF.bak'
RESTORE HEADERONLY FROM DISK='C:\Tmp\BackupEncrypted_DIFF.bak'
RESTORE FILELISTONLY FROM DISK='C:\Tmp\BackupEncrypted_log.trn'
RESTORE HEADERONLY FROM DISK='C:\Tmp\BackupEncrypted_log.trn'

展示結果部分截圖如下：

清理環境

清理環境目的是模擬在一臺全新實例上還原數據庫備份文件。

use master
GO
-- let's try to simulate a database crash, here we just drop this database.
DROP DATABASE [BackupEncrypted];
GO
-- and clean certificate and master key to simulate restore to a new instance.
DROP CERTIFICATE MasterCert_BackupEncrypted;
GO
DROP MASTER KEY;
GO

再次查看備份文件信息

清理掉證書和Master Key後，再次查看備份文件信息，此時會報錯。因為數據庫備份文件已經加密。這種報錯是我們所預期的，即就算我們的數據庫備份文件被脫庫洩漏，我們的數據也可以保證絕對安全，而不會非預期的還原回來。

USE master
GO
-- try to get backup files meta info again after clean environment, will be not success now.
RESTORE FILELISTONLY FROM DISK='C:\Tmp\BackupEncrypted_FULL.bak'
RESTORE HEADERONLY FROM DISK='C:\Tmp\BackupEncrypted_FULL.bak'
RESTORE FILELISTONLY FROM DISK='C:\Tmp\BackupEncrypted_DIFF.bak'
RESTORE HEADERONLY FROM DISK='C:\Tmp\BackupEncrypted_DIFF.bak'
RESTORE FILELISTONLY FROM DISK='C:\Tmp\BackupEncrypted_log.trn'
RESTORE HEADERONLY FROM DISK='C:\Tmp\BackupEncrypted_log.trn'

報錯信息類似如下：

Msg 33111, Level 16, State 3, Line 178
Cannot find server certificate with thumbprint '0xA938CE32CC86DFA6EAD2AED9429814F1A4C683ED'.
Msg 3013, Level 16, State 1, Line 178
RESTORE FILELIST is terminating abnormally.
Msg 33111, Level 16, State 3, Line 179
Cannot find server certificate with thumbprint '0xA938CE32CC86DFA6EAD2AED9429814F1A4C683ED'.
Msg 3013, Level 16, State 1, Line 179
RESTORE HEADERONLY is terminating abnormally.
Msg 33111, Level 16, State 3, Line 181
Cannot find server certificate with thumbprint '0xA938CE32CC86DFA6EAD2AED9429814F1A4C683ED'.
Msg 3013, Level 16, State 1, Line 181
RESTORE FILELIST is terminating abnormally.
Msg 33111, Level 16, State 3, Line 182
Cannot find server certificate with thumbprint '0xA938CE32CC86DFA6EAD2AED9429814F1A4C683ED'.
Msg 3013, Level 16, State 1, Line 182
RESTORE HEADERONLY is terminating abnormally.
Msg 33111, Level 16, State 3, Line 184
Cannot find server certificate with thumbprint '0xA938CE32CC86DFA6EAD2AED9429814F1A4C683ED'.
Msg 3013, Level 16, State 1, Line 184
RESTORE FILELIST is terminating abnormally.
Msg 33111, Level 16, State 3, Line 185
Cannot find server certificate with thumbprint '0xA938CE32CC86DFA6EAD2AED9429814F1A4C683ED'.
Msg 3013, Level 16, State 1, Line 185
RESTORE HEADERONLY is terminating abnormally.

部分錯誤信息截圖如下：

還原證書文件

數據庫備份加密，可以有效防止脫庫洩漏的安全風險。當然，合法用戶需要在新實例上成功還原加密備份文件。首先，創建Master Key；然後，從證書備份文件中，重新創建證書。

USE master
GO
-- so we have to re-create master key, the certificate and open the 
IF NOT EXISTS (SELECT * 
 FROM sys.symmetric_keys
 WHERE name LIKE '%MS_DatabaseMasterKey%') 
BEGIN
 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKey*'; 
END 
GO
use master
GO
-- re-create certificate
CREATE CERTIFICATE MasterCert_BackupEncrypted
FROM FILE = 'C:\Tmp\MasterCert_BackupEncrypted.cer'
WITH PRIVATE KEY (FILE = 'C:\Tmp\MasterCert_BackupEncrypted.key',
DECRYPTION BY PASSWORD = 'aa11@@AA');
GO

檢查備份文件信息

校驗備份文件信息，已經可以正確讀取。

USE master
GO
-- after re-create certificate, try to get backup files meta info again, will be success.
RESTORE FILELISTONLY FROM DISK='C:\Tmp\BackupEncrypted_FULL.bak'
RESTORE HEADERONLY FROM DISK='C:\Tmp\BackupEncrypted_FULL.bak'
RESTORE FILELISTONLY FROM DISK='C:\Tmp\BackupEncrypted_DIFF.bak' 

RESTORE HEADERONLY FROM DISK='C:\Tmp\BackupEncrypted_DIFF.bak'
RESTORE FILELISTONLY FROM DISK='C:\Tmp\BackupEncrypted_log.trn'
RESTORE HEADERONLY FROM DISK='C:\Tmp\BackupEncrypted_log.trn'

還原已加密完全備份文件

首先，嘗試還原數據庫完全備份文件，成功。

USE [master]
-- restore encrypted full backup
RESTORE DATABASE [BackupEncrypted] 
FROM DISK = N'C:\Tmp\BackupEncrypted_FULL.bak' 
WITH FILE = 1, 
MOVE 'BackupEncrypted_data' TO N'E:\SQLDATA\DATA\BackupEncrypted_data.mdf',
MOVE 'BackupEncrypted_MemoryOptimized' TO N'E:\SQLDATA\DATA\BackupEncrypted_MemoryOptimized',
MOVE 'BackupEncrypted_log' TO N'E:\SQLDATA\DATA\BackupEncrypted_log.ldf',
NOUNLOAD, STATS = 5, NORECOVERY
GO

還原已加密差異備份文件

其次，嘗試還原數據庫差異備份文件，成功。

-- Restore encrypted diff backup
RESTORE DATABASE [BackupEncrypted] 
FROM DISK = N'C:\Tmp\BackupEncrypted_DIFF.bak' WITH FILE = 1, 
MOVE 'BackupEncrypted_data' TO N'E:\SQLDATA\DATA\BackupEncrypted_data.mdf',
MOVE 'BackupEncrypted_MemoryOptimized' TO N'E:\SQLDATA\DATA\BackupEncrypted_MemoryOptimized',
MOVE 'BackupEncrypted_log' TO N'E:\SQLDATA\DATA\BackupEncrypted_log.ldf',
NOUNLOAD, STATS = 5, NORECOVERY
GO

還原已加密日誌備份文件

再次，嘗試還原數據庫日誌備份文件，成功。

-- restore encrypted transaction log backup
RESTORE LOG [BackupEncrypted] 
FROM DISK = N'C:\Tmp\BackupEncrypted_log.trn' WITH FILE = 1, 
MOVE 'BackupEncrypted_data' TO N'E:\SQLDATA\DATA\BackupEncrypted_data.mdf',
MOVE 'BackupEncrypted_MemoryOptimized' TO N'E:\SQLDATA\DATA\BackupEncrypted_MemoryOptimized',
MOVE 'BackupEncrypted_log' TO N'E:\SQLDATA\DATA\BackupEncrypted_log.ldf',
NOUNLOAD, STATS = 10
GO

檢查測試表數據

最後，檢查測試表的三條測試數據。

USE [BackupEncrypted]
GO
-- double check the three records
SELECT * FROM dbo.testTable ORDER BY id;

三條校驗數據一致。

清理測試環境

清理掉我們的測試環境。

use master
GO
-- clean up the environment
DROP DATABASE BackupEncrypted;
GO
DROP CERTIFICATE MasterCert_BackupEncrypted;
GO
DROP MASTER KEY;
GO

最後總結

本期月報我們分享了SQL Server 2014及以上版本如何使用證書實現數據庫備份加密技術，在防範脫庫安全風險的同時，既能夠比較好的保證用戶查詢性能，又不會帶來額外CPU資源的消耗。

參考文章

閱讀更多 阿里云云棲社區 的文章

關鍵字: Server 加密 中央處理器