All About Encryption

In this ongoing series on SQL security, we discern the differences between in-flight encryption and data at rest encryption.

• By Joshua Jones
• 06/07/2010

You may have heard the word encryption used in conjunction with SQL Server quite a number of times. As many times as most people hear it, they can often still be confused about what types of encryption, and what exactly is actually encrypted, inside of SQL Server. Let's talk about the types of encryption available and what scenarios they would be used in this week.

Essentially, there are two areas where encryption is applied: in-flight transactions, and data at rest.

In-Flight Data Encryption -- In-flight encryption applies encryption to any data movement, i.e. connections from applications, and data being manipulated.

If you've used the internet in the last 15 years, you know that transactions are quite often secured via the Secure Sockets Layer, or SSL. Using SSL requires a certificate to be issued on both the source and destination computers, and SSL encryption must be supported by the host operating systems. SQL Server supports SSL connections for client connections to encrypt data in flight between computers. This can help that intercepted data is secured, assuming whoever is doing the interception doesn't have the certificate. The obvious benefit of doing this is security; however, performance can be greatly impacted when using SSL.

SSL connections to SQL Server will require at least one extra network trip, and the encryption/decryption process on both ends takes extra time. So, if you have a scenario where remote client connections are susceptible, and security trumps performance, SSL may be a good way to.

Data At Rest Encryption -- Data at rest encryption applies to data being stored on disk, whether in the database data files or in the backup files for a given database. When it comes to encrypting data on disk, there's transparent data encryption, or TDE. This encryption method encrypts ALL data in a database, including both data and log files.

TDE works via a database encryption key (DEK) which is stored in the database boot record, and a certificate for the DEK which is stored in the master database on the server. This means that the master database which holds the correct certificate is required to use the DEK in the database itself to read and write data in the database.

When using TDE, administrators need to be careful to immediately backup and store the certificate and DEK for the database, because if the database is restored or attached to another server, the data will be unavailable until the certificate is installed and the DEK recreated.

TDE also introduces some complexity in database maintenance, because many operations are not allowed while the encryption scan is happening. Additionally, if the database being encrypted is a replicated database, it is import to know that the data will NOT be encrypted as it is transferred between the distributor and subscriber databases. TDE can  be enabled in these databases to make sure the entire communication channel is protected. In terms of log shipping and database mirroring, all database will automatically be encrypted.

One note: TDE greatly REDUCES SQL Server's ability to compress data, meaning that compressing backups will derive very little benefit. It's often recommended to disable backup compression when backing up a TDE enabled database.

Hopefully, between firewalls, access permissions, and encryption, you'll be able to build a totally secure SQL Server environment.

Next time, we'll start looking at some scenarios that combine these security methods to protect data. Until then, have fun!