tde数据库加密_如何将TDE加密的用户数据库添加到Always On可用性组

tde数据库加密

SQL Server Transparent Data Encryption, also known as TDE, is a “data at rest” encryption mechanism that is introduced in SQL Server 2008 as an Enterprise Edition feature. TDE is used to perform a real-time I/O encryption for the SQL Server database data, log, backup and snapshot physical files, rather than encrypting the data itself, using either Advanced Encryption Standard (AES) or Triple DES (3DES) encryption.

SQL Server透明数据加密（也称为TDE）是SQL Server 2008中作为企业版功能引入的“静态数据”加密机制。 TDE用于对SQL Server数据库数据，日志，备份和快照物理文件执行实时I / O加密，而不是使用高级加密标准（AES）或三重DES（3DES）加密对数据本身进行加密。 。

Transparent Data Encryption encrypts the database data files using a Database Encryption Key. The Database Encryption Key is a symmetric key that is secured by a Certificate and a Master Key stored in the master database, preventing these data files from being viewed outside the current SQL instance. However, if the database data files or the backup media are stolen, the data thief will not be able to attach the database data files or restore the backup files to another SQL instance without the encryption keys. TDE also prevents the data and backup files from being opened in a text editor to view the database file’s contents.

透明数据加密使用数据库加密密钥加密数据库数据文件。 数据库加密密钥是一个对称密钥，由存储在主数据库中的证书和主密钥来保护，以防止在当前SQL实例外部查看这些数据文件。 但是，如果数据库数据文件或备份媒体被盗，数据窃贼将无法在没有加密密钥的情况下附加数据库数据文件或将备份文件还原到另一个SQL实例。 TDE还防止在文本编辑器中打开数据和备份文件以查看数据库文件的内容。

The TDE encryption process, as the name indicates, is transparent from the users and requires no change from the application side. Transparent Data Encryption encrypts the physical files at the page level before writing the pages to the disk, and decrypts the files when the pages are read into the memory.

顾名思义，TDE加密过程对用户是透明的，不需要在应用程序端进行任何更改。 透明数据加密在将页面写入磁盘之前先在页面级别加密物理文件，并在将页面读入内存时解密文件。

In this article, we will see how we could add a TDE encrypted database to Always On Availability Group site that is already configured. Assume that we have configured the Always On Availability Group AG40VS that contains two replicas: DB41VS and DB42VS. The DB41VS SQL Server is configured as the primary replica and the DB42 SQL server is configured as a Secondary replica. Both DB41VS and DB42VS are configured for Automatic Failover:

在本文中，我们将看到如何将TDE加密的数据库添加到已配置的Always On可用性组站点。 假设我们已经配置了包含两个副本的Always On Availability组AG40VS ： DB41VS和DB42VS 。 DB41VS SQL Server被配置为主要副本，而DB42 SQL Server被配置为辅助副本。 DB41VS和DB42VS均配置为自动故障转移：

TDE_Test is a user database that contains a critical financial data, and on a monthly basis, the backup of that database is sent to the company headquarter office to verify the database backup. So that, we need to make sure that if the backup is stolen or missed, no one can browse its data. To achieve that, the database physical files should be encrypted using the SQL Server Transparent Data Encryption.

TDE_Test是一个包含重要财务数据的用户数据库，该数据库的备份每月发送到公司总部办公室，以验证数据库备份。 因此，我们需要确保如果备份被盗或丢失，则没有人可以浏览其数据。 为此，应使用SQL Server透明数据加密对数据库物理文件进行加密。

To configure the TDE on the TDE_Test database, we should first create a master key in the master database. Only one master key can be created per each SQL Server instance. All user databases that are hosted in the same SQL instance, and have the TDE enabled on it, will share the dependency upon the same master key. The below CREATE MASTER KEY T-SQL statement is used to create the master key under the master database, and this master key will be encrypted by a complex password. It is better to keep a backup of that password in a secure place:

