透明数据加密 (TDE)Transparent Data Encryption (TDE)

适用对象:是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

透明数据加密 (TDE) 加密 SQL ServerSQL ServerAzure SQL DatabaseAzure SQL DatabaseAzure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW) 数据文件,称为加密空闲数据。Transparent Data Encryption (TDE) encrypts SQL ServerSQL Server, Azure SQL DatabaseAzure SQL Database, and Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW) data files, known as encrypting data at rest. 您可以采取一些预防措施来帮助保护数据库的安全,如设计一个安全系统、加密机密资产以及在数据库服务器的周围构建防火墙。You can take several precautions to help secure the database such as designing a secure system, encrypting confidential assets, and building a firewall around the database servers. 但是,如果遇到物理介质(如驱动器或备份磁带)被盗的情况,恶意破坏方只需还原或附加数据库即可浏览数据。However, in a scenario where the physical media (such as drives or backup tapes) are stolen, a malicious party can just restore or attach the database and browse the data. 一种解决方案是加密数据库中的敏感数据,并通过证书保护用于加密数据的密钥。One solution is to encrypt the sensitive data in the database and protect the keys that are used to encrypt the data with a certificate. 这可以防止任何没有密钥的人使用这些数据,但这种保护必须事先计划。This prevents anyone without the keys from using the data, but this kind of protection must be planned in advance.

TDE 可对数据和日志文件执行实时 I/O 加密和解密。TDE performs real-time I/O encryption and decryption of the data and log files. 这种加密使用数据库加密密钥 (DEK),该密钥存储在数据库引导记录中以供恢复时使用。The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. DEK 是使用存储在服务器的 master 数据库中的证书保护的对称密钥,或者是由 EKM 模块保护的非对称密钥。The DEK is a symmetric key secured by using a certificate stored in the master database of the server or an asymmetric key protected by an EKM module. TDE 保护“处于休眠状态”的数据,即数据和日志文件。TDE protects data "at rest", meaning the data and log files. 它提供了遵从许多法律、法规和各个行业建立的准则的能力。It provides the ability to comply with many laws, regulations, and guidelines established in various industries. 软件开发人员籍此可以使用 AES 和 3DES 加密算法来加密数据,且无需更改现有的应用程序。This enables software developers to encrypt data by using AES and 3DES encryption algorithms without changing existing applications.

关于 TDEAbout TDE

数据库文件的加密在页级别执行。Encryption of the database file is performed at the page level. 已加密数据库中的页在写入磁盘之前会进行加密,在读入内存时会进行解密。The pages in an encrypted database are encrypted before they are written to disk and decrypted when read into memory. TDE 不会增加已加密数据库的大小。TDE does not increase the size of the encrypted database.

适用于 SQL 数据库SQL Database 的信息Information applicable to SQL 数据库SQL Database

当将 TDE 与 SQL 数据库SQL Database V12 一起使用时,SQL 数据库SQL Database 将自动创建存储在 master 数据库中的服务器级别证书。When using TDE with SQL 数据库SQL Database V12, the server-level certificate stored in the master database is automatically created for you by SQL 数据库SQL Database. 若要在 SQL 数据库SQL Database 上移动 TDE 数据库,不需要为移动操作解密数据库。To move a TDE database on SQL 数据库SQL Database, you do not have to decrypt the database for the move operation. 有关将 TDE 与 SQL 数据库SQL Database 一起使用的详细信息,请参阅 Azure SQL 数据库的透明数据加密For more information on utilizing TDE with SQL 数据库SQL Database, see Transparent Data Encryption with Azure SQL Database.

适用于 SQL ServerSQL Server 的信息Information applicable to SQL ServerSQL Server

对数据库实施保护措施后,可以通过使用正确的证书还原此数据库。After it is secured, the database can be restored by using the correct certificate. 有关证书的详细信息,请参阅 SQL Server Certificates and Asymmetric KeysFor more information about certificates, see SQL Server Certificates and Asymmetric Keys.

