5 Flavors of Microsoft SQL Data Encryption

With the constant drumbeat of security vulnerabilities and breaches in the news, protecting data is, or should be, on everybody’s mind. While we know that a firewall is essential to keeping the bad guys out, we’ve also learned that most data breaches happen from behind our firewalls. Good security defenses, then, are multi-tiered. Assuming that our firewalls and permission schemes will eventually be overcome or otherwise thwarted, an essential line of defense is strong data encryption.

For static files, such as a Microsoft Word or Excel document, drive or folder encryption is a viable option. However, SQL Server uses constantly active files with complex dependencies and interactions with other systems and applications. Therefore, the SQL service itself must implement an encryption scheme or schemes. Following is a quick comparison of the 5 kinds of encryption available within Microsoft SQL Server.

SSL Transport Encryption

SSL (Secure Socket Layer) is a common encryption protocol used to encrypt data between network endpoints. It is most commonly associated with web addresses starting with “https” where the “s” indicates secure (encrypted) traffic. Using network sniffers or so-called man-in-the-middle (e.g., proxy) attacks, unencrypted traffic can be easily read. Encrypted traffic is magnitudes more difficult to read (measured in how long it takes to unencrypt without the right keys). Fortunately, web servers are not the only servers that can encrypt data in transit using SSL. SQL Server can also be set up to allow SSL encryption of its data as it travels over the network. This capability is available in all modern versions, across all editions of SQL Server.

Transparent Data Encryption (TDE)

While SSL encrypts data in motion on the network, TDE encrypts data at rest. This means that should someone manage to take a copy of your data files, they would not be able to decipher their contents without the proper keys. When a SQL Server instance mounts an encrypted data file, it uses the keys to decrypt the data as it is extracted from the file during use, and then to encrypt it again before it is written to the drive. That means that once it leaves the drive, it is unencrypted, so both memory and network vulnerabilities will have access to unencrypted data. The good news is that therefore client software and server application using a TDE database do not need to be changed in any way to use it. In other words, TDE can be implemented without developer or 3^rd party application vendor involvement in most cases. However, it is the one encryption technology that requires Enterprise edition of SQL Server.

Backup Encryption

Like TDE, this is encryption of data at the whole file level but applies to your SQL backups instead of the active data files. It is this author’s opinion that this is the first and most essential encryption technique as backups are and should be, often copied to at least 2 locations, including offsite. Should a backup file fall into the wrong hands, encryption will nearly ensure data security assuming keys are well guarded. Backup encryption is available for both Standard and Enterprise editions.

Column/Cell-Level Encryption

As its name implies, cell-level encryption is applied on specific columns, such as credit cards, salaries, and passwords. Unlike TDE, when the data is selected in a query, such as a report, the data remains encrypted by default, even in memory. However, unencrypting is only a matter of using a function (decryptbykey) in the query. That is to say, cell-level encryption obfuscates the data by default, but does not secure it within the context of a valid user connection. Also, since it requires the use of a function to decrypt the data, implementing it with existing applications and queries requires changes to the code. However, unlike TDE, it is available for all editions of SQL Server.

Always Encrypted

Available only since SQL Server 2016, and only to client applications using similarly modern data libraries (e.g., .NET 4.6) Always Encrypted is a new implementation of cell-level encryption that provides holistic coverage. And, though it’s only available on the latest versions of SQL, 2016 Service Pack 1 enabled it for all editions. With Always Encrypted, data is encrypted at the client transparently by the data connection layer without any code changes required. Data then remains encrypted over the network, in memory, and on the drive. It is, however, implemented at the column level, so schema changes are required, which typically will require developer and/or 3^rd party application vendor participation. Also, since the data cannot be encrypted by SQL, certain indexing, and other functions will not work. For example, it is not possible to sort, look for string fragments, or do the math on data that cannot be unencrypted. One final benefit, however, is that this is the only encryption scheme that can also secure data even from the highest privileged SQL users, including those with sysadmin rights.

It is evident that Microsoft takes data encryption seriously and has implemented a number of ways to take advantage of it. However, as with many security capabilities, there is no single encryption panacea to protect your data. Licensing, version, application dependencies, administrative processes, and also performance will dictate which encryption technique or techniques to deploy in your scenario. But given the efficacy of encryption for protecting data, and the fact that many of these capabilities are included in most SQL editions, consider deploying at least one as a part of your security defense strategy.

Want to learn more? Contact Thrive today!