透明加密tde_如何在SQL Server中配置透明数据加密（TDE）

透明加密tde

简介与概述 (Introduction and Overview)

Transparent Data Encryption (TDE) was introduced in SQL Server 2008. Its main purpose was to protect data by encrypting the physical files, both the data (mdf) and log (ldf) files (as opposed to the actual data stored within the database). Transparent Data Encryption Encrypts SQL Server, Azure SQL Databases, and Azure SQL Data Warehouse data files.

SQL Server 2008中引入了透明数据加密（TDE）。其主要目的是通过对物理文件（数据（mdf）和日志（ldf）文件）（而不是存储在数据库中的实际数据）进行加密来保护数据。 。 透明数据加密对SQL Server，Azure SQL数据库和Azure SQL数据仓库数据文件进行加密。

 

This technology was designed to have the entire encryption process be completely transparent to the applications accessing the database. It does this by using either Advanced Encryption Standard (AES), or Triple DES, encrypting the file pages and then decrypted as the information goes into memory. This inhibits limitations from querying the data in an encrypted database. This is essentially real time I/O encryption and decryption and does not increase the size of said database.

该技术旨在使整个加密过程对访问数据库的应用程序完全透明。 它通过使用高级加密标准（AES）或Triple DES来实现此目的，先加密文件页面，然后在信息进入内存时解密。 这抑制了查询加密数据库中数据的限制。 这本质上是实时的I / O加密和解密，不会增加所述数据库的大小。

Also note, that as a result of Transparent Data Encryption, database backups will also be encrypted. In the event that a backup of the database gets lost or stolen, the culprit will not be able to restore the database without the appropriate certificate, keys and passwords.

还要注意，由于透明数据加密，数据库备份也将被加密。 如果数据库的备份丢失或被盗，罪魁祸首将无法在没有适当的证书，密钥和密码的情况下还原数据库。

Also, the TempDB database will be automatically encrypted. Since the tempdb is used by all user databases (processing/storing temporary objects). You shouldn’t notice much of a difference in how Transparent Data Encryption operates, but this is good to know and often overlooked. What good is an encrypted database if the data placed in TempDB isn’t encrypted?

另外，TempDB数据库将被自动加密。 由于tempdb被所有用户数据库使用（处理/存储临时对象）。 在透明数据加密的操作方式上，您应该不会注意到太多差异，但这是一个很好的认识，并且经常被忽略。 如果放置在TempDB中的数据未加密，那么加密的数据库有什么用？

However, this does not encrypt the data “across the wire” so to speak. If there is a requirement to encrypt data across the network an SSL connection must be implemented on the clients. (For more information regarding this please see this link)

但是，可以这么说，这不会“在线”加密数据。 如果需要通过网络加密数据，则必须在客户端上实现SSL连接。 （有关此的更多信息，请参见此链接 ）

If you’re a DBA there is a very strong chance that you are in charge of securing some very sensitive information.

如果您是DBA，则很有可能负责保护某些非常敏感的信息。

符合透明数据加密要求SQL Server版本 (Transparent Data Encryption Eligible SQL Server Editions)

First we must determine the correct version of SQL Server that allows Transparent Data Encryption. I like to call it an expensive feature as it requires Enterprise Editions. It also works with Developer Edition, but of course, this is just for testing and development purposes. When implementing this in a production environment you must have the correct version of SQL Server. I’ve listed the eligible editions below.

首先，我们必须确定允许透明数据加密SQL Server的正确版本。 我喜欢称它为昂贵的功能，因为它需要企业版。 它也可以与Developer Edition一起使用，但是当然，这只是出于测试和开发目的。 在生产环境中实施此操作时，必须具有正确版本SQL Server。 我在下面列出了符合条件的版本。

• SQL 2016 Evaluation, Developer, Enterprise

   

  SQL 2016评估，开发人员，企业
• SQL 2014 Evaluation, Developer, Enterprise

   

  SQL 2014评估，开发人员，企业
• SQL Server 2012 Evaluation, Developer, Enterprise

   

  SQL Server 2012评估，开发人员，企业