启用 TDE 时,应该立即备份证书和与证书相关联的私钥。When enabling TDE, you should immediately back up the certificate and the private key associated with the certificate. 如果证书变为不可用,或者如果必须在另一台服务器上还原或附加数据库,则必须同时具有证书和私钥的备份,否则将无法打开该数据库。If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database. 即使不再对数据库启用 TDE,也应该保留加密证书。The encrypting certificate should be retained even if TDE is no longer enabled on the database. 即使数据库未加密,事务日志的某些部分仍可能保持受到保护,但在执行数据库的完整备份前,对于某些操作可能需要证书。Even though the database is not encrypted, parts of the transaction log may still remain protected, and the certificate may be needed for some operations until the full backup of the database is performed. 超过过期日期的证书仍可以用于通过 TDE 加密和解密数据。A certificate that has exceeded its expiration date can still be used to encrypt and decrypt data with TDE.

加密层次结构Encryption Hierarchy

下图显示了 TDE 加密体系结构。The following illustration shows the architecture of TDE encryption. 仅数据库级项目(在 SQL 数据库SQL Database上使用 TDE 时,用户可配置数据库加密密钥和 ALTER DATABASE 部分。Only the database level items (the database encryption key and ALTER DATABASE portions are user-configurable when using TDE on SQL 数据库SQL Database.

显示主题中介绍的层次结构。Displays the hierarchy described in the topic.

使用透明数据加密Using Transparent Data Encryption

若要使用 TDE,请按以下步骤操作。To use TDE, follow these steps.

适用于SQL ServerSQL ServerApplies to: SQL ServerSQL Server.

  • 创建主密钥Create a master key

  • 创建或获取由主密钥保护的证书Create or obtain a certificate protected by the master key

  • 创建数据库加密密钥并通过此证书保护该密钥Create a database encryption key and protect it by the certificate

  • 将数据库设置为使用加密Set the database to use encryption

下面的示例演示如何使用安装在名为 AdventureWorks2012 的服务器上的证书加密和解密 MyServerCert数据库。The following example illustrates encrypting and decrypting the AdventureWorks2012 database using a certificate installed on the server named MyServerCert.

USE master;  
GO  
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>';  
go  
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';  
go  
USE AdventureWorks2012;  
GO  
CREATE DATABASE ENCRYPTION KEY  
WITH ALGORITHM = AES_128  
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;  
GO  
ALTER DATABASE AdventureWorks2012  
SET ENCRYPTION ON;  
GO  

加密和解密操作由 SQL ServerSQL Server安排在后台线程中执行。The encryption and decryption operations are scheduled on background threads by SQL ServerSQL Server. 您可以使用本主题后面部分显示的列表中的目录视图和动态管理视图查看这些操作的状态。You can view the status of these operations using the catalog views and dynamic management views in the list that appears later in this topic.

注意

启用了 TDE 的数据库的备份文件也使用数据库加密密钥进行加密。Backup files of databases that have TDE enabled are also encrypted by using the database encryption key. 因此,当您还原这些备份时,用于保护数据库加密密钥的证书必须可用。As a result, when you restore these backups, the certificate protecting the database encryption key must be available. 也就是说,除了备份数据库之外,您还要确保自己保留了服务器证书的备份以防数据丢失。This means that in addition to backing up the database, you have to make sure that you maintain backups of the server certificates to prevent data loss. 如果证书不再可用,将会导致数据丢失。Data loss will result if the certificate is no longer available. 有关详细信息,请参阅 SQL Server Certificates and Asymmetric KeysFor more information, see SQL Server Certificates and Asymmetric Keys.

命令和函数Commands and Functions

TDE 证书必须使用数据库主密钥加密才能被下列语句接受。The TDE certificates must be encrypted by the database master key to be accepted by the following statements. 如果它们仅用密码加密,这些语句将拒绝将它们视为加密程序。If they're encrypted by password only, the statements will reject them as encryptors.

重要

在 TDE 使用证书之后将证书改为用密码保护将会导致数据库在重新启动后无法访问。Altering the certificates to be password-protected after they are used by TDE will cause the database to become inaccessible after a restart.

下表提供了 TDE 命令和函数的链接和说明。The following table provides links and explanations of TDE commands and functions.

命令或函数Command or function 用途Purpose
CREATE DATABASE ENCRYPTION KEY (Transact-SQL)CREATE DATABASE ENCRYPTION KEY (Transact-SQL) 创建一个用于加密数据库的密钥。Creates a key that is used to encrypt a database.
ALTER DATABASE ENCRYPTION KEY (Transact-SQL)ALTER DATABASE ENCRYPTION KEY (Transact-SQL) 更改用于加密数据库的密钥。Changes the key that is used to encrypt a database.
DROP DATABASE ENCRYPTION KEY (Transact-SQL)DROP DATABASE ENCRYPTION KEY (Transact-SQL) 删除用于加密数据库的密钥。Removes the key that was used to encrypt a database.
ALTER DATABASE SET 选项 (Transact-SQL)ALTER DATABASE SET Options (Transact-SQL) 介绍用来启用 TDE 的 ALTER DATABASE 选项。Explains the ALTER DATABASE option that is used to enable TDE.

目录视图和动态管理视图Catalog Views and Dynamic Management Views

下表显示了 TDE 目录视图和动态管理视图。The following table shows TDE catalog views and dynamic management views.

目录视图或动态管理视图Catalog view or dynamic management view 用途Purpose
sys.databases (Transact-SQL)sys.databases (Transact-SQL) 显示数据库信息的目录视图。Catalog view that displays database information.
sys.certificates (Transact-SQL)sys.certificates (Transact-SQL) 显示数据库中的证书的目录视图。Catalog view that shows the certificates in a database.
sys.dm_database_encryption_keys (Transact-SQL)sys.dm_database_encryption_keys (Transact-SQL) 提供有关数据库中使用的加密密钥的信息以及数据库加密状态的动态管理视图。Dynamic management view that provides information about the encryption keys used in a database, and the state of encryption of a database.

权限Permissions

如上表中所述,TDE 的每项功能和每个命令都有各自的权限要求。Each TDE feature and command has individual permission requirements, described in the tables shown earlier.

查看 TDE 所涉及的元数据要求拥有对证书的 VIEW DEFINITION 权限。Viewing the metadata involved with TDE requires the VIEW DEFINITION permission on the certificate.

注意事项Considerations

当进行数据库加密操作的重新加密扫描时,将禁用对数据库的维护操作。While a re-encryption scan for a database encryption operation is in progress, maintenance operations to the database are disabled. 您可以使用数据库的单用户模式设置来执行维护操作。You can use the single user mode setting for the database to perform the maintenance operation. 有关详细信息,请参阅 将数据库设置为单用户模式For more information, see Set a Database to Single-user Mode.

可以使用 sys.dm_database_encryption_keys 动态管理视图来确定数据库加密状态。You can find the state of the database encryption using the sys.dm_database_encryption_keys dynamic management view. 有关详细信息,请参阅本主题前面的“目录视图和动态管理视图”部分。For more information, see the "Catalog Views and Dynamic Management Views"section earlier in this topic).

在 TDE 过程中,数据库中的所有文件和文件组都进行加密。In TDE, all files and filegroups in the database are encrypted. 如果将数据库中的任何文件组标记为 READ ONLY,数据库加密操作将会失败。If any filegroups in a database are marked READ ONLY, the database encryption operation will fail.

如果某个数据库正在用于数据库镜像或日志传送,则两个数据库都将进行加密。If a database is being used in database mirroring or log shipping, both databases will be encrypted. 日志事务将以加密形式在它们之间发送。The log transactions will be encrypted when sent between them.

重要

当数据库设置为加密时,将加密全文索引。Full-text indexes will be encrypted when a database is set for encryption. 使用 SQL Server 2008 之前版本创建的全文索引将会在升级到 SQL Server 2008 或更高版本时导入数据库,并通过 TDE 进行加密。Full-text indexes created prior to SQL Server 2008 will be imported into the database during upgrade to SQL Server 2008 or greater and they will be encrypted by TDE.

提示

若要监视数据库的 TDE 状态更改,请使用 SQL Server Audit 或 SQL 数据库审核。To monitor changes in the TDE status of a database, use SQL Server Audit or SQL Database Auditing. 就 SQL Server 而言,在审核操作组 DATABASE_CHANGE_GROUP 下跟踪 TDE,可在 SQL Server 审核操作组和操作中找到该组。For SQL Server, TDE is tracked under the audit action group DATABASE_CHANGE_GROUP which can be found in SQL Server Audit Action Groups and Actions.

限制Restrictions

在初始数据库加密、密钥更改或数据库解密期间,不允许执行下列操作:The following operations are not allowed during initial database encryption, key change, or database decryption:

  • 从数据库中的文件组中删除文件Dropping a file from a filegroup in the database

  • 删除数据库Dropping the database

  • 使数据库脱机Taking the database offline

  • 分离数据库Detaching a database

  • 将数据库或文件组转换为 READ ONLY 状态Transitioning a database or filegroup into a READ ONLY state

在执行 CREATE DATABASE ENCRYPTION KEY、ALTER DATABASE ENCRYPTION KEY、DROP DATABASE ENCRYPTION KEY 或 ALTER DATABASE...SET ENCRYPTION 语句期间,不允许执行下列操作。The following operations are not allowed during the CREATE DATABASE ENCRYPTION KEY, ALTER DATABASE ENCRYPTION KEY, DROP DATABASE ENCRYPTION KEY, or ALTER DATABASE...SET ENCRYPTION statements.

  • 从数据库中的文件组中删除文件。Dropping a file from a filegroup in the database.

  • 删除数据库。Dropping the database.

  • 使数据库脱机。Taking the database offline.

  • 分离数据库。Detaching a database.

  • 将数据库或文件组转换为 READ ONLY 状态。Transitioning a database or filegroup into a READ ONLY state.

  • 使用 ALTER DATABASE 命令。Using an ALTER DATABASE command.

  • 启动数据库或数据库文件备份。Starting a database or database file backup.

  • 启动数据库或数据库文件还原。Starting a database or database file restore.

  • 创建快照。Creating a snapshot.

下列操作或条件将阻止执行 CREATE DATABASE ENCRYPTION KEY、ALTER DATABASE ENCRYPTION KEY、DROP DATABASE ENCRYPTION KEY 或 ALTER DATABASE...SET ENCRYPTION 语句。The following operations or conditions will prevent the CREATE DATABASE ENCRYPTION KEY, ALTER DATABASE ENCRYPTION KEY, DROP DATABASE ENCRYPTION KEY, or ALTER DATABASE...SET ENCRYPTION statements.

  • 数据库为只读或包含任何只读文件组。The database is read-only or has any read-only file groups.

  • 正在执行 ALTER DATABASE 命令。An ALTER DATABASE command is executing.

  • 正在进行任何数据备份。Any data backup is running.

  • 数据处于脱机或还原状态。The database is in an offline or restore condition.

  • 正在创建快照。A snapshot is in progress.

  • 数据库维护任务。Database maintenance tasks.

当创建数据库文件时,如果启用了 TDE,则即时文件初始化功能不可用。When creating database files, instant file initialization is not available when TDE is enabled.

要使用非对称密钥对数据库加密密钥进行加密,非对称密钥必须驻留在可扩展密钥管理提供程序上。In order to encrypt the database encryption key with an asymmetric key, the asymmetric key must reside on an extensible key management provider.

透明数据加密与事务日志Transparent Data Encryption and Transaction Logs

允许数据库使用 TDE 具有将虚拟事务日志的剩余部分“清零”以强制加密下一个虚拟事务日志的效果。Enabling a database to use TDE has the effect of "zeroing out" the remaining part of the virtual transaction log to force the next virtual transaction log. 这可以保证在数据库设置为加密后事务日志中不会留有明文。This guarantees that no clear text is left in the transaction logs after the database is set for encryption. 可通过查看 encryption_state 视图中的 sys.dm_database_encryption_keys 列来确定日志文件加密状态,如以下示例所示:You can find the status of the log file encryption by viewing the encryption_state column in the sys.dm_database_encryption_keys view, as in this example:

USE AdventureWorks2012;  
GO  
/* The value 3 represents an encrypted state   
   on the database and transaction logs. */  
SELECT *  
FROM sys.dm_database_encryption_keys  
WHERE encryption_state = 3;  
GO  

有关 SQL ServerSQL Server 日志文件体系结构的详细信息,请参阅事务日志 (SQL Server)For more information about the SQL ServerSQL Server log file architecture, see The Transaction Log (SQL Server).

所有在数据库加密密钥更改前写入事务日志的数据都将使用之前的数据库加密密钥加密。All data written to the transaction log before a change in the database encryption key will be encrypted by using the previous database encryption key.

在数据库加密密钥修改过两次后,必须执行日志备份才能再次对数据库加密密钥进行修改。After a database encryption key has been modified twice, a log backup must be performed before the database encryption key can be modified again.

透明数据加密与 tempdb 系统数据库Transparent Data Encryption and the tempdb System Database

如果 SQL ServerSQL Server 实例中的任何其他数据库是使用 TDE 加密的,则会加密 tempdb 系统数据库。The tempdb system database will be encrypted if any other database on the instance of SQL ServerSQL Server is encrypted by using TDE. 这可能会对同一个 SQL ServerSQL Server实例上的未加密数据库产生性能影响。This might have a performance effect for unencrypted databases on the same instance of SQL ServerSQL Server. 有关 tempdb 系统数据库的详细信息,请参阅 tempdb 数据库For more information about the tempdb system database, see tempdb Database.

透明数据加密和复制Transparent Data Encryption and Replication

复制不会以加密形式从启用了 TDE 的数据库中自动复制数据。Replication does not automatically replicate data from a TDE-enabled database in an encrypted form. 如果您想保护分发和订阅服务器数据库,则必须单独启用 TDE。You must separately enable TDE if you want to protect the distribution and subscriber databases. 快照复制以及用于事务和合并复制的初始数据分发,都能够在未加密的中间文件(例如 bcp 文件)中存储数据。Snapshot replication, as well as the initial distribution of data for transactional and merge replication, can store data in unencrypted intermediate files; for example, the bcp files. 在事务或合并复制期间,可以启用加密来保护通信信道。During transactional or merge replication, encryption can be enabled to protect the communication channel. 有关详细信息,请参阅启用数据库引擎的加密连接(SQL Server 配置管理器)For more information, see Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager).