要在TDE_Test数据库上配置TDE，我们应该首先在master数据库中创建一个主密钥。 每个SQL Server实例只能创建一个主密钥。 托管在同一SQL实例中并启用了TDE的所有用户数据库将共享对同一主键的依赖关系。 下面的CREATE MASTER KEY T-SQL语句用于在master数据库下创建主密钥，并且该主密钥将通过复杂的密码进行加密。 最好将该密码备份保存在安全的地方：

 
USE master
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='Az_Y@89|87';
GO
 

After creating the master key successfully, the next step now is to create the Certificate that will be used to encrypt the Database Encryption Key. The Certificate will be protected by the previously created master key. The Certificate can be created using the CREATE CERTIFICATE T-SQL statement below:

成功创建主密钥后，下一步是创建将用于加密数据库加密密钥的证书。 证书将受到先前创建的主密钥的保护。 可以使用下面的CREATE CERTIFICATE T-SQL语句创建证书 ：

 
CREATE CERTIFICATE TDE_AVG
WITH SUBJECT='TDE_on_AVG_test';
GO
 

To make sure that the certificate is created successfully, you can query the sys.certificate system object for all certificates that are created under the current SQL Server instance as below:

要确保成功创建证书，可以查询sys.certificate系统对象以获取在当前SQL Server实例下创建的所有证书，如下所示：

 
SELECT name, certificate_id, principal_id, pvt_key_encryption_type_desc, start_date, expiry_date
FROM sys.certificates;
 

The result will show us that the TDE_AVG certificate is created successfully, encrypted by the master key and valid for one year only:

结果将向我们显示TDE_AVG证书已成功创建，已通过主密钥加密，并且有效期仅为一年：

The Certificate is ready now, so we will start working at the database side. We will use the CREATE DATABASE ENCRYPTION KEY T-SQL command to create the TDE_Test database encryption key that is encrypted by the TDE_AVG certificate created previously, specifying the encryption algorithm, which is the Advanced Encryption Standard (AES) in our case with a 128 bit key length as follows:

证书现已准备就绪，因此我们将在数据库端开始工作。 我们将使用CREATE DATABASE ENCRYPTION KEY T-SQL命令创建由先前创建的TDE_AVG证书加密的TDE_Test数据库加密密钥，并指定加密算法，在本例中为128位高级加密标准（AES）密钥长度如下：

 
USE TDE_Test
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TDE_AVG;
GO
 

The last step in configuring the TDE is to turn on the TDE encryption on the TDE_Test database using ALTER DATABASE … SET ENCRYPTION ON T-SQL command below:

配置TDE的最后一步是使用以下ALTER DATABASE…SET ENCRYPTION ON T-SQL命令在TDE_Test数据库上打开TDE加密：

 
ALTER DATABASE TDE_Test
SET ENCRYPTION ON;
GO
 

That is it! The Transparent Data Encryption is configured completely on the TDE_Test database. The sys.dm_database_encryption_keys DMV can be used to list all databases with TDE enabled on the databases:

这就对了！ 透明数据加密已在TDE_Test数据库上完全配置。 sys.dm_database_encryption_keys DMV可用于列出所有在数据库上启用了TDE的数据库：

 
SELECT 
	 DB_NAME(database_id) AS DatabaseName
	,Encryption_State AS EncryptionState
	,key_algorithm AS Algorithm
	,key_length AS KeyLength
FROM sys.dm_database_encryption_keys
GO
 

The result shows that the TDE is enabled on the TDE_Test database, and the value 3 for the database encryption state means that the database is completely Encrypted. But what about the tempdb? We have not configured the TDE on that database!

结果显示，在TDE_Test数据库上启用了TDE，并且数据库加密状态的值3表示数据库已完全加密。 但是，tempdb呢？ 我们尚未在该数据库上配置TDE！