• SQL Server 2008 R2 Datacenter, Evaluation, Developer, Enterprise, Datacenter

   

  SQL Server 2008 R2数据中心，评估，开发人员，企业，数据中心
• SQL Server 2008 Evaluation, Developer, Enterprise

   

  SQL Server 2008评估，开发人员，企业

透明数据加密层次结构 (Transparent Data Encryption Hierarchy )

Now let’s have a quick overview of the Transparent Data Encryption architecture and hierarchy. First we have the Windows Operating System Level Data Protection API, which decrypts the Service Master Key found in the SQL Server instance level. The Server Master Key is created at the time of the initial SQL Server instance setup. From there we go the database level. The Service Master Key encrypts the database Master Key for the master database. The database master key creates a certificate in the master database. Keep in mind that you must create a backup of this certificate. Not only for environmental refreshes but disaster recovery purposes. Once Transparent Data Encryption is enabled on the database you won’t be able to restore or move it another server unless this same certificate has been installed. Keep good (and secure records) of the certificate and password.

现在，让我们快速了解一下透明数据加密体系结构和层次结构。 首先，我们拥有Windows操作系统级别的数据保护API，该API解密在SQL Server实例级别中找到的服务主密钥。 服务器主密钥是在初始SQL Server实例设置时创建的。 从那里我们进入数据库级别。 服务主密钥为master数据库加密数据库主密钥。 数据库主密钥在主数据库中创建一个证书。 请记住，您必须创建此证书的备份。 不仅用于环境刷新，还用于灾难恢复。 一旦在数据库上启用了透明数据加密，您将无法将其还原或移动到另一台服务器，除非已安装了相同的证书。 妥善保存（和安全记录）证书和密码。

The certificate is then used to enable encryption at the database level, thus creating the database encryption key.

然后，该证书用于在数据库级别启用加密，从而创建数据库加密密钥。

To help visualize this process, please refer to the following diagram:

为了帮助可视化此过程，请参考下图：

 

实作 (Implementation)

As always I like to do my work in SQL Server Management Studio. So please open up SSMS and log into the server that you will be using.

和往常一样，我喜欢在SQL Server Management Studio中完成工作。 因此，请打开SSMS并登录到将要使用的服务器。

 

 

 

创建主密钥 (Create Master Key )

We must first create the master key. It must be created in the master database, so as a precautionary measure I like to begin this statement with the USE MASTER command.

我们必须首先创建主密钥。 它必须在master数据库中创建，因此，为了预防起见，我希望使用USE MASTER命令开始此语句。

1.  
    
2.  
   USE Master;
3.  
   GO
4.  
   CREATE MASTER KEY ENCRYPTION
5.  
   BY PASSWORD='InsertStrongPasswordHere';
6.  
   GO
7.  
    

创建受主密钥保护的证书 (Create Certificate protected by master key )

Once the master key is created along with the strong password (that you should remember or save in a secure location), we will go ahead and create the actual certificate.

一旦创建了主密钥和强密码（您应该记住或保存在安全的位置），我们将继续创建实际的证书。

1.  
    
2.  
   CREATE CERTIFICATE TDE_Cert
3.  
   WITH
4.  
   SUBJECT='Database_Encryption';
5.  
   GO
6.  
    

The certificate’s name is “TDE_Cert” and I gave it a generic subject. Some Database Administrators like to put the name of the actual database that they are going to encrypt in there. It is totally up to you.

证书的名称是“ TDE_Cert”，我给它一个通用主题。 一些数据库管理员喜欢在其中放置要加密的实际数据库的名称。 这完全取决于您。

创建数据库加密密钥 (Create Database Encryption Key )

Now, we must utilize our USE command to switch to the database that we wish to encrypt. Then we create a connection or association between the certificate that we just created and the actual database. Then we indicate the type of encryption algorithm we are going to use. In this case it will be AES_256 encryption.

现在，我们必须使用USE命令来切换到我们希望加密的数据库。 然后，我们在刚创建的证书和实际数据库之间创建连接或关联。 然后，我们指出将要使用的加密算法的类型。 在这种情况下，它将是AES_256加密。

1.  
    
2.  
   USE <DB>
