Updated: November 23, 2015
Transparent Data Encryption (TDE) encrypts SQL Server and Azure SQL Database 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 performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. 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 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. This enables software developers to encrypt data by using AES and 3DES encryption algorithms without changing existing applications.
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 does not increase the size of the encrypted database.
Information applicable to SQL Database
When using TDE with SQL Database V12 V12 (Preview in some regions) the server-level certificate stored in the master database is automatically created for you by SQL Database. To move a TDE database on SQL Database you must decrypt the database, move the database, and then re-enable TDE on the destination SQL Database. For step-by-step instructions for TDE on SQL Database, see Transparent Data Encryption with Azure SQL Database.
The preview of status of TDE applies even in the subset of geographic regions where version family V12 of SQL Database is announced as now being in general availability status. TDE for SQL Database is not intended for use in production databases until Microsoft announces that TDE is promoted from preview to GA. For more information about SQL Database V12, see What’s new in Azure SQL Database.
Information applicable to SQL Server
After it is secured, the database can be restored by using the correct certificate. For more information about certificates, see SQL Server Certificates and Asymmetric Keys.
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. 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. A certificate that has exceeded its expiration date can still be used to encrypt and decrypt data with TDE.
The following illustration shows the architecture of TDE encryption. Only the database level items (the database encryption key and ALTER DATABASE portions are user-configurable when using TDE on SQL Database.
To use TDE, follow these steps.
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
The following example illustrates encrypting and decrypting the AdventureWorks2012 database using a certificate installed on the server named MyServerCert.
The encryption and decryption operations are scheduled on background threads by SQL 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.
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. For more information, see SQL Server Certificates and Asymmetric Keys.
The TDE certificates must be encrypted by the database master key to be accepted by the following statements. If they are encrypted by password only, the statements will reject them as encryptors.
Altering the certificates to be password-protected after they are used by TDE will cause the database to become inaccessible after a restart.
The following table provides links and explanations of TDE commands and functions.
The following table shows TDE catalog views and dynamic management views.
Each TDE feature and command has individual permission requirements, described in the tables shown earlier.
Viewing the metadata involved with TDE requires the VIEW DEFINITION permission on the certificate.
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.
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).
In TDE, all files and filegroups in the database are encrypted. 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.
Any new full-text indexes will be encrypted when a database is set for encryption. Previously-created full-text indexes will be imported during upgrade and they will be in TDE after the data is loaded into SQL Server. Enabling a full-text index on a column can cause that column’s data to be written in plain text onto the disk during a full-text indexing scan. We recommend that you do not create a full-text index on sensitive encrypted data.
Encrypted data compresses significantly less than equivalent unencrypted data. If TDE is used to encrypt a database, backup compression will not be able to significantly compress the backup storage. Therefore, using TDE and backup compression together is not recommended.
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
Transitioning a database or filegroup into a READ ONLY state
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.
Transitioning a database or filegroup into a READ ONLY state.
Using an ALTER DATABASE command.
Starting a database or database file backup.
Starting a database or database file restore.
Creating a snapshot.
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.
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.
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.
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. 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:
For more information about the SQL 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.
The tempdb system database will be encrypted if any other database on the instance of SQL Server is encrypted by using TDE. This might have a performance effect for unencrypted databases on the same instance of SQL Server. For more information about the tempdb system database, see tempdb Database.
Replication does not automatically replicate data from a TDE-enabled database in an encrypted form. You must separately enable TDE if you want to protect the distribution and subscriber databases. 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. For more information, see Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager).
FILESTREAM data is not encrypted even when TDE is enabled.
Files related to buffer pool extension (BPE) are not encrypted when database is encrypted using TDE. You must use file system level encryption tools like Bitlocker or EFS for BPE related files.
TDE can be enabled on a database that has In-Memory OLTP objects. In-Memory OLTP log records are encrypted if TDE is enabled. Data in a MEMORY_OPTIMIZED_DATA filegroup is not encrypted if TDE is enabled.
Move a TDE Protected Database to Another SQL ServerEnable TDE Using EKMTransparent Data Encryption with Azure SQL DatabaseSQL Server EncryptionSQL Server and Database Encryption Keys (Database Engine)Security Center for SQL Server Database Engine and Azure SQL DatabaseFILESTREAM (SQL Server)
- Encryption Software Market - Global Forecast to 2022 - March 24th, 2018
- What AES Encryption Is And How It's Used To Secure File Transfers - March 24th, 2018
- Encryption vs. Cryptography - What is the Difference? - March 24th, 2018
- Energy-efficient encryption for the internet of things | MIT News - February 16th, 2018
- The Best Encryption Software - TopTenReviews - February 16th, 2018
- File-Based Encryption | Android Open Source Project - February 7th, 2018
- Beyond Encryption | Secure Enterprise email using existing ... - February 1st, 2018
- Azure Search enterprise security: Data encryption and user ... - January 26th, 2018
- Skype finally getting end-to-end encryption | Ars Technica - January 13th, 2018
- FBI chief says phone encryption is a 'major public safety issue' - January 13th, 2018
- Encryption and Export Administration Regulations (EAR) - December 27th, 2017
- Key (cryptography) - Wikipedia - December 21st, 2017
- security - Fundamental difference between Hashing and ... - December 15th, 2017
- What Is Encryption? | Surveillance Self-Defense - December 4th, 2017
- Comodo Disk Encryption Download - softpedia.com - December 1st, 2017
- Encryption - Simple English Wikipedia, the free encyclopedia - November 24th, 2017
- BitLocker Drive Encryption Overview - technet.microsoft.com - November 23rd, 2017
- The Encrypting File System - technet.microsoft.com - November 18th, 2017
- FBI cant break the encryption on Texas shooters smartphone - November 13th, 2017
- DOJ: Strong encryption that we dont have access to is ... - November 13th, 2017
- DOJ Fires Up New War With Apple Over Encryption - November 12th, 2017
- Security Awareness - Encryption | Office of Information ... - October 15th, 2017
- Data Encryption and Decryption (Windows) - October 14th, 2017
- Trumps DOJ tries to rebrand weakened encryption as responsible ... - October 11th, 2017
- How to encrypt (almost) anything | PCWorld - September 22nd, 2017
- Private Internet Access | VPN Encryption - September 21st, 2017
- Encryption Substitutes | Privacy | Encryption - September 21st, 2017
- Data Encryption: Hardware & Software Security: Online ... - September 21st, 2017
- How To Enable BitLocker Drive Encryption In Windows 10? - September 21st, 2017
- PGP Encryption Tool - iGolder - September 21st, 2017
- encryption - How to encrypt String in Java - Stack Overflow - September 21st, 2017
- Encryption Software Market, Size, Trends and Forecast 2020 - September 21st, 2017
- Encryption Definition - Tech Terms - September 20th, 2017
- Why You Should Be Encrypting Your Devices and How to Easily Do It - Gizmodo - September 6th, 2017
- Black Hats, White Hats, and Hard Hats The Need for Encryption in Mining and Resources - Australian Mining - September 6th, 2017
- How can enterprises secure encrypted traffic from cloud applications? - TechTarget - September 6th, 2017
- Encryption Explained - Arizona Daily Wildcat - September 6th, 2017
- News in brief: Call to link encryption to ID; Facebook maps everyone ... - Naked Security - September 2nd, 2017
- 'Independent' gov law reviewer wants users preemptively identified before they're 'allowed' to use encryption - The Register - September 2nd, 2017
- High-Dimensional Quantum Encryption Performed in Real-World ... - Futurism - September 2nd, 2017
- It's Time to Replace Your Encryption-Key Spreadsheet - Data Center Knowledge - September 2nd, 2017
- Legislation to limit smartphone encryption 'may be necessary,' deputy AG Rosenstein says - Washington Times - August 31st, 2017
- Cloud Encryption Market by Component, Service Model, Organization Size, Vertical And Region - Global Forecast to ... - Markets Insider - August 31st, 2017
- Cipher Suites: Ciphers, Algorithms and Negotiating Security Settings - Hashed Out by The SSL Store (registration) (blog) - August 31st, 2017
- Encryption in Office 365 - Office 365 - August 29th, 2017
- Need-to-Know Only: Use Encryption to Make Data Meaningless to ... - Security Intelligence (blog) - August 29th, 2017
- Four strategies to prevent data encryption from hijacking your network - Digital News Asia - August 29th, 2017
- Amber Rudd is wrong - real people do want end-to-end encryption - ITProPortal - August 29th, 2017
- Why encryption is for everyone - IFEX - August 29th, 2017
- 4D quantum encryption successful in first real-world test - New Atlas - New Atlas - August 29th, 2017
- For the First Time Ever, Quantum Communication is Demonstrated in Real-World City Conditions - Futurism - August 26th, 2017
- High-Dimensional Quantum Encryption Takes Place in Real-World ... - Photonics.com - August 26th, 2017
- Hedvig Bakes Encryption into Software-Defined Storage Platform - IT Business Edge (blog) - August 26th, 2017
- Hedvig storage upgrade adds flash tier, encryption options - TechTarget - August 26th, 2017
- How to use EFS encryption to encrypt individual files and folders on Windows 10 - Windows Central - August 26th, 2017
- Cloud Encryption Market Worth 2401.9 Million USD by 2022 - Markets Insider - August 23rd, 2017
- To Protect Genetic Privacy, Encrypt Your DNA - WIRED - August 23rd, 2017
- Data Encryption in OneDrive for Business and SharePoint Online - August 21st, 2017
- Researchers use encryption to keep patients' DNA private - Engadget - August 21st, 2017
- Additional proof that Lancaster County Commissioners should reconsider encrypting police transmissions - LancasterOnline - August 21st, 2017
- iPhone Secure Enclave firmware encryption key leaked - TechTarget - August 21st, 2017
- Encryption, speed push the modern mainframe into the future - TechTarget - August 21st, 2017
- Hardware encryption vs software encryption: the simple guide - Kroll Ontrack UK (press release) (blog) - August 21st, 2017
- Encryption Technology Could Protect the Privacy of Your DNA - Gizmodo - August 21st, 2017
- Beginner's guide to Windows 10 encryption - Windows Central - August 18th, 2017
- Encryption key for iPhone 5s Touch ID exposed, opens door to further research - AppleInsider (press release) (blog) - August 18th, 2017
- How security pros look at encryption backdoors - Help Net Security - August 18th, 2017
- The Laws of Mathematics and the Laws of Nations: The Encryption Debate Revisited - Lawfare (blog) - August 18th, 2017
- 72 percent of security pros say encryption backdoors won't stop terrorism - BetaNews - August 18th, 2017
- Ex-MI5 Boss Evans: Don't Undermine Encryption - Infosecurity Magazine - August 14th, 2017
- Despite end to end encryption, apps like WhatsApp, Messenger are still vulnerable to hacking: Study - Firstpost - August 13th, 2017
- What is Encryption? (with pictures) - wiseGEEK - August 12th, 2017
- Ex-MI5 chief warns against crackdown on encrypted messaging ... - The Guardian - August 12th, 2017
- Former UK security service head says weakening encryption would be too dangerous - 9to5Mac - August 12th, 2017
- News in brief: facial recognition planned for Carnival; spy chief backs encryption; ginger emoji planned - Naked Security - August 12th, 2017
- Avoid getting lost in encryption with these easy steps - We Live Security (blog) - August 12th, 2017
- Here's why IBM Z Mainframe Wants to Encrypt the World - Edgy Labs (blog) - August 10th, 2017
- Symantec Announces Plesk Will Integrate Symantec Encryption Everywhere Security Into Its Website Management ... - Business Wire (press release) - August 10th, 2017
- Australia: Shelve Proposed Law to Weaken Encryption - Human Rights Watch (press release) - August 6th, 2017
- IBM India Helps Create Breakthrough Encryption Technology That's Completely Hacker Proof - Indiatimes.com - August 6th, 2017