+1.800.357.1019

+1.800.357.1019

Feel free to call us toll free at +1.800.357.1019.

If you are in the area you can reach us at +1.360.359.4400.

Standard support
6:30am - 4:00pm PST, Monday - Friday, Free

Premium support
If you own Townsend Security 24x7 support and
have a production down issue outside normal
business hours, please call +1.800.349.0711
and the on-call person will be notified.

International customers, please dial +1.757.278.1926.

Townsend Security Data Privacy Blog

SQL Server TDE vs Cell-Level Encryption: A Brief Comparison

Posted by Ken Mafli on May 31, 2017 2:21:18 PM

In 2008, Microsoft introduced Transparent Data Encryption (TDE) to its Enterprise and Datacenter Editions of SQL Server. Billed as a way to seamlessly deploy SQL Server encryption, users now had the choice of full database-level encryption, instead of just the previous choices of cell-level encryption (CLE), Encrypting File System (EFS), or Bitlocker. With its rapid deployment, ease-of-use, and enhanced security TDE has been a staple for every version of SQL Server Enterprise Edition (and Developer Edition) ever since.

Versions of SQL Server Enterprise with TDE:
2008, 2008 R2, 2012, 2014, 2016

Encryption & Key Management for SQL Server - Definitive GuideTDE has become a favorite for bulk encryption in meeting regulatory compliance (like PCI DSS) or internal corporate data security initiatives. But while TDE has it’s advantages, it is not a cure-all. Sung Hsueh did a great job explaining the advantages and disadvantages of TDE as compared to CLE. The following is a curated look at that whitepaper. Let’s take a quick look:

What is Transparent Data Encryption?

TDE fundamentally is full database-level encryption. It functions at the Input/Output (I/O) level. Any data written into the database is automatically encrypted. Backups are also automatically encrypted. Data in use is decrypted by TDE as they are read by a user or application and stored, in clear text, in memory. Since the data-in-flight is decrypted; TLS or SSH (or now, “Always Encrypted”) should be enabled to protect the data while in motion.

What is Cell-Level Encryption?

Introduced in 2005, CLE is implemented as a series of built-ins. It is a manual process “that requires a re-architecture of the application to call the encryption and decryption functions.” Hsueh also notes that “the traditional limitations of encryption are inherent in this method as none of the automatic query optimization techniques [of TDE] can be used.” 

CLE vs. TDE

The advantages of CLE:
  • Since it is column level encryption, it encrypts only the sensitive information in a table.
  • With CLE, the data is still encrypted even when it is loaded into memory.
    CLE allows for “explicit key management” giving you greater control over the keys and who has access to them.
  • CLE is highly configurable, giving you a high degree of customization (especially when your applications require it).
  • Queries may be faster with CLE if the encrypted column(s) is not referenced in the query. TDE will always decrypt the entire row in the table. CLE will decrypt the column value only IF it is a part of the data that is returned. So in some cases CLE implementations provide much better overall performance.

The disadvantages of CLE:

  • One of the main disadvantages of CLE is the high degree of fully manual application changes needed to use it. TDE, on the other hand, can be very simple to deploy with no changes to the database, tables or columns required.
  • CLE can also have high performance penalties if search queries cannot be optimized to avoid encrypted data. “As a rough comparison, performance for a very basic query (that selects and decrypts a single encrypted column) when using cell-level encryption tends to be around 20% worse [than TDE].”

The whitepaper goes on to note that with CLE performance impacts “are several magnitudes worse when attempting to encrypt an entire database. One sample application with 10,000 rows was four times worse with one column encrypted, and 20 times worse with nine columns encrypted.” TDE, on the other hand, only had a 3-5% average performance impact compared to a non-encrypted database.

Final Thoughts

A case could be made for using CLE in conjunction with TDE as a defense-in-depth strategy. By selectively encrypting columns with CLE, encrypting the full database with TDE, and then managing the separate keys with a centralized key manager; it would ensure that crucial data was protected, even while loaded into memory.

