Terabytes of data, millions of rows; the entire business depends on this — a database administrator’s responsibility is as massive as it sounds. Apart from keeping the data secure, a database administrator also has to keep the system up and running, and restore the data as needed, in case of a failure, with minimal impact to the business.

兆兆字节的数据，数百万行； 整个业务都取决于这一点–数据库管理员的责任听上去是巨大的。 除了保证数据安全外，数据库管理员还必须保持系统正常运行，并在出现故障的情况下根据需要还原数据 ，而对业务的影响最小。

While this is less of a challenge in an all-on-premises environment, database backups stored off-site or on the cloud require some more precaution. The data literally resides on someone else’s infrastructure. Data security has to be thought of from a different perspective now. If someone gets unauthorized access to the site, they could simply restore a copy of your database from a backup, onto their own hardware. What good, then, is it to lock the doors of your own equipment, when the soul has already departed?

尽管在全本地环境中这不是一个挑战，但存储在异地或云中的数据库备份需要更多的预防措施。 数据实际上位于其他人的基础结构上。 现在必须从另一个角度考虑数据安全性。 如果有人未经授权访问该站点，则他们只需将备份中数据库的副本还原到自己的硬件上即可。 那么，当灵魂已经离开时，锁上自己设备的门有什么用呢？

Security best-practices are in place in order to secure the metaphorical soul. They need to be implemented not just in the production environment, but within our backup solution as well.

安全最佳实践已经到位，以保护隐喻的灵魂。 它们不仅需要在生产环境中实现，还需要在我们的备份解决方案中实现 。

Earlier versions of SQL Server had a limitation on this security feature; we had to use third-party solutions to encrypt and compress the backup files. Microsoft, with SQL Server 2014, has begun introducing database backup encryption within the native backup capability—now, SQL Server has the ability to encrypt the data while creating a backup using various encryption algorithms!

早期版本SQL Server在此安全功能上有局限。 我们必须使用第三方解决方案来加密和压缩备份文件。 Microsoft在SQL Server 2014中已开始在本机备份功能内引入数据库备份加密-现在，SQL Server能够在使用各种加密算法创建备份的同时对数据进行加密！

In this article, I will discuss backup encryption application internals, how this feature is applied with the latest versions of SQL, the importance of security, how to recover/restore the certificate and the database in case of system/database failure, asymmetric key and EKM provider, and give step-by-step examples of the process of demonstrating the encryption and the recovery of a backup.

在本文中，我将讨论备份加密应用程序的内部结构，如何将此功能与最新版本SQL配合使用，安全性的重要性，在系统/数据库故障，非对称密钥和错误情况下如何恢复/还原证书和数据库。 EKM提供程序，并逐步演示了加密和恢复备份的过程。

In this article:

在这篇文章中：

• Key challenges in securing data

  保护数据的关键挑战

• What database encryption is, and why it’s critical in today’s data environment

  什么是数据库加密，以及为什么它在当今的数据环境中至关重要

• The two classifications of database backup encryption

  数据库备份加密的两种分类

• The benefits of implementing a database backup off-site or on the cloud

  异地或在云上实施数据库备份的好处

• The impact of database backup and restore/recovery

  数据库备份和还原/恢复的影响

Prerequisites

先决条件

• SQL Server 2014+ Enterprise or Standard Edition

  SQL Server 2014+企业版或标准版

• Write access to a local/remote file system

  对本地/远程文件系统的写访问

• Storage with adequate space to create a backup of the database

  具有足够空间的存储空间可创建数据库备份

Benefits

好处

1. Encryption for native SQL Managed Backup

   本机SQL托管备份的加密

2. Security and integrity of the backup

   备份的安全性和完整性

3. It can also be used for databases that are encrypted using TDE

   它也可以用于使用TDE加密的数据库

4. Additional security for off-site backups

   异地备份的额外安全性

5. Use of various encryption algorithms, which provides you with flexibility in selecting an algorithm that aligns with your requirements

   使用各种加密算法，使您可以灵活地选择符合要求的算法

6. asymmetric key to manage and integrate security with EKM providers 非对称密钥来管理和集成EKM提供程序的安全性

Feature Support – SQL [2014-2016]

功能支持– SQL [2014-2016]

加密的数据库备份 (Database Backup with Encryption)

