MSSQL-最佳实践-数据库备份加密-阿里云开发者社区

开发者社区> 风移> 正文

MSSQL-最佳实践-数据库备份加密

简介: --- title: MSSQL-最佳实践-数据库备份加密 author: 风移 --- # 摘要 在SQL Server安全系列专题月报分享中,我们已经分享了:如何使用对称密钥实现SQL Server列加密技术、使用非对称密钥实现SQL Server列加密、使用混合密钥实现SQL S.
+关注继续查看

title: MSSQL-最佳实践-数据库备份加密

author: 风移

摘要

在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;

该条数据内容如下截图:
01.png

创建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

差异备份操作前,校验表中的两条数据如下图所示:
02.png

加密日志备份

数据库事物日志备份加密,备份前,我们照样插入一条数据,以供后续测试数据校验。

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

日志备份操作前,校验表中的三条数据如下图所示:
03.png

查看备份历史

数据完全备份、差异备份和日志备份结束后,查看备份历史记录。

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

备份历史信息展示如下:
04.png

从截图中数据我们可以看出,三种备份都采用了证书做备份加密。

查看备份文件信息

备份历史检查完毕后,在清理测试环境之前,检查备份文件元数据信息,可以成功查看,没有任何报错。

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'

展示结果部分截图如下:
05.png

清理环境

清理环境目的是模拟在一台全新实例上还原数据库备份文件。

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.

部分错误信息截图如下:
06.png

还原证书文件

数据库备份加密,可以有效防止脱库泄漏的安全风险。当然,合法用户需要在新实例上成功还原加密备份文件。首先,创建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;

三条校验数据一致。
07.png

清理测试环境

清理掉我们的测试环境。

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

最后总结

本期月报我们分享了SQL Server 2014及以上版本如何使用证书实现数据库备份加密技术,在防范脱库安全风险的同时,既能够比较好的保证用户查询性能,又不会带来额外CPU资源的消耗。

参考文章

SQL Server Transparent Data Encryption (TDE) Performance Comparison
SQLServer · 最佳实践 · 透明数据加密TDE在SQLServer的应用
开启TDE的RDS SQL Server还原到本地环境
Understanding Database Backup Encryption in SQL Server

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
SQLServer · 最佳实践 · 透明数据加密在SQLServer的应用
背景 作为云计算的服务提供者,我们在向用户提供优秀的服务能力时会遇到一个合规的问题。在数据库领域,数据是极其敏感和珍贵的,保护好数据,就如保护好企业的生命线。因此,需要采取一些预防措施来帮助保护数据库的安全,如设计一个安全系统、加密机密资产以及在数据库服务器的周围构建防火墙。但是,如果遇到物理介质
1958 0
MSSQL-最佳实践-实例级别数据库上云RDS SQL Server
--- title: MSSQL-最佳实践-实例级别数据库上云RDS SQL Server author: 风移 --- # 摘要 到目前,我们完成了SQL Server备份还原专题系列八篇月报分享:三种常见的数据库备份、备份策略的制定、查找备份链、数据库的三种恢复模式与备份之间的关系、利用文件组实现冷热数据隔离备份方案、如何监控备份还原进度、阿里云RDS SQL自动化迁移上云的一种
1460 0
MSSQL · 最佳实践 · 使用混合密钥实现列加密
摘要 在SQL Server安全系列专题的上两期月报分享中,我们分别分享了:如何使用对称密钥实现SQL Server列加密技术和使用非对称密钥加密方式实现SQL Server列加密。本期月报我们分享使用混合密钥加密方式实现SQL Server列加密技术,最大限度减少性能损失,最大程度保护用户数据安全。
1504 0
使用DAS实现数据库自治最佳实践
本最佳实践描述在电商、新零售等ToC业务场景下,用户利用数据库自治服务DAS对核心OLTP数据库PolarDB for MySQL进行异常检测、自动SQL限流、自动SQL优化、自动弹性伸缩等,提高数据库系统的稳定性和性能。
499 0
PostgreSQL 最佳实践 - 在线逻辑备份与恢复介绍
背景 PostgreSQL 逻辑备份, 指在线备份数据库数据, DDL以SQL语句形式输出, 数据则可以以SQL语句或者固定分隔符(row格式)的形式输出. 备份时不影响其他用户对备份对象的DML操作. 本文主要介绍一下PostgreSQL提供的逻辑备份工具pg_dump, p
3575 0
SQL Server - 最佳实践 - SSMS配合BCP迁移SQL Server数据库上阿里云
本文讨论的主题是使用SSMS(SQL Server Management Studio)配合BCP命令行的方式来迁移SQL Server数据库。使用SSMS做数据库结构迁移,使用BCP命令做全量数据迁移,此方案是以本地SQL Server数据库迁移到阿里云RDS SQL Server 2012为例。
3404 0
MSSQL - 最佳实践 - 使用SSL加密连接
--- title: MSSQL - 最佳实践 - 使用SSL加密连接 author: 风移 --- # 摘要 在SQL Server安全系列专题月报分享中,往期我们已经陆续分享了:[如何使用对称密钥实现SQL Server列加密技术](http://mysql.taobao.org/monthly/2018/08/03/)、[使用非对称密钥实现SQL Server列加密](http:/
2449 0
+关注
风移
阿里云数据库专家,负责SQL Server数据库产品线。SQL Server从业10年,经历过SQL 2000、SQL 2005、SQL 2008、SQL 2008R2、SQL 2012、SQL 2014、SQL 2016和SQL on Linux各个版本。
75
文章
46
问答
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载