All About Encryption

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

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!

About the Author

Joshua Jones is co-author of A Developer's Guide to Data Modeling for SQL Server: Covering SQL Server 2005 and 2008 (Addison-Wesley Professional, 2008) and is a principal with Denver-based Consortio Services LLC.


  • Windows Admin Center vs. Hyper-V Manager: What's Better for Managing VMs?

    Microsoft's preferred interface for Windows Server is Windows Admin Center, but can it really replace Hyper-V Manager for managing virtual machines? Brien compares the two management tools.

  • Microsoft Offers More Help on Windows Server 2008 Upgrades

    Microsoft this week published additional help resources for organizations stuck on Windows Server 2008, which fell out of support on Jan. 14.

  • Microsoft Ups Its Carbon Reduction Goals

    Microsoft on Thursday announced a corporatewide carbon reduction effort that aims to make the company "carbon negative" by 2030.

  • How To Dynamically Lock Down an Unattended Windows 10 PC

    One of the biggest security risks in any organization happens when a user walks away from their PC without logging out. Microsoft has the solution (and it's not a password-protected screensaver).

comments powered by Disqus

Office 365 Watch

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.