透明数据加密和 FILESTREAM 数据Transparent Data Encryption and FILESTREAM DATA

即使启用了 TDE,也不会加密 FILESTREAM 数据。FILESTREAM data is not encrypted even when TDE is enabled.

透明数据加密 (TDE) 扫描Transparent Data Encryption (TDE) scan

为了在数据库上启用透明数据加密 (TDE),SQL ServerSQL Server 必须执行加密扫描,此操作会从数据文件将每个页面读入缓冲池,然后将加密的页面写回磁盘。In order to enable Transparent Data Encryption (TDE) on a database, SQL ServerSQL Server must perform an encryption scan that reads each page from the data file(s) into the buffer pool, and then writes the encrypted pages back out to disk. 为了使用户能够更好地控制加密扫描,SQL Server 2019 (15.x)SQL Server 2019 (15.x) 引入了 TDE 扫描 - 暂停和恢复语法,可以实现当系统上的工作负荷繁重时或在关键业务时间内暂停扫描,然后稍后再恢复扫描。To provide the user with more control over the encryption scan, SQL Server 2019 (15.x)SQL Server 2019 (15.x) introduces TDE scan - suspend and resume syntax so that you can pause the scan while the workload on the system is heavy, or during business-critical hours, and then resume the scan later.

使用以下语法暂停 TDE 加密扫描:Use the following syntax to pause the TDE encryption scan:

