Month: November 2015

Transparent Data Encryption (TDE) is an encryption method that encrypts database containers (usually a file) of Oracle or SQL Server databases. Containers are “data at rest” (ie, the data is not moving from one place to another). Because database backup programs copy blocks from the (encrypted) database container, TDE causes backups to be encrypted. This is important because backups are frequently moved off-site. If a backup tape/disk were to be lost, any attempt to restore the database would require the keys/certificates that were used to encrypt the data. Without them, the database would not be usable. It’d look like random bits & bites. The engine probably would not even mount the container.

Another feature of TDE is that if a disk becomes unusable, it can simply be discarded. There is no need to destroy the disk if it only contained database containers as any data that might later be recovered would be encrypted.

TDE is “transparent” because it does not change the way the database is used by applications. A SELECT will return data just as it would if TDE were not implemented. Everything works the same. This means that, with TDE, confidential information can be queried (ie, by a DBA), but could not be obtained if a backup tape were restored to another system (assuming it did not have copies of the proper keys & certificates).

When evaluating how to protect confidential information, one needs to carefully consider the goals of the encryption. For example, if you want to protect against this situation: “If an intruder circumvents other security controls and gains access to encrypted data, without the proper cryptography keys, the data is unreadable and unusable to that person” [PCI 3.1, Summary of Section 3], maybe End to End Public Key cryptography should be used (in addition to, or) rather than TDE. Another option might be columnar encryption. Judging by the chatter on the internet, a PCI audit has been blessed because the auditors are simply unaware of what TDE really is. I’ll talk about columnar and End to End Public Key encryption in the near future.

Is TDE worth the Effort? I assert not. Here’s why. If the disk fails, simply destroy it by dropping it on a concrete floor until you hear little pieces of what sounds like broken glass rattling around inside. If you are concerned about backups, simply encrypt the backups as they are taken. Adding a script to do that is much simpler than the hassles of setting up, maintaining, and using TDE.

Setting up TDE involves creating certificates & keys, and understanding how each works.

Maintaining TDE involves things like regularly backing up those certificates & keys and storing them separately from your normal backups (in PCI environments, implementing proper key management).

So, what is involved with USING TDE? If you’ve implemented TDE, the database just works, correct? Yes, that’s what’s cool about TDE… But, suppose you want to perform DBA level tasks like coping a (sanitized) database from production (with TDE) to development?

The process should be: restore the database to production server with a new name, sanitize the new database, back it up, restore it to development. But, with TDE on the source database, the restore will not work unless you have the keys available. I flat-out could not get it to work. Here’s a second opinion. See the bottom of the page, section “Known Issue with TDE”: second opinion.

I ended up having to move keys to the destination system in order to restore the non-encrypted database backup. It’s simply not worth the hassle for zero benefit (other than to say that your database is encrypted, and maybe snow your PCI auditor into believing your data is secure against commonly known attacks, which it’s not. I’ll blog about that next month).

On the other hand, if TDE were not involved, but the backup is explicitly encrypted, one could specify the key when restoring the copy to production, and skip the encryption when backing up the copy… without the possibility of leaving TDE keys behind on development as no keys would be required to restore the unencrypted backup.

Because the problems that are solved with TDE are so easily solved by other means, and because of the problems CAUSED by TDE, I discourage its use.