Almost every organization has a challenge of protecting the data. It is almost impossible to run a business without protecting the sensitive data. This feature comes handy where backups are managed and handled at a remote site or cloud. In order to encrypt the backup, we will need a certificate. And, in order to create a certificate, we will need a master key for the database.

几乎每个组织都面临着保护数据的挑战。 如果不保护敏感数据，几乎不可能开展业务。 在远程站点或云中管理和处理备份的地方，此功能非常方便。 为了加密备份，我们将需要一个证书。 并且，为了创建证书，我们将需要数据库的主密钥。

示范 (Demonstration)

Let’s see how we can create a secure backup for off-site storage. We’ll do that by creating a new database. Let’s call it “SQLShack”. I’m going to switch into the SQLShack database to create a sample table to hold dummy data which are needed to demonstrate the Tail Log backup process. A stored procedure is created to generate the sample data. On execution of the stored procedure, the data will be fed to the table.

让我们看看如何为场外存储创建安全备份。 我们将通过创建一个新的数据库来做到这一点。 我们称之为“ SQLShack”。 我将切换到SQLShack数据库，以创建一个示例表来保存虚拟数据，这些数据是演示Tail Log备份过程所需的。 创建一个存储过程以生成样本数据。 在执行存储过程时，数据将被馈送到表中。

-- create a new database for this example

CREATE DATABASE SQLShack;

GO

USE SQLShack;

GO

-- insert some data

CREATE TABLE SQLShackTable (

    ID int IDENTITY(1,1000) PRIMARY KEY NOT NULL,

    value int

);

GO

CREATE PROCEDURE InsertSQLShackTable

AS

DECLARE @i int = 1

WHILE @i <100

    BEGIN

        INSERT SQLShackTable (value) VALUES (@i)

        Set @i +=1

    END

GO

EXECUTE InsertSQLShackTable;

GO

SELECT * FROM SQLShackTable;

GO

生成主密钥 (Generate Master Key)

Now, I’ll change the current context of the database to Master. Next, we’re going to create a master key and the certificate on our server. 

现在，我将数据库的当前上下文更改为Master。 接下来，我们将在服务器上创建一个主密钥和证书。

USE MASTER;

GO

-- create master key and certificate

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '!@Api1401@2015!!';

GO

创建证书 (Create Certificate)

The certificate “SQLShackDBCert” will be created with a subject line, which’ll just appear in the metadata for the certificate, of SQLShackDBCert Backup Certificate. I’ll execute the below line to create the certificate.

将使用主题行创建证书“ SQLShackDBCert”，该主题行将仅出现在证书的元数据中，即SQLShackDBCert Backup Certificate。 我将执行以下行来创建证书。

Now, since we’re working in the master database, I can find that certificate created in the System database. Browsing the Object Explorer, expanding master, going down to Security, and then opening up the Certificates folder tells that the certificate is created. 

现在，由于我们正在使用master数据库，因此可以在System数据库中找到该证书。 浏览对象资源管理器，展开母版，转到“安全性”，然后打开“证书”文件夹，这表明已创建证书。

In order protect the data from failure event; make sure that back-up the certificate to an external file. Let’s backup the certificate using the BACKUP CERTIFICATE command, the name of the certificate is SQLShackDBCert, and we’ll export it to a file to remote secured location

为了保护数据免受故障事件的影响； 确保将证书备份到外部文件。 让我们使用BACKUP CERTIFICATE命令备份证书，证书的名称是SQLShackDBCert，然后将其导出到文件中到远程安全位置

CREATE CERTIFICATE SQLShackDBCert

    WITH SUBJECT = 'SQLShackDB Backup Certificate';

GO

The name of the certificate file is “f:\Program Files\SQLShackDBCert.cert”. We’d also want to export the private key file, “f:\Program Files\SQLShackDBCert.key” which will encrypt the certificate file. Finally, we’ll protect the entire thing with a strong password.  Let’s create the backup of the certificate at a secured offsite location

证书文件的名称为“ f：\ Program Files \ SQLShackDBCert.cert” 。 我们还希望导出私钥文件“ f：\ Program Files \ SQLShackDBCert.key” ，该文件将对证书文件进行加密。 最后，我们将使用强密码保护整个内容。 让我们在安全的异地位置创建证书的备份

-- export the backup certificate to a file

BACKUP CERTIFICATE SQLShackDBCert TO FILE = 'f:\Program Files\SQLShackDBCert.cert'

