Improve Your Database Security with Transparent Data Encryption

If you don’t have encryption, but you want another layer of security for your database, Transparent Data Encryption (TDE) is a relatively simple way to encrypt your data at rest. It will protect you against any attack where someone gains access to your media such as a backup of your database. Without the right encryption tools, the thief won’t be able to make use of the information stored in the database.

How Does TDE Work?

TDE protects data at rest by encrypting the physical files of the database rather than the data itself. SQL Server 2008 Enterprise Edition was the first to use TDE. It now encrypts the Enterprise Editions of SQL Server, Azure SQL databases, and Azure SQL Data Warehouse data files.

TDE offers additional locks beyond the Service Master Key (SMK). To enable TDE, you create a Database Master Key (DMK), Certificates, and Database Encryption keys (DEK). You then have a hierarchy that provides additional protection.

• The Windows Data Protection API protects the SMK in the master database
• The SMK protects the DMK in the master database
• The DMK protects the certificate’s private key in the master database
• The certificate protects the DEK in the user database
• The DEK protects the dta in the user database

The SMK, DMK, and the Master Certificate are all stored outside of the database to prevent access by someone who has an unauthorized copy of the database.

What are the Benefits of TDE?

The biggest benefit to using TDE is that you don’t need to change anything in your applications or code. Your users won’t need to do anything different to accommodate the use of TDE. When used in the right situations, TDE provides a number of benefits to your organization.

• It’s easy to implement compared to other types of encryption
• It doesn’t require Schema modification that would require developers to accommodate the encryption
• It works with all Enterprise Edition features, including high availability features such as AlwaysOn, Mirroring, and Log shipping
• It doesn’t interfere with query execution
• It’s invisible to the end user

Is TDE Right for You?

TDE isn’t right in every situation, so there are a number of things to consider before implementing it.

Data traveling to and from applications isn’t encrypted. If your goal is to protect data at rest, this won’t be a problem. If you want to encrypt data in transit, you’ll need to look for other solutions.

All data in the database is encrypted. TDE will work well if encrypting all the data in the database is your goal. Don’t use TDE If you only want to encrypt part of the database or only sensitive data. Look for granular control such as cell or column level encryption.

The TempDB is encrypted. As a result, data is un-encrypted and encrypted on the fly. There is a slight performance reduction, but most users don’t find it significant enough to avoid using TDE. In fact, for the extra security TDE provides, the three to five percent degradation on the CPU is exceptionally small.

The master database isn’t encrypted. If you need encryption for your master database, TDE isn’t your best solution.

TDE is only available with Enterprise Level Editions. If you’re running Standard Edition SQL Server, for example, you would need to upgrade and TDE would be included. If that isn’t possible, you can’t use TDE.

When you use TDE, the most important thing to remember is that you won’t be able to restore a database without the keys and certificates. On the other hand, anyone with access to that information can decrypt the database. It’s critical that you save keys and certificates, know how to access them, and control the number of people who have access to the keys.

The best practice for securing keys and certificates would be to put a password on them and only give that password to select people within administration and your DBA staff.

While the overall implementation of TDE isn’t difficult, it does take time to determine how to set it up and how to get it working. If you’d have a problem fitting that type of an effort into your schedule, keep in mind that Datavail can help. Get in touch to discuss putting together a project. You don’t need a huge investment to implement TDE, but it does a lot of good.

Check out all of our SQL Server resources here.