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)
- Review: SecureDrive BT, the encrypted external SSD you can unlock with Face ID - 9to5Mac - January 19th, 2020
- EncryptOnClick is a freeware tool that can encrypt files and folders - Ghacks Technology News - January 19th, 2020
- Trump and Comey Are United Against Encrypted Communications - Reason - January 19th, 2020
- Police Scotland to roll out encryption bypass technology - Glasgow Live - January 19th, 2020
- Encryption battle reignited as US govt at loggerheads with Apple - Times Now - January 19th, 2020
- Hardware Encryption Market Set To Register A CAGR Growth Of XX% Over The Forecast Period 2017 2025 - Fusion Science Academy - January 19th, 2020
- Malware Obfuscation, Encoding and Encryption - Security Boulevard - January 14th, 2020
- Microsoft CEO says encryption backdoors are a terrible idea - The Verge - January 14th, 2020
- Debate over access to encryption isn't going away - Washington Examiner - January 14th, 2020
- Over two dozen encryption experts call on India to rethink changes to its intermediary liability rules - TechCrunch - January 14th, 2020
- Encryption Software Market Booming by Size, Trends and Top Growing Companies- IBM Corporation, Sophos, Ciphercloud, Pkware, Mcafee - BulletintheNews - January 14th, 2020
- Hardware-based Full Disk Encryption Market Analysis With Key Players, Applications, Trends And Forecasts To 2025 - Instanews247 - January 14th, 2020
- Mobile Encryption Market Insights and Technology 2020, Forecasts to 2026 - Broadcast Offer - January 14th, 2020
- Garda needs new technology for online child abuse investigations - The Irish Times - January 14th, 2020
- IoT Security Solution for Encryption Market Research, Recent Trends and Growth F - News by aeresearch - January 14th, 2020
- Apple made a rare appearance at tech's biggest conference and defended encryption on the iPhone - Business Insider - January 8th, 2020
- Encryption Software Market to cross USD 20 Bn by 2026: Global Market Insights, Inc. - Yahoo Finance - January 8th, 2020
- Data Encryption Software Market Size by Top Leading Key Players, Growth Opportunities, Incremental Revenue , Trends, Outlook and Forecasts to 2025 -... - January 8th, 2020
- WidePoint Partners with KoolSpan to Offer End-to-End Encryption for Phone Calls and Text Messages - GlobeNewswire - January 8th, 2020
- Encryption Software Market 2020 Size, Growth By Top Companies, Forecast Analysis To 2027 - Citi Blog News - January 8th, 2020
- How to cope with a FileVault recovery key disappearing while you write it down - Macworld - January 8th, 2020
- Doing these 10 things on WhatsApp may land you in jail - Gadgets Now - January 8th, 2020
- Holistic encryption is one of the keys to California Consumer Privacy Act risk management - Continuity Central - January 6th, 2020
- Encryption Software Market 2020 Size, Share Metrics, Growth Trends and Forecast to 2026 - Food & Beverage Herald - January 6th, 2020
- New Informative Report of IoT Security Solution for Encryption Top Key Players are Cisco Systems, Intel Corporation, IBM Corporation, Symantec... - January 6th, 2020
- Maastricht University gets almost all of its Windows systems encrypted by ransomware - 2-spyware.com - January 6th, 2020
- Homomorphic Encryption Market Analysis, Industry Size, Application Analysis, Regional Outlook, Competitive Strategies And Forecasts (2020 2027) -... - January 6th, 2020
- The Week in Ransomware - January 3rd 2020 - Busy Holiday Season - BleepingComputer - January 6th, 2020
- Keep Your Business Secure From Online Threats - BBN Times - January 6th, 2020
- State Department Adds ITAR Definitions in Interim Final Rule for Activities that Are Not Exports, Reexports, Retransfers or Temporary Imports -... - January 6th, 2020
- Global Hardware-based Full Disk Encryption (FDE) Market Executive Summary and Analysis by Top Players 2020-2027: Seagate Technology PLC, Western... - January 6th, 2020
- Clop Ransomware Now Kills Windows 10 Apps and 3rd Party Tools - BleepingComputer - January 6th, 2020
- Scientists in Scotland help develop worlds first encryption system that is unbreakable by hackers - The Independent - December 21st, 2019
- Apple Bows Down To Google As 2019 Most Trustworthy Recognition - International Business Times - December 21st, 2019
- Facebook , Apple being threatened by US senators over data encryption - Gizmo Posts 24 - December 21st, 2019
- How To Secure Microsoft Windows 10 In Eight Easy Steps - Forbes - December 21st, 2019
- Examine Mobile Encryption Market expected to obtain $2,917.9 million by 2022 - WhaTech - WhaTech - December 21st, 2019
- Facebook's end-to-end encryption will enhance user privacy but its not good news for law enforcement - Firstpost - December 21st, 2019
- Future of Encryption Software Market Reviewed in a New Research Study 2019-2025 - Daily News Reports 24 - December 21st, 2019
- Fortanix expert on how European companies are taking back control of their data in the cloud - Intelligent CIO ME - December 21st, 2019
- What's that? Encryption's OK now? UK politicos Brexit from Whatsapp to Signal - The Register - December 20th, 2019
- NYPD radio encryption most likely wont happen in 2020 but will soon - amNY - December 20th, 2019
- If You Think Encryption Back Doors Won't Be Abused, You May Be a Member of Congress - Reason - December 20th, 2019
- The decline of passwords, the rise of encryption and deepfakes cybersecurity predictions for 2020 - BetaNews - December 20th, 2019
- Facebook's Push for End-to-End Encryption Is Good News for User Privacy, as Well as Terrorists and Paedophiles - Nextgov - December 20th, 2019
- Internet of crap (encryption): IoT gear is generating easy-to-crack keys - The Register - December 20th, 2019
- What Is Snatch Ransomware and How to Remove It - Guiding Tech - December 20th, 2019
- Hardware-based Full Disk Encryption Market Executive Summary, Introduction, Sizing, Analysis and Forecast To 2025 - Market Research Sheets - December 20th, 2019
- NYPD Eyeing Encrypted Radios to Protect Criminal Investigations - Officer - December 20th, 2019
- Volunteer firefighters, EMTs worry they won't have NYPD radio access to help public - amNY - December 20th, 2019
- What We Learned About the Technology That Times Journalists Use - The New York Times - December 20th, 2019
- The Senate Judiciary Committee Wants Everyone to Know It's Concerned About Encryption - EFF - December 14th, 2019
- The Defense Department Says It Needs the Encryption the FBI Wants to Break - Free - December 14th, 2019
- Congress wants to regulate encryption for big tech - The Burn-In - December 14th, 2019
- Facebook says it won't break end-to-end encryption - TechRadar - December 14th, 2019
- Encryption spat sees backdoor back-and-forth between tech firms, Congress - TelecomTV - December 14th, 2019
- Michael Hayden Ran The NSA And CIA: Now Warns That Encryption Backdoors Will Harm American Security & Tech Leadership - Techdirt - December 14th, 2019
- Large, diverse coalition of civil society groups tell the US, UK and Australian governments not to ban working encryption - Boing Boing - December 14th, 2019
- U.S. Attorney Justin Herdman of Ohio says agents need access encrypted devices, apps for the sake of public s - cleveland.com - December 14th, 2019
- Google makes it safer to text on Android phones, but end-to-end encryption is still MIA - PCWorld - December 14th, 2019
- Priti Patel bids to create end-to-end encryption apps' back door - The National - December 14th, 2019
- Encryption can't put tech giants beyond the reach of the law, Minister says - The Age - December 14th, 2019
- Chrome 79 includes anti-phishing and hacked password protection - Naked Security - December 14th, 2019
- Hardware Encryption Technology Market : Analysis and In-depth study on market Size Trends, Emerging Growth Factors and Forecasts to 2027 - Downey... - December 14th, 2019
- Encryption back on the congressional agenda - Politico - December 9th, 2019
- Police radios blocked from the public in southeast Denver metro area - The Denver Post - December 9th, 2019
- Encryption Software Market Innovations, And Top Companies - Forecast To 2029| Microsoft, Sophos Ltd., Check Point Software Technologies Ltd. -... - December 9th, 2019
- Did You Hear That? Securing Communications in 2019 | Insight for the Connected Enterprise - No Jitter - December 9th, 2019
- 'Government broke their promise': Labor seeks to amend encryption legislation - Sydney Morning Herald - December 9th, 2019
- Global Hardware-based Full Disk Encryption Market 2019 Innovation and Technological Developments, Industry Analysis & Outlook 2023 - Weekly News... - December 9th, 2019
- Privacy vs public safety - the pros and cons of encryption - World Economic Forum - December 8th, 2019
- 80% of all Android apps encrypt traffic by default - We Live Security - December 8th, 2019
- Keybase moves to stop onslaught of spammers on encrypted message platform - Ars Technica - December 8th, 2019
- Labor says it will fix encryption laws it voted for last year - ZDNet - December 8th, 2019
- Nick Clegg to be summoned to Parliament to give evidence on Facebook encryption - Sunriseread - December 8th, 2019
- This startup just solves the data privacy problem by making it possible to search encrypted data in the cloud - TechStartups.com - December 8th, 2019
- Encryption Software Market to Discern Magnified Growth During 2017-2027 - Weekly Spy - December 8th, 2019
- Millions of Private Text Messages Have Been Exposed: Here's How to Encrypt Messages on iPhone and Android - Tech Times - December 8th, 2019
- Biometric Data Encryption Device Market : Analysis and In-depth study on market Size Trends, Emerging Growth Factors and Forecasts to 2018 to 2028 -... - December 8th, 2019
- Certbot Leaves Beta with the Release of 1.0 - EFF - December 8th, 2019