WITH PRIVATE KEY (

FILE = 'f:\Program Files\SQLShackDBCert.key',

ENCRYPTION BY PASSWORD = 'Api1401@2015!!')

If you get an error message at this point stating that your password doesn’t meet Windows’ policy requirements, it simply means that your installation of Windows is configured to require stronger passwords. Make sure you enter a strong password.

如果此时出现错误消息，指出您的密码不符合Windows的策略要求，则仅表示您的Windows安装配置为需要更强的密码。 确保输入一个强密码 。

数据库备份 (Database Backup )

Next, I’m going to back-up the SQLShack database with the encryption protocols enabled. The AES_256 encryption is a very strong and a recommended encryption algorithm. We’re going to encrypt this backup with the server certificate that we just created.

接下来，我将备份启用了加密协议SQLShack数据库。 AES_256加密是一种非常强大且推荐的加密算法。 我们将使用刚刚创建的服务器证书对该备份进行加密。

-- backup the database with encryption

BACKUP DATABASE SQLShack

TO DISK = 'g:\Program Files\SQLShack.bak'

WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = SQLShackDBCert)

数据库恢复 (Database Recovery)

This section discusses the demonstration of the Tail Log backup using database encryption. The data is inserted after a full backup of SQLShack. We’re going to drop the database SQLShack, and the certificate file, just to simulate movement to a new, clean instance of SQL Server.

本节讨论使用数据库加密的尾日志备份的演示。 在完全备份SQLShack之后插入数据。 我们将删除数据库SQLShack和证书文件，只是为了模拟向新的干净SQL Server实例的移动。

-- insert additional records

USE SQLShack;

GO

EXECUTE InsertSQLShackTable;

Let’s demonstrate the recovery process.

让我们演示恢复过程。

• Insert sample data

  插入样本数据

• Simulate database corruption by detaching the database

  通过分离数据库模拟数据库损坏

• Delete the file from the drive

  从驱动器中删除文件

• Bring the database back online

  使数据库重新联机

• Refresh the database

  刷新数据库

• Initiate the Tail Log backup with database encryption

  使用数据库加密启动尾日志备份

-- take SQLShack offline

 

ALTER DATABASE SQLShack SET OFFLINE WITH  ROLLBACK IMMEDIATE

 

-- delete .mdf data file from the hard drive

 

 

 

-- attempt to take TailLogDB online

 

USE master;

GO

BACKUP LOG SQLShack

TO DISK = 'g:\Program Files\SQLShackTailLogDB.log'

WITH CONTINUE_AFTER_ERROR,ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = SQLShackDBCert)

1. 

检查备份元数据 (Check the backup meta-data)

If you use encryption during the backup you wouldn’t be able to append the backup to an existing media set. The restoration just works like normal restoration steps, except ensuring the corresponding certificates are created and configured on the destination server.

如果在备份期间使用加密，则无法将备份附加到现有媒体集。 除了确保在目标服务器上创建并配置了相应的证书外，还原的工作原理与正常还原步骤相同。

--Check for the backup

 

SELECT

b.database_name,

    key_algorithm,

    encryptor_thumbprint,

    encryptor_type,

b.media_set_id,

    is_encrypted,

type,

    is_compressed,

bf.physical_device_name

FROM msdb.dbo.backupset b

INNER JOIN msdb.dbo.backupmediaset m ON b.media_set_id = m.media_set_id

INNER JOIN msdb.dbo.backupmediafamily bf on bf.media_set_id=b.media_set_id

WHERE database_name = 'SQLShack'

ORDER BY b.backup_start_date  DESC

We have four new files that have just been created. We have the backup certificate, the encryption key file, as well as the full backup and the tail log backup of our database. Let’s go back into SQL Server Management Studio and see how we can restore this backup.

我们刚创建了四个新文件。 我们拥有备份证书，加密密钥文件以及数据库的完整备份和尾日志备份。 让我们回到SQL Server Management Studio，看看我们如何还原此备份。

-- clean up the instance

 

DROP DATABASE SQLShack;

GO

DROP CERTIFICATE SQLShackDBCert;

GO

DROP MASTER KEY;

GO