ALTER DATABASE <db_name> SET ENCRYPTION SUSPEND;

同样,以下语法将恢复 TDE 加密扫描:Similarly, the following syntax resumes the TDE encryption scan:

ALTER DATABASE <db_name> SET ENCRYPTION RESUME;

为了显示加密扫描的当前状态,已将 encryption_scan_state 添加到了 sys.dm_database_encryption_keys 动态管理视图中。To show the current state of the encryption scan, encryption_scan_state has been added to the sys.dm_database_encryption_keys dynamic management view. 还有一个名为 encryption_scan_modify_date 的新列,此列将包含上次加密扫描状态更改的日期和时间。There is also a new column called encryption_scan_modify_date which will contain the date and time of the last encryption scan state change. 另请注意,如果在加密扫描处于暂停状态时重启 SQL ServerSQL Server 实例,启动时系统将在错误日志中记录一条消息,指示存在已暂停的现有扫描。Also note that if the SQL ServerSQL Server instance is restarted while the encryption scan is in a suspended state, a message will be logged in the error log on startup indicating that there is an existing scan that has been paused.

透明数据加密和缓冲池扩展Transparent Data Encryption and Buffer Pool Extension

在使用 TDE 加密数据库时不对与缓冲池扩展 (BPE) 相关的文件进行加密。Files related to buffer pool extension (BPE) are not encrypted when database is encrypted using TDE. 必须对与 BPE 相关的文件使用文件系统级别的加密工具,如 Bitlocker 或 EFS。You must use file system level encryption tools like BitLocker or EFS for BPE related files.

