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.

Featured

  • Microsoft Nabs IoT Platform Provider Express Logic

    As part of its plan to invest $5 billion in IoT technologies, Microsoft this week acquired Express Logic, which provides real-time operating systems for industrial embedded and IoT devices.

  • Dealing with Broken Dependencies in SCVMM

    Brien shows you how to resolve some broken, template-related dependencies in Microsoft's System Center Virtual Machine Manager.

  • AzCopy Preview Adds AWS S3 Data Transfer Improvements

    Microsoft announced this week that it has improved the preview version of its AzCopy tool to better handle Amazon Web Services (AWS) S3 data.

  • Microsoft Adding Google G Suite Migration in Exchange Admin Center

    Microsoft's Exchange Admin Center will be getting the ability to move Google G Suite calendar, contacts and e-mail data over to the Office 365 service "in the coming weeks."

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.