After running the commands above, if I right-click on the Security folder and say Refresh, you’ll notice that it is no longer inside of our Certificates folder. And, if I go back up and refresh databases, you’ll notice that SQLShack is no longer available either. Now, let’s attempt to restore the database from the backup. I’ll specify that I want to restore the database, SQLShack, from the disk. When I execute the command, I get an error message, saying that it couldn’t find the server certificate required in order to restore the backed-up database.

运行上述命令后，如果我右键单击“ 安全性”文件夹并说“ 刷新” ，您会发现它不再位于“ 证书”文件夹中。 而且，如果我备份并刷新数据库，您会注意到SQLShack也不再可用。 现在，让我们尝试从备份中还原数据库。 我将指定要从磁盘还原数据库SQLShack。 当我执行该命令时，我收到一条错误消息，说它找不到还原备份数据库所需的服务器证书。

Without configuring the certificate, any attempt to restore would result in the following error:

如果不配置证书，则任何还原尝试都会导致以下错误：

--Use RESTORE FILELISTONLY to get the logical names of the data files in the backup. This is especially useful when you’re working with an unfamiliar backup file.

 

RESTORE FILELISTONLY FROM DISK='g:\Program Files\SQLShack.bak'

重新创建主密钥和证书 (Recreate the master key and the certificates)

In order to restore this encrypted database, we first need to restore the certificate. But this time, instead of creating it based off of the master key for the database, we’re going to restore it from the file. I’ll specify FROM FILE and the path to that file that we exported. We’ll also specify the private key file. Finally, we’ll enter DECRYPTION BY PASSWORD and we’ll re-specify the password that we established earlier when we created that key. After execution, if I go back into System Databases -> master, -> Security, and back to Certificates, we should see the SQLShackDBCert certificate back there.

为了还原此加密数据库，我们首先需要还原证书。 但是这次，我们不是基于数据库的主键来创建它，而是要从文件中还原它。 我将指定FROM FILE以及我们导出的文件的路径。 我们还将指定私钥文件。 最后，我们将输入DECRYPTION BY PASSWORD，然后将重新指定创建该密钥时先前建立的密码。 执行后，如果我返回到“系统数据库”->“主数据库”->“安全性”，然后再返回到“证书”，我们应该在那里看到SQLShackDBCert证书。

-- recreate master key and certificate

 

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '!@Api1401@2015!!';

GO

-- restore the certificate

CREATE CERTIFICATE SQLShackDBCert

FROM FILE = 'f:\Program Files\SQLShackDBCert.cert'

WITH PRIVATE KEY (FILE = 'f:\Program Files\SQLShackDBCert.key',

DECRYPTION BY PASSWORD = 'Api1401@2015!!');

GO

数据库还原 (Database Restoration)

Now, go ahead and try the restore again, the RESTORE DATABASE SQLShack from the disk file. Execute the command, and voila, it processed successfully this time! We see that the database, SQLShack, came online

现在，继续尝试从磁盘文件还原RESTORE DATABASE SQLShack。 执行命令，瞧，这次成功处理了！ 我们看到数据库SQLShack已联机

--Use RESTORE WITH MOVE to move and/or rename database files to a new path.

 

RESTORE DATABASE SQLShack FROM DISK = 'g:\Program Files\SQLShack.bak'

WITH NORECOVERY,

MOVE 'SQLShack' TO 'f:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\SQLShack_Data.mdf',

MOVE 'SQLShack_Log' TO 'g:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\SQLShack_Log.ldf',

REPLACE, STATS = 10;

GO

-- attempt the restore log again

RESTORE LOG SQLShack

FROM DISK = 'g:\Program Files\SQLShackTailLogDB.log';

GO

 

--Data validation

 

SELECT * FROM SQLShackTable;

GO

-- clean up the instance

 

DROP DATABASE SQLShack;

GO

DROP CERTIFICATE SQLShackDBCert;

GO

DROP MASTER KEY;

GO

使用非对称密钥的数据库备份 (Database backup Using Asymmetric Key)

In order to encrypt the database encryption key with an asymmetric key, please use an asymmetric key that resides on an Extensible Key Management Provider. Extensible Key Management (EKM) is another new feature that gives SQL Server the ability to store the encryption key used for TDE on hardware specifically designed for key security and management. Such devices are called High Security Modules (HSM), whose vendors are referred to as EKM providers. A good key management vendor should supply you with software libraries that easily add and implement it in SQL Server encryption.