透明数据加密和内存中 OLTPTransparent Data Encryption and In-Memory OLTP

可在拥有内存中 OLTP 对象的数据库上启用 TDE。TDE can be enabled on a database that has In-Memory OLTP objects. SQL Server 2016 (13.x)SQL Server 2016 (13.x)Azure SQL DatabaseAzure SQL Database 中,如果启用 TDE,将对内存中 OLTP 日志记录和数据加密。In SQL Server 2016 (13.x)SQL Server 2016 (13.x) and Azure SQL DatabaseAzure SQL Database In-Memory OLTP log records and data are encrypted if TDE is enabled. SQL Server 2014 (12.x)SQL Server 2014 (12.x) 中,如果启用 TDE,将对内存中 OLTP 日志记录加密,但不对 MEMORY_OPTIMIZED_DATA 文件组中的文件加密。In SQL Server 2014 (12.x)SQL Server 2014 (12.x) In-Memory OLTP log records are encrypted if TDE is enabled, but files in the MEMORY_OPTIMIZED_DATA filegroup are not encrypted.

将受 TDE 保护的数据库移到其他 SQL ServerMove a TDE Protected Database to Another SQL Server
使用 EKM 在 SQL Server 上启用 TDEEnable TDE on SQL Server Using EKM
使用 Azure 密钥保管库的可扩展密钥管理 (SQL Server)Extensible Key Management Using Azure Key Vault (SQL Server)

借助 Azure SQL 数据库实现透明数据加密Transparent Data Encryption with Azure SQL Database
开始在 SQL 数据仓库上使用透明数据加密 (TDE)Get started with Transparent Data Encryption (TDE) on SQL Data Warehouse
SQL Server 加密SQL Server Encryption
SQL Server 和数据库加密密钥(数据库引擎)SQL Server and Database Encryption Keys (Database Engine)

另请参阅See Also

SQL Server 数据库引擎和 Azure SQL 数据库的安全中心 Security Center for SQL Server Database Engine and Azure SQL Database
FILESTREAM (SQL Server)FILESTREAM (SQL Server)