摘要

在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 NULLDROP 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 NULLDROP TABLE dbo.testTable GO CREATE TABLE dbo.testTable (id UNIQUEIDENTIFIER default NEWID(),parent_id UNIQUEIDENTIFIER default NEWSEQUENTIALID() ); GOSET NOCOUNT ON; INSERT INTO dbo.testTable DEFAULT VALUES; GOSELECT * 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_keysWHERE name LIKE '%MS_DatabaseMasterKey%') BEGINCREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKey*'; END GOUSE 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; GOSELECT * 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; GOSELECT * 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; GODROP 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_keysWHERE name LIKE '%MS_DatabaseMasterKey%') BEGINCREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKey*'; END GOuse 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資源的消耗。

原文鏈接
本文為云棲社區原創內容，未經允許不得轉載。

總結

以上是生活随笔為你收集整理的MSSQL实践-数据库备份加密的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。