But, in general, TDE and CLE are used for different purposes. If you are looking to encrypt a small amount of data, if your application “has custom design requirements,” or if performance is not a much of a concern, CLE may have advantages over TDE. But, if performance is a concern or you would like to avoid manually implementing encryption (normally a time-consuming process) then TDE is the way to go.

For more information on both types of encryption and how they relate to Extensible Key Management, visit our Definitive Guide to SQL Server Encryption & Key Management.

Encryption

Topics: SQL Server, Cell Level Encryption, Transparent Data Encryption (TDE), SQL Server encryption

4 Ways to Encrypt Data in Microsoft SQL Server

Posted by Patrick Townsend on May 6, 2013 4:29:00 PM

Almost every organization has at least one application built on Microsoft’s SQL Server database. Whether you build an application in-house using Microsoft’s development tools or you deploy a software package from a software vendor, chances are that your organizations has one or more SQL Server databases to help you manage information.

The Challenge: Protect Data with SQL Server’s Encryption

Encryption-Key-Management-SQL-Server Today it is almost impossible to run a business without handling sensitive information and storing storing data such as customer names, credit card numbers, bank account numbers, passwords, email addresses, or other personally identifiable information (PII) or private health information (PHI) in your SQL Server database. If your organization must meet data security regulations such as PCI-DSS, HIPAA/HITECH, or GLBA/FFIEC, you probably already know that this data must be encrypted in order to protect your customers and prevent data loss in the event of a data breach.

What you may not know is that in order to truly protect your data, you must manage your encryption keys in adherence to key management best practices such as dual control and separation of duties using an external encryption key manager (key managers are available in VMware, Cloud, as a traditional harware security moule or HSM). Your company will only be able to avoid data breach notification if you are using these best practices.

The good news is that SQL Server 2008-2016 comes equipped with transparent data encryption (TDE) and extensible key management (EKM) to make encryption and key management using a third-party key manager easier than ever. Older versions of SQL Server can also be easily encrypted using different tactics, and you can manage those encryption keys just as easily with an encryption key manager as well.

Encrypting Data in SQL Server Depends on Your Version

If you’re currently looking into encrypting your SQL Server database or deploying a key management system, you may be concerned about how to protect your data depending on the version, code, and language used to build your database. To help ease your worries, here are 4 ways to encrypt your SQL Server database and protect your encryption keys:

  1. Since SQL Server 2008 Microsoft has supported automatic encryption with TDE and cell level encryption for Enterprise Edition users and above. Without any programming you can encrypt the SQL Server database or an individual column, and store the keys on an encryption key manager (commonly available as an HSM and in VMware or Cloud).
  2. If you have an older version of SQL Server, or you have SQL Server Standard Edition or Web Edition, you don’t have access to TDE. But you can still automate encryption: Through the strategic use of SQL Views and Triggers, you can automate encryption of sensitive data on your SQL Server without extensive program modifications, and still use a secure key managemer to protect the encryption keys.
  3. Your developers might have written custom application code to implement your SQL Server database. But SQL Server encryption and key management is still within your reach. A good key management vendor should supply you with software libraries that easily add into your applications and implement SQL Server encryption.
  4. You might have a SQL Server database, but not be using Microsoft programming languages. Perhaps your applications are written in Java, Perl, or PHP. Again, it is simple to deploy software libraries that encrypt the SQL Server data and which store the encryption keys on an external centralized key manager.

SQL Server encryption and good key management is not difficult to achieve. Although key management has a reputation for being difficult and costly, today key management for SQL Server is cost-effective, easy, has little to no performance impact, will get your company in compliance, and will keep your organization out of the headlines by helping to prevent a data breach.  Townsend Security's Alliance Key Manager is FIPS 140-2 compliant and in use by over 3,000 customers worldwide.

To learn more about key management for SQL Server, download the White Paper, “Encryption Key Management for Microsoft SQL Server 2008/2016.”

Encryption and Key Management for Microsoft SQL Server

 

Topics: Extensible Key Management (EKM), Microsoft, Encryption Key Management, White Paper, SQL Server, SQL Server encryption

 

 

Subscribe to Email Updates

Recent Posts

Posts by Topic

see all