3.  
   GO
4.  
   CREATE DATABASE ENCRYPTION KEY
5.  
   WITH ALGORITHM = AES_256
6.  
   ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;
7.  
   GO
8.  
    

启用加密 (Enable Encryption )

Finally, we can enable encryption on our database by using the ALTER DATABASE command.

最后，我们可以使用ALTER DATABASE命令对数据库启用加密。

1.  
    
2.  
   ALTER DATABASE <DB>
3.  
   SET ENCRYPTION ON;
4.  
   GO
5.  
    

Once the encryption is turned on, depending on the size of the database, it may take some time to complete. You can monitor the status by querying the sys.dm_database_encryption_keys DMV.

启用加密后，取决于数据库的大小，可能需要一些时间才能完成。 您可以通过查询sys.dm_database_encryption_keys DMV来监视状态。

备份证书 (Backup Certificate)

It’s important to backup the certificate you created and store it in a secure location. If the server ever goes down and you need to restore it elsewhere, you will have to import the certificate to the server. In certain environments, the DR servers are already stood up and on warm/hot standby, so it’s a good idea to just preemptively import the saved certificate to these servers.

备份创建的证书并将其存储在安全位置非常重要。 如果服务器出现故障，需要将其还原到其他位置，则必须将证书导入服务器。 在某些环境中，DR服务器已经站立并且处于热/热备用状态，因此，最好先抢先将保存的证书导入这些服务器。

1.  
    
2.  
   BACKUP CERTIFICATE TDE_Cert
3.  
   TO FILE = 'C:\temp\TDE_Cert'
4.  
   WITH PRIVATE KEY (file='C:\temp\TDE_CertKey.pvk',
5.  
   ENCRYPTION BY PASSWORD='InsertStrongPasswordHere')
6.  
    

Remember to store the certificate in a safe and available locations (not a temporary one like this example).

请记住将证书存储在安全且可用的位置（而不是像此示例那样的临时位置）。

恢复证书 (Restoring a Certificate)

In order to restore the certificate, you will once again have to create a service master key on the secondary server.

为了还原证书，您将不得不再次在辅助服务器上创建服务主密钥。

1.  
    
2.  
   USE Master;
3.  
   GO
4.  
   CREATE MASTER KEY ENCRYPTION
5.  
   BY PASSWORD='InsertStrongPasswordHere';
6.  
   GO
7.  
    

Once that is done, you must remember where you backed up the certificate and the encryption/decryption password.

完成此操作后，您必须记住备份证书和加密/解密密码的位置。

1.  
    
2.  
   USE MASTER
3.  
   GO
4.  
   CREATE CERTIFICATE TDECert
5.  
   FROM FILE = 'C:\Temp\TDE_Cert'
6.  
   WITH PRIVATE KEY (FILE = 'C:\TDECert_Key.pvk',
7.  
   DECRYPTION BY PASSWORD = 'InsertStrongPasswordHere' );
8.  
    

Be mindful of the paths used in this example. You must specify the path that you have stored the certificate and private key. Also keep good and secure records of the encryption passwords.

请注意本示例中使用的路径。 您必须指定存储证书和私钥的路径。 还应保留加密密码的良好且安全的记录。

Once the certificate is restored to the secondary server you may restore a copy of the encrypted database.

将证书还原到辅助服务器后，您可以还原加密数据库的副本。

Some things to note before applying TDE. There are some drawbacks. Remember that Transparent Data Encryption encrypts the underlying database files including the backups. You can’t just take the files and dump them onto another SQL Server without the appropriate encryption keys and certificates. It does NOT allow for granular user level encryption. If that is the type of encryption you are looking for, you should investigate column level encryption.

应用TDE之前需要注意的一些事情。 有一些缺点。 请记住，透明数据加密会加密基础数据库文件，包括备份。 如果没有适当的加密密钥和证书，您不能只将文件转储到另一个SQL Server中。 它不允许细粒度的用户级别加密。 如果这是您要寻找的加密类型，则应调查列级加密。

翻译自: https://www.sqlshack.com/how-to-configure-transparent-data-encryption-tde-in-sql-server/