If you encrypt any user database using the Transparent Data Encryption, the physical files of the tempdb will be encrypted automatically, as it may contain data from the TDE enabled user database temporarily, while using temporary tables or cursors. In this way, we will maintain full protection for our data using the TDE feature. The operative point is that, the TDE encryption on the tempdb is transparent from that database users and will have a minimal performance impact on the current SQL instance.

如果使用透明数据加密对任何用户数据库进行加密，则tempdb的物理文件将被自动加密，因为它可能临时包含使用TDE的用户数据库中的数据，同时使用临时表或游标。 这样，我们将使用TDE功能对我们的数据保持完全保护。 有效的点在于，tempdb上的TDE加密对该数据库用户是透明的，并且对当前SQL实例的性能影响最小。

As a best practice, it is better to back up the TDE certificate and the private key associated with that certificate after enabling the TDE on a database. In this way, you will be able to restore the database backup files or attach the database data files on another SQL Server instance. The backup can be performed by running the below BACKUP CERTIFICATE T-SQL command, which will backup both the certificate itself and its private key, providing a complex password to encrypt that private key as below:

作为最佳实践，最好在数据库上启用TDE之后备份TDE证书和与该证书关联的私钥。 这样，您将能够还原数据库备份文件或将数据库数据文件附加到另一个SQL Server实例上。 可以通过运行以下BACKUP CERTIFICATE T-SQL命令执行备份，该命令将备份证书本身及其私钥，并提供一个复杂的密码来加密该私钥，如下所示：

 
USE master
GO
BACKUP CERTIFICATE TDE_AVG
TO FILE = 'C:\TDE_Cert\TDE_AVG'
WITH PRIVATE KEY (file='C:\TDE_Cert\TDE_AVG_Pirvate',
ENCRYPTION BY PASSWORD='Lm_s$$15_12')
 

The generated backup files can be viewed by browsing the path provided in the script or the SQL instance DATA path by default if you do not provide the path as follows:

如果不提供以下路径，则可以通过浏览脚本中提供的路径或默认情况下SQL实例DATA路径来查看生成的备份文件：

At this point, the TDE_Test database is encrypted at the DB41VS SQL Server that acts as the primary replica in the AG40VS Always On Availability Group.

此时，TDE_Test数据库已在DB41VS SQL Server上加密，该数据库充当AG40VS Always On可用性组中的主要副本。

Now we need to add it to the Availability Group in order to replicate it to the DB42VS SQL Server. The Add Database Wizard and New Availability Group Wizard for AlwaysOn Availability Groups do not support adding databases that are already encrypted using the TDE encryption to an Availability Group. If you try to add the TDE_Test database, which is encrypted using TDE, by the Add Database Wizard, the wizard will show you that this is not applicable as the database contains an encryption key as below:

现在，我们需要将其添加到可用性组，以便将其复制到DB42VS SQL Server。 AlwaysOn可用性组的“ 添加数据库向导”和“ 新可用性组向导 ”不支持将已经使用TDE加密加密的数据库添加到可用性组。 如果尝试通过“添加数据库向导”添加使用TDE加密的TDE_Test数据库，该向导将向您显示该数据库不适用，因为该数据库包含如下加密密钥：

This means that we need to add the database to the Availability group manually. We need to perform full backup and transaction log backup for the TDE_Test database, copy it and restore it with NORECOVERY at the DB42VS secondary replica. But recall again that the TDE_Test database is encrypted using Transparent Data Encryption. If we try to restore the TDE_Test database backup to the DB42VS SQL Server, the restore process will fail getting the below error:

这意味着我们需要将数据库手动添加到“可用性”组。 我们需要对TDE_Test数据库执行完整备份和事务日志备份，将其复制并在DB42VS辅助副本上使用NORECOVERY进行还原。 但是，请再次记得使用透明数据加密对TDE_Test数据库进行了加密。 如果我们尝试将TDE_Test数据库备份还原到DB42VS SQL Server，则还原过程将失败，并出现以下错误：