为了使用非对称密钥对数据库加密密钥进行加密，请使用驻留在可扩展密钥管理提供程序上的非对称密钥。 可扩展密钥管理（EKM）是另一个新功能，使SQL Server能够在专门为密钥安全性和管理设计的硬件上存储用于TDE的加密密钥。 这种设备称为高安全性模块（HSM），其供应商称为EKM提供程序。 好的密钥管理供应商应为您提供易于在SQL Server加密中添加和实现的软件库。

SQL Server stores encryption keys separately from the database server on a secure key manager, in order to meet various compliance requirements. Encryption is supported for backups done by SQL Server Managed Backup, which provides additional security for off-site backups. For example, a database backup file placed on the cloud.

SQL Server将加密密钥与数据库服务器分开存储在安全密钥管理器中，以满足各种合规性要求。 SQL Server托管备份完成的备份支持加密，从而为异地备份提供了额外的安全性。 例如，将数据库备份文件放置在云上。

In asymmetric encryption, two different keys are used: A “public key” for encrypting and a “private key” for decrypting. This type of asymmetric encryption is referred to as Public Key Infrastructure (PKI) or Public-key Cryptography.

在非对称加密中，使用了两个不同的密钥：用于加密的“公用密钥”和用于解密的“专用密钥”。 这种类型的非对称加密称为“公共密钥基础结构（PKI）”或“公共密钥密码术”。

摘要 (Summary)

• In an environment that relies on SQL-managed native backup methodology, this would be a great feature to secure the data. Given that the backup files are encrypted, we can be confident that they’ll be unusable even if they fall into the wrong hands, while still taking advantage of an off-site storage strategy.
  在依赖SQL管理的本机备份方法的环境中，这将是保护数据安全的重要功能。 鉴于备份文件已加密，我们可以确信，即使它们落入错误的人手中，它们也将无法使用，同时仍可利用异地存储策略。
• While the process of recovery is no different from restoring the normal database backup file, there’s no “backdoor” to recover the database access if you lose all access to the keys. So keep the keys safe.

  尽管恢复过程与恢复普通数据库备份文件没有什么不同，但是如果您失去对密钥的所有访问权限，则没有“后门”来恢复数据库访问。 因此，请确保钥匙安全。

• The restore operation validates the thumbprint of the certificate during the restore operation. Therefore, the certificate used to create the backup must be retained in its original state.

  还原操作将在还原操作期间验证证书的指纹。 因此，用于创建备份的证书必须保留其原始状态。

• If restoring a database from an encrypted backup file is performed on the same SQL Server instance, the restore operation is performed as usual, since the keys and the certificate are already contained in the master database. They’d be opened automatically during the process of decryption

  如果在同一个SQL Server实例上从加密的备份文件还原数据库，则还原操作将照常执行，因为密钥和证书已包含在master数据库中。 它们会在解密过程中自动打开

• You won’t be able to append the backup files to the existing media set in case of encrypted backups; this is not the case with normal backup methodology.

  如果进行加密备份，则无法将备份文件附加到现有媒体集； 普通备份方法不是这种情况。

• Even after several attempts, I didn’t notice a significant difference in the usage of system resources during the process of encryption of database backup, when compared to the normal database backup operation. However, AES is a block cipher, and requires the input to be a multiple of the block size (16 bytes, a.k.a. 128 bits). This means that padding schemes are used. Most of the time, the padding is negligible, which may be a reason why it doesn’t increase the size of the backup.

  与正常的数据库备份操作相比，即使经过几次尝试，在数据库备份加密过程中，我也没有注意到系统资源使用方面的显着差异。 但是，AES是一种分组密码，并且要求输入为分组大小的倍数（16字节，也就是128位）。 这意味着将使用填充方案。 在大多数情况下，填充可以忽略不计，这可能就是为什么它不会增加备份大小的原因。

• Use a combination of hardware- and software-supporting AES with a 256-bit key to ensure that the encryption doesn’t impact your backup or recovery time objectives.

  将支持硬件和软件的AES与256位密钥结合使用，以确保加密不会影响您的备份或恢复时间目标。

参考资料 (References)

• Extensible Key Management (EKM) 可扩展密钥管理（EKM）
• SQL Server and Database Encryption Keys SQL Server和数据库加密密钥
• SQL Server 2014 Backup Encryption SQL Server 2014备份加密