Enterprise Transparent Data Encryption In SQL Server

One of the advanced security features of SQL Server Enterprise edition since SQL 2008 is Transparent Data Encryption (TDE).

Useful for many large enterprises with security constraints or regulatory requirements, TDE provides encryption of the database files and backups. This protects them from being restored to another authorized server, if for example, they resided on stolen backup tapes.

TDE secures database files to a server by means of a certificate, and encrypts the files continuously during typical operation. Users won't notice and applications do not need to be modified in any way – that's the "Transparent" keyword here. Encryption of personally-identifying information in tables, such as social security numbers, passwords and credit cards, is not a job for TDE – again, users access to an in-progress database isn't affected. Encrypting personally-identifying data fields in your database is a job for native SQL Encryption, but not for TDE.

The files remain encrypted if detached from the server and are impossible to attach or restore to another server without the presence of that certificate. Securing this certificate and its private key – and backing them up separately from the database files it protects - becomes a crucial step for server administrators. The expiration of the certificate isn't taken into account– you won't ever lose your data because of an expired certificate, only if you lose the certificate/key itself.

TDE does not increase the size of the database, however, the biggest drawback of TDE is the countermanding of another great SQL Server feature – backup compression. Note that TDE does NOT conflict with SQL Server Enterprise Data Compression. Because TDE is set up at the database level, not all databases on a SQL Server need be protected by TDE and suffer the loss of backup compression.

On final caveat – because the background encryption is transparent to the applications and users, TDE does not encrypt data in server memory or over the network, those are security concerns for other products or features.

As a database architect, team lead and senior consultant for Sparkhound,, I'd be happy to discuss your SQL Server Security surface area including the positives and negatives of Transparent Data Encryption and SQL Server Enterprise edition. We also have a checklist-driven, onsite SQL Health Check offering that directly interfaces with the client's IT staff – a very valuable and educational exercise for any sized environment.

Information and material in our blog posts are provided "as is" with no warranties either expressed or implied. Each post is an individual expression of our Sparkies. Should you identify any such content that is harmful, malicious, sensitive or unnecessary, please contact  marketing@sparkhound.com