The error indicates that the TDE_Test database is encrypted using a database encryption key that is protected by a certificate, and this certificate is not available on that SQL Server instance. So that, in order to restore this encrypted database backup on a SQL Server instance, the certificate that encrypts the database encryption key should be created on that server. This is why we create a backup for the certificate and its private key when we enable the TDE on our database.

该错误表明TDE_Test数据库已使用受证书保护的数据库加密密钥加密，并且该证书在该SQL Server实例上不可用。 因此，为了在SQL Server实例上还原此加密的数据库备份，应该在该服务器上创建对数据库加密密钥进行加密的证书。 这就是为什么在数据库上启用TDE时为证书及其私钥创建备份的原因。

To do that, we should first create a master key on the secondary server that is encrypted by a complex password, using the CREATE MASTER KEY T-SQL command below:

为此，我们首先应该使用以下CREATE MASTER KEY T-SQL命令在通过复杂密码加密的辅助服务器上创建一个主密钥：

 
USE master
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'Az_Y@89|87'
 

You have the choice to use the same password for the previous master key or use a new password, as the master key is used to encrypt the certificate not the database itself in order to keep it protected. In all cases, the key itself will be different due to how the key generation process is performed.

您可以选择对先前的主密钥使用相同的密码，也可以使用新的密码，因为主密钥用于加密证书而不是数据库本身，以便对其进行保护。 在所有情况下，密钥本身都会因密钥生成过程的执行方式而有所不同。

The next step is to copy the certificate and the private key backup files generated previously from the DB41VS primary replica to the DB42VS secondary server. You can keep it in the default data folder on the secondary serve, or in a custom folder where you should provide that bath in the CREATE CERTIFICATE T-SQL statement. The private key of the certificate should be decrypted by the same password that was used to encrypt it when the backup was created:

下一步是将先前从DB41VS主副本生成的证书和私钥备份文件复制到DB42VS辅助服务器。 您可以将其保留在辅助服务器上的默认数据文件夹中，或保留在应在CREATE CERTIFICATE T-SQL语句中提供该浴的自定义文件夹中。 证书的私钥应使用创建备份时用于对其加密的相同密码解密：

 
CREATE CERTIFICATE TDE_AVG
FROM FILE='F:\TDE_Cert\TDE_AVG'
WITH PRIVATE KEY (
FILE = 'F:\TDE_Cert\TDE_AVG_Pirvate',
DECRYPTION BY PASSWORD='Lm_s$$15_12')
 

Now the certificate that encrypts the TDE_Test database is available in the secondary replica. We will create a full back and transaction log backup from the TDE_Test database in the primary replica and copy it to the secondary replica:

现在，加密TDE_Test数据库的证书在辅助副本中可用。 我们将从主副本中的TDE_Test数据库创建完整备份和事务日志备份，并将其复制到辅助副本中：

 
BACKUP DATABASE [TDE_Test] 
TO  DISK = N'F:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\TDE_Test.bak' WITH NOFORMAT, NOINIT,  
NAME = N'TDE_Test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
 
BACKUP LOG [TDE_Test] TO  DISK = N'F:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\TDE_Test.trn' WITH NOFORMAT, NOINIT,  
NAME = N'TDE_Test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
 

On the primary replica we will add the database to the Availability Group using the ALTER AVAILABILITY GROUP… ADD DATABASE T-SQL command as follows:

在主副本上，我们将使用ALTER AVAILABILITY GROUP…ADD DATABASE T-SQL命令将数据库添加到可用性组，如下所示：

 
USE master 
GO
ALTER AVAILABILITY GROUP AG40VS ADD DATABASE [TDE_Test]
 

You can make sure that the database is added successfully to the Availability Group by expanding the AG40VS Availability Groups node in the DB41VS primary server and you will find the database under the Availability Databases node:

