自己公司网站如何添加qq,绿色环保企业网站模板,个人网站介绍模板,企业网站设计的特点摘要
在SQL Server安全系列专题月报分享中#xff0c;我们已经分享了#xff1a;如何使用对称密钥实现SQL Server列加密技术、使用非对称密钥实现SQL Server列加密、使用混合密钥实现SQL Server列加密技术、列加密技术带来的查询性能问题以及相应解决方案、行级别安全解决方…摘要
在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 NE:\SQLDATA\DATA\BackupEncrypted_data.mdf,SIZE 100MB, FILEGROWTH 10MB),
FILEGROUP SampleDB_MemoryOptimized_filegroup CONTAINS MEMORY_OPTIMIZED_DATA( NAME BackupEncrypted_MemoryOptimized,FILENAME NE:\SQLDATA\DATA\BackupEncrypted_MemoryOptimized)
LOG ON( NAME BackupEncrypted_log,FILENAME NE:\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 aa11AA)
;
加密完全备份
创建完Master Key和证书文件后我们就可以做数据库完全备份加密操作。
USE master;
GO
-- do full backup database with encryption
BACKUP DATABASE [BackupEncrypted]
TO DISK NC:\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 NC:\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 NC:\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_idb.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 DISKC:\Tmp\BackupEncrypted_FULL.bak
RESTORE HEADERONLY FROM DISKC:\Tmp\BackupEncrypted_FULL.bakRESTORE FILELISTONLY FROM DISKC:\Tmp\BackupEncrypted_DIFF.bak
RESTORE HEADERONLY FROM DISKC:\Tmp\BackupEncrypted_DIFF.bakRESTORE FILELISTONLY FROM DISKC:\Tmp\BackupEncrypted_log.trn
RESTORE HEADERONLY FROM DISKC:\Tmp\BackupEncrypted_log.trn
展示结果部分截图如下 清理环境
清理环境目的是模拟在一台全新实例上还原数据库备份文件。
use master
GO
-- lets 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 DISKC:\Tmp\BackupEncrypted_FULL.bak
RESTORE HEADERONLY FROM DISKC:\Tmp\BackupEncrypted_FULL.bakRESTORE FILELISTONLY FROM DISKC:\Tmp\BackupEncrypted_DIFF.bak
RESTORE HEADERONLY FROM DISKC:\Tmp\BackupEncrypted_DIFF.bakRESTORE FILELISTONLY FROM DISKC:\Tmp\BackupEncrypted_log.trn
RESTORE HEADERONLY FROM DISKC:\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 aa11AA);
GO
检查备份文件信息
校验备份文件信息已经可以正确读取。
USE master
GO
-- after re-create certificate, try to get backup files meta info again, will be success.
RESTORE FILELISTONLY FROM DISKC:\Tmp\BackupEncrypted_FULL.bak
RESTORE HEADERONLY FROM DISKC:\Tmp\BackupEncrypted_FULL.bakRESTORE FILELISTONLY FROM DISKC:\Tmp\BackupEncrypted_DIFF.bak
RESTORE HEADERONLY FROM DISKC:\Tmp\BackupEncrypted_DIFF.bakRESTORE FILELISTONLY FROM DISKC:\Tmp\BackupEncrypted_log.trn
RESTORE HEADERONLY FROM DISKC:\Tmp\BackupEncrypted_log.trn
还原已加密完全备份文件
首先尝试还原数据库完全备份文件成功。
USE [master]
-- restore encrypted full backup
RESTORE DATABASE [BackupEncrypted]
FROM DISK NC:\Tmp\BackupEncrypted_FULL.bak
WITH FILE 1,
MOVE BackupEncrypted_data TO NE:\SQLDATA\DATA\BackupEncrypted_data.mdf,
MOVE BackupEncrypted_MemoryOptimized TO NE:\SQLDATA\DATA\BackupEncrypted_MemoryOptimized,
MOVE BackupEncrypted_log TO NE:\SQLDATA\DATA\BackupEncrypted_log.ldf,
NOUNLOAD, STATS 5, NORECOVERY
GO
还原已加密差异备份文件
其次尝试还原数据库差异备份文件成功。
-- Restore encrypted diff backup
RESTORE DATABASE [BackupEncrypted]
FROM DISK NC:\Tmp\BackupEncrypted_DIFF.bak WITH FILE 1,
MOVE BackupEncrypted_data TO NE:\SQLDATA\DATA\BackupEncrypted_data.mdf,
MOVE BackupEncrypted_MemoryOptimized TO NE:\SQLDATA\DATA\BackupEncrypted_MemoryOptimized,
MOVE BackupEncrypted_log TO NE:\SQLDATA\DATA\BackupEncrypted_log.ldf,
NOUNLOAD, STATS 5, NORECOVERY
GO
还原已加密日志备份文件
再次尝试还原数据库日志备份文件成功。
-- restore encrypted transaction log backup
RESTORE LOG [BackupEncrypted]
FROM DISK NC:\Tmp\BackupEncrypted_log.trn WITH FILE 1,
MOVE BackupEncrypted_data TO NE:\SQLDATA\DATA\BackupEncrypted_data.mdf,
MOVE BackupEncrypted_MemoryOptimized TO NE:\SQLDATA\DATA\BackupEncrypted_MemoryOptimized,
MOVE BackupEncrypted_log TO NE:\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资源的消耗。
原文链接 本文为云栖社区原创内容未经允许不得转载。
