What to Consider when using Transparent Data Encryption (TDE)

Transparent Data Encryption (TDE) is a security feature which protects data at rest.  The data on disk is encrypted, this includes data files, log files, and backup files.  The transparent aspect is there is no special decryption methods to be used when you have the correct permissions to access the data.  TDE works on the data files in the event they fall into “the wrong hands.”

Considerations

1. Certificate / Key Storage
   This is one of the most critical things to consider.  Who/Where the backup of the keys and their passwords.  Without these components, it is much harder, if not impossible, to recover the database in the event failure or server move.
2. Moving Protected database to another server
   Moving the protected database to another server requires the certificates to be installed on the new server prior to restoration.  It is not possible to restore a protected database on a server where its encryption key does not exist.
3. Removing TDE
   Ensure you have a good backup of the keys and certificates as TDE does not attempt to encrypt portions of the LOG files that have already been written to disk and vise versa.   A restoration where there are still encrypted values in the log file can cause failure if you prematurely remove the certificate.  Additionally, if you need to restore an archived backup, you want to ensure you can perform this task.

4. Encryption Strength (algorithm used)
   SQL Server uses Advanced Encryption Standard (AES) or Triple DES encryption algorithms.

5. Performance
   Since SQL 2016, SQL Server use AES-NI hardware acceleration which limits extra I/O and CPU therefore having minimal impact on database read/write performance.  During processing of queries that require use of TempDB, performance for databases that are not already encrypted with TDE may be slightly reduced as the data is encrypted on the way into and out of TEMPDB (our experience has shown this is marginal). It is important to note that, during the initial encryption process, database performance will be impacted – however SQL Server mostly does this mostly asynchronous in the background – so even the impact of the initial encryption should be fairly minimal.
6. Encrypting new databases
   New databases are not automatically encrypted.   Monitoring should be performed to catch for any new databases and their encryption status.   The process to encrypt a new database can be automated.
7. Importing / Exporting databases
   When you export the database the contents of the export are not encrypted (bacpac, saving to CSV files, etc).  You will have to remember to re-encrypt the database once it has been imported to its new home.
8. Backup Sizes
   Prior to SQL Server 2016, compression of backups was not possible.  Little to no space was saved in compressing the backups.   SQL Server 2016 you must specify the MAXTRANSFERSIZE that is greater than 64K to allow SQL Server to decrypt the backup, apply compression then re-encrypt the backup file as it is written to disk. Excluding the MAXTRANSFERSIZE or having a size less than 64K performs the same compression method as earlier versions of SQL Server.

What does it encrypt

1. Offers encryption at the file level

1. Database Backups[jmw]  (.trn, .bak, etc)
2. SQL Server Data files[jmw]  (.mdf, .ndf)
3. SQL Server Log files[jmw]  (.ldf)

2. TempDB

What is not encrypted

1. “Over the wire”
   Transfer of data when directly connected to the database via Management Studio or another application. The data can still be protected in-flight by using encrypted connections.

2. Filestream Data
   Filestream uses the OpenSQLFilesteam API, which bypasses the normal SQL I/O process and therefore is not encrypted.
3. Files related to buffer pool extension (BPE)
   Buffer pool extension provides integration of nonvolatile RAM (a solid-state drive) extension to the database buffer pool. 

4. In-Memory OLTP
   While you can encrypt a database where In-Memory OLTP objects exist, the files in the MEMORY_OPTIMIZED_DATA filegroup are left unencrypted.

Links:

1. https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?redirectedfrom=MSDN&view=sql-server-ver15
2. https://docs.microsoft.com/en-us/archive/blogs/sqlsecurity/feature-spotlight-transparent-data-encryption-tde
3. https://www.sqlshack.com/how-to-configure-transparent-data-encryption-tde-in-sql-server/
4. https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/enable-encrypted-connections-to-the-database-engine?view=sql-server-ver15
5. https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/enable-tde-on-sql-server-using-ekm?view=sql-server-ver15
6. https://askinglot.com/goto/266056A11