您可以通过在DB41VS主服务器中展开AG40VS可用性组节点来确保数据库已成功添加到可用性组，并且您将在“可用性数据库”节点下找到该数据库：

Once the backup files are copied to the secondary server, we will restore the full backup and the transaction log backup on that server using WITH NORECOVERY option:

将备份文件复制到辅助服务器后，我们将使用WITH NORECOVERY选项在该服务器上还原完整备份和事务日志备份：

 
USE [master]
RESTORE DATABASE [TDE_Test] FROM  DISK = N'F:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\TDE_Test.bak' 
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
GO
 
RESTORE LOG [TDE_Test] FROM  DISK = N'F:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\TDE_Test.trn' WITH  FILE = 1,  
NORECOVERY,  NOUNLOAD,  STATS = 10
GO
 

The restore process will complete successfully this time as the same certificate that encrypt the database encryption key is created on the secondary server. The database is available now on the secondary replica and waiting in RESTORING state.

这次还原过程将成功完成，因为在辅助服务器上创建了用于加密数据库加密密钥的相同证书。 该数据库现在在辅助副本上可用，并处于RESTORING状态。

At the secondary replica side, we need to join the database to the availability group using the ALTER AVAILABILITY GROUP T-SQL command below:

在辅助副本端，我们需要使用下面的ALTER AVAILABILITY GROUP T-SQL命令将数据库加入可用性组：

 
USE master
GO 
ALTER DATABASE TDE_Test SET HADR AVAILABILITY GROUP = AG40VS;
 

To make sure that the TDE_Test database is replicated to the DB42VS SQL Server, expand the Databases node at the DB42VS SQL Server and you will see the TDE_Test database in synchronized state:

要确保将TDE_Test数据库复制到DB42VS SQL Server，请展开DB42VS SQL Server上的Databases节点，您将看到TDE_Test数据库处于同步状态：

A manual failover test can be performed to make sure that our database, with TDE enabled on it, is added completely to the Availability Group and working fine. Right-click on the AG40VS Availability Group and choose Failover option:

可以执行手动故障转移测试，以确保将启用了TDE的数据库完全添加到可用性组中，并且可以正常工作。 右键单击AG40VS可用性组，然后选择“故障转移”选项：

Go through this simple wizard and failover the AG40VS Availability Group to the DB42VS SQL server. The DB42VS is the primary replica now with no issue. You can browse the database and run few SELECT statements to make sure that it is working fine:

通过这个简单的向导，将AG40VS可用性组故障转移到DB42VS SQL服务器。 现在，DB42VS是主要副本，没有问题。 您可以浏览数据库并运行一些SELECT语句以确保其正常运行：

结论 (Conclusion)

Securing and encrypting sensitive data stored in our databases is very important, especially the databases that store the organization’s financial data and customers’ confidential information. SQL Server Transparent Data Encryption is an encryption feature that provides encryption on the database file level, as it encrypts the database data, logs, backup and snapshot files. Also, the database should be added to a high availability and disaster recovery site in order to be available and online for the users all the time.

保护和加密存储在我们数据库中的敏感数据非常重要，尤其是存储组织财务数据和客户机密信息的数据库。 SQL Server透明数据加密是一项加密功能，它在对数据库数据，日志，备份和快照文件进行加密时，在数据库文件级别提供加密。 此外，应将数据库添加到高可用性和灾难恢复站点，以便用户始终可以使用和联机。

Always On Availability Groups is the best high availability and disaster recovery choice for us. Adding a database that is encrypted by the TDE feature to the Availability Group by the wizard is not supported. In this article, we described how to do that manually step by step.

永远在线的可用性组是我们最好的高可用性和灾难恢复选择。 不支持通过向导将由TDE功能加密的数据库添加到可用性组。 在本文中，我们逐步介绍了如何手动执行该操作。

翻译自: https://www.sqlshack.com/how-to-add-a-tde-encrypted-user-database-to-an-always-on-availability-group/

tde数据库加密