Share via

Feature Spotlight: Transparent Data Encryption (TDE)

  • Article

As more and more businesses go digital and towards the cloud, security is more important than ever. We’re not all security experts, but with the responsibility we assume as businesses to be entrusted with protecting our customer’s data, it’s essential that we understand the basics of how we secure data. Security only works when you identify the following:

  • What you’d like to protect
  • Who you’d like to protect it from
  • What vulnerabilities remain

Data encryption in the SQL Platform is no different. This post helps to clarify the attack surface area that Transparent Data Encryption (TDE) protects, and answers some frequently asked questions.

TDE is SQL’s form of encryption at rest. It encrypts data files at rest for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, and APS. The term “data at rest” refers to the data, log files, and backups stored in persistent storage. Accordingly, TDE protects against malicious parties who try to restore stolen database files, such as the data, logs, backups, snapshots, and database copies. Encryption-at-rest is intended as a layer of protection for customers who run their own hardware on-premises, or host their data in cloud data centers. Many corporate and compliance regulations require encryption to protect data at rest.

With TDE enabled on a database, all offline database files are encrypted with AES 256 encryption. When your database is open, any data in memory is decrypted. Therefore, your application doesn’t require any changes to work with TDE. Similarly, a database administrator (DBA) working with data in Management Studio (SSMS) can work with the data freely in plaintext. The DBA would also be able to run DBCC commands on the database. However, the DBA would not be able to search through the data files or log files (using grep for example), since the data is encrypted there. As with all forms of security, TDE requires its users to make careful trade-offs between usability, data availability, and security.

This brings us to a few important points about the capabilities of TDE. Most importantly, TDE is not meant to be used as the sole protection in a database system. As mentioned earlier, it’s important to identify the remaining attack surface even if TDE is enabled. A few to highlight are: SQL injection attacks, cross-site scripting that hijacks an administrator’s permissions, code flaws that expose vulnerabilities, attacks by a high-privileged user with sysadmin privileges, attacks by an OS or machine administrator (“box admin”), or attacks by anyone who has physical access to the machine or has access to the OS image and the data files, just to name a few scenarios where attackers could obtain access through elevation of privileges. These are vulnerabilities that TDE does not cover, and you’ll need to protect against these by other means.

TDE is only as strong as the other precautions you take around its implementation. Here are some best practices to keep in mind:

  1. Store your backups away from the location of the physical server
    • Avoid having a single point of failure. For example, if a fire were to break out near your physical server and you could not access the server, you wouldn’t want the backups to also be compromised in the fire.
  2. Protect your encryption certificates and keys from loss and theft
    • TDE’s architecture involves a hierarchy of encryption levels that ultimately protect the TDE certificate or TDE asymmetric key (if you use extensible key management, or EKM), which encrypts the database encryption key, which in turn encrypts your database’s data, log files, and any backups.
    • Be sure to back up your TDE certificates or TDE asymmetric keys, and secure and monitor their use. Always store certificates and keys in a physically safe location, separate from where your database backup files are (see #1 above).
    • Using EKM adds additional security, because the encryption key is stored in a Hardware Security Module device (HSM) separate from the database system. SQL Server never has access to the encryption key itself.
  3. Minimize permissions
    • By default, the access control lists (ACLs) on the data files should limit access to only the SQL Server or administrators on the machine. Be careful if these default permissions are going to be modified, and refer to your IT department’s security guidelines as well.

Azure SQL Database’s security offering works in layers to provide security mechanisms at different levels, which together create a more complete security solution. TDE is only one essential piece of this overall security solution, so learn how other aspects like access control and proactive monitoring help fortify your data security with this overview, Securing your SQL Database. If you require broader encryption protection, including protection against DBAs, the maximum protection that SQL can provide through encryption is Always Encrypted. If you have questions that aren’t covered in the following FAQs, please reach out to the SQL security forums for help and feedback.

Frequently Asked Questions

  • With TDE, is the data stored in my data, log, and backup files protected from a disk theft?
    • Yes, but only if the files are stored on a separate disk separate from the OS image, and if the attacker does not steal the disk containing the OS image as well. TDE only aims to protect the database files, including data, logs, backups, snapshots, and database copies, but not the entire disk. If the OS image and data files are located on the same disk, an attacker can restore and compromise the data. A recommended best practice is to store the OS image and database files on separate disks for additional security.
  • Can a backup encrypted by TDE be compressed?
    • Depends on the version of SQL Server. For SQL Server 2016, if you'd like a backup to have both compression and TDE, you must explicitly specify a MAXTRANSFERSIZE that is greater than 64K in the backup command (the next possible size is 128K). SQL Server will then decrypt the backup first, compress the backup, then encrypt the backup before writing to media. If MAXTRANSFERSIZE is not specified to be greater than 64K, compress will occur but in the same behavior as previous versions of SQL Server.
    • For previous versions of SQL Server, the backup remains encrypted and then is compressed. Because the original encryption adds randomness to the data, little to no space is saved from the compression, wasting CPU cycles on compression/decompression. Such a backup set is still considered encrypted at rest due to the original encryption, but backup compression will be almost ineffective. Therefore, for previous versions of SQL Server, using TDE and backup compression together is not recommended.
  • Is TDE supported with export/import of a database?
    • Simply put, the moment you export your database, the data is in plaintext. When exporting a TDE protected database using the Export Database function in the Azure SQL Database Portal or the SQL Server Import and Export Wizard, the content of the database is not encrypted. The content is stored in .bacpac files which are not encrypted. Be sure to protect the .bacpac files appropriately and enable TDE once import of the new database is completed. The one exception is when exporting to and from Azure SQL Database – TDE will be enabled in the new database, but the .bacpac file itself is still not encrypted.
  • How does key rotation work in TDE?
    • There are two encryptions involved with TDE: a TDE certificate or TDE asymmetric key, and a symmetric database encryption key (DEK). Rotating the former is relatively fast, while rotating the latter will be an operation on the order of your database size, since all the data pages must be re-encrypted. DEK rotation is resumable, so in case of any interruptions, the re-encryption scan will resume on SQL startup. Learn more through a deep-dive post on DEK rotation.
  • What is the performance impact of TDE?
    • SQL Server 2016 and Azure SQL Database use the latest Intel AES-NI hardware acceleration instructions to significantly improve the performance of TDE. Once initial encryption has completed, there is no extra I/O, and CPU impact and latency per database read is minimal.
    • If you’re using SQL Server 2014 or previous versions, an alternative encryption-at-rest option is BitLocker with Intel AES-NI instructions.
  • I’ve heard that using a Hardware Security Module (HSM) makes encryption more secure. How does that work?
    • Using TDE with extensible key management (EKM) and an HSM separates your database files from your encryption key, which reduces your vulnerability risk if either your database system or your HSM were compromised. During encryption and decryption processes, the encryption key never leaves the HSM, so SQL Server can only send requests to the HSM but never has access to the key itself. Depending on the type of HSM, the HSM may have additional requirements in order to be used, such as the need to be accessed over same network or bus.
  • Is there anything else I should be aware of before turning TDE?
    • The initial encryption and final decryption processes with TDE are computationally expensive, as we need to read and encrypt every database page. Although it is run in a background thread, it can have a performance impact – you should test the impact for your workload before deploying broadly. Some workloads which already have high CPU utilization can see higher impact. If you’re using Standard and below tiers, consider enabling TDE in batches to minimize performance impact.

Learn More

Comments

  • Anonymous
    October 07, 2016
    Great points!! Thanks for sharing!