SQL Transparent Data Encryption (TDE)

SQL Transparent Data Encryption (TDE) Feature is a native feature of SQL Enterprise edition.

Transparent Data Encryption is designed to protect data by encrypting the physical files of the database, rather than the data itself. Its main purpose is to prevent unauthorized access to the data by restoring the files to another server. With Transparent Data Encryption in place, this requires the original encryption certificate and master key. It was introduced in the Enterprise edition of SQL Server 2008.

Transparent Data Encryption (TDE) encrypts SQL Server, Azure SQL Database, and 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 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.

A valid user can access the decrypted contents of the database files without any distinguishable actions, and without even being aware that the underlying data files are encrypted. However, a would-be data thief, who has obtained access to the data files through a stolen backup file, will find he or she is unable to access the data it contains. Overall, this is a straightforward, low-impact feature that has great security benefits; the only caveat being that it requires SQL Server 2008 Enterprise Edition.

Transparent Data Encryption (TDE) was introduced in SQL Server 2008, as a feature of the Enterprise Edition of that product. The Developer Edition of SQL Server 2008 also offers TDE, but its license limits its use to development and testing only.

As noted above, TDE’s specific purpose is to protect data at rest by encrypting the physical files of the database, rather than the data. These physical files include the database file (.mdf), the transaction log file (.ldf) and the backup files (.bak).

While TDE does not offer the granularity of protection that cell-level encryption offers, it does provide a level of protection that cannot be achieved through cell-level encryption. This feature provides a means to render unreadable the plain text that would otherwise be disclosed in the database backup files.

Thanks to the encryption key hierarchy that is external to the database, additional protection is offered by restricting restoration of the encrypted database to the instance from which the backup files were created.

Encryption Check

Shows which DBs are encrypted with TDE and which are not (encryption_state = 3 is the teller):

SELECT
    db.name,
    db.is_encrypted,
    dm.encryption_state,
    dm.percent_complete,
    dm.key_algorithm,
    dm.key_length
FROM
    sys.databases db
    LEFT OUTER JOIN sys.dm_database_encryption_keys dm
        ON db.database_id = dm.database_id;
GO

Backup/Restore with TDE

You will get the following message if you try to restore a TDE enabled backup file.

Msg 33111, Level 16, State 3, Line 2 Cannot find server certifiate with thumbprint…

Benefits and Disadvantages of TDE

Benefits

• Implementation of TDE does not require any schema modifications.
• Since the physical data files and not the data itself are encrypted, the primary keys and indexes on the data are unaffected, and so optimal query execution can be maintained.
• The performance impact on the database is minimal. In their whitepaper titled “Database Encryption in SQL Server 2008 Enterprise Edition“, Microsoft estimates the performance degradation for TDE to be 3-5%, while cell-level encryption is estimated to be 20-28%. Of course, the impact well may vary, depending upon your specific environment, and volume of data.
• The decryption process is invisible to the end user.

Disadvantages

• Use of TDE renders negligible any benefits to be gained from backup compression, as the backup files will be only minimally compressed. It is not recommended to use these two features together on the same database.
• TDE does not provide the same granular control, specific to a user or database role, as is offered by cell-level encryption.
• TDE is available only with SQL Server 2008, Enterprise Edition and so will probably not be available to all installations within your environment.

TDE and tempdb

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.

Sources:

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver15

Transparent Data Encryption