透明資料加密 (TDE)Transparent Data Encryption (TDE)

適用於: 是SQL Server 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

透明資料加密 (TDE) 會加密 SQL ServerSQL ServerAzure SQL DatabaseAzure SQL DatabaseAzure SQL 資料倉儲Azure SQL Data Warehouse 資料檔案,一般稱之為靜止的加密資料。Transparent Data Encryption (TDE) encrypts SQL ServerSQL Server, Azure SQL DatabaseAzure SQL Database, and Azure SQL 資料倉儲Azure SQL Data Warehouse 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 DatabaseSQL Database 的資訊Information applicable to SQL DatabaseSQL Database

搭配 SQL DatabaseSQL Database V12 使用 TDE 時,SQL DatabaseSQL Database 會自動為您建立儲存在主要資料庫中的伺服器層級憑證。When using TDE with SQL DatabaseSQL Database V12, the server-level certificate stored in the master database is automatically created for you by SQL DatabaseSQL Database. 若要移動 SQL DatabaseSQL Database 上的 TDE 資料庫,您不需要將資料解密來進行移動作業。To move a TDE database on SQL DatabaseSQL Database, you do not have to decrypt the database for the move operation. 如需有關搭配 SQL DatabaseSQL Database 運用 TDE 的詳細資訊,請參閱搭配 Azure SQL Database 的透明資料加密For more information on utilizing TDE with SQL DatabaseSQL 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 Keys>。For 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 DatabaseSQL 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 DatabaseSQL 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. 如需詳細資訊,請參閱啟用 Database Engine 的加密連接 (SQL Server 組態管理員)For more information, see Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager).

透明資料加密和 FILESTREAM DATATransparent 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 預覽SQL Server 2019 preview 導入了 TDE 掃描暫止和繼續語法,以便您在系統的工作負載偏高時或商務關鍵性時段能夠暫停掃描,且稍後再繼續掃描。To provide the user with more control over the encryption scan, SQL Server 2019 預覽SQL Server 2019 preview 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;

為了顯示目前的加密掃描狀態,sys.dm_database_encryption_keys 動態管理檢視中已新增 encryption_scan_stateTo 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

TDE 可在具有記憶體中 OLTP 物件的資料庫上啟用。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 Database 的透明資料加密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 和資料庫加密金鑰 (Database Engine)SQL Server and Database Encryption Keys (Database Engine)

另請參閱See Also

SQL Server Database Engine 和 Azure SQL Database 的資訊安全中心 Security Center for SQL Server Database Engine and Azure SQL Database
FILESTREAM (SQL Server)FILESTREAM (SQL Server)