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

Updated: 3/13/2020 - to reflect current status of TDE in SQL Server editions.

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 and up

Versions of SQL Server Standard with TDE:
2019 and up

Encryption & Key Management for SQL Server - Definitive Guide SQL Server TDE 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

SQL Server Column Level Encryption

Posted by Patrick Townsend on Feb 28, 2017 9:11:00 AM

Microsoft customers attempting to meet security best practices, compliance regulations, and protection of organization’s digital assets turn to encryption of sensitive data in Microsoft SQL Server databases. The easiest way to encrypt data in SQL Server is through Transparent Data Encryption (TDE) which is a supported feature in SQL Server Enterprise Edition. For a variety of reasons, TDE may not be the optimal solution. Microsoft customers using SQL Server Standard, Web, and Express Editions do not have access to the TDE feature. And even when using SQL Server Enterprise Edition, TDE may not be the best choice for very large databases.

Encryption & Key Management for SQL Server - Definitive Guide Let’s look at some approaches to column level encryption in SQL Server. The following discussion assumes that you want to meet encryption key management best practices by storing encryption keys away from the protected data, and retain full and exclusive control of your encryption keys.

Column Level Encryption (aka Cell Level Encryption) 
Starting with the release of SQL Server 2008, all Enterprise editions of the database have supported the Extensible Key Management (EKM) architecture. The EKM architecture allows for two encryption options: Transparent Data Encryption (TDE) and Column Level Encryption (CLE). Cell Level Encryption is the term Microsoft uses for column level encryption. SQL Server Enterprise edition customers automatically have access to column level encryption through the EKM architecture.

Encryption Key Management solution providers can support both TDE and Column Level Encryption through their EKM Provider software. However, not all key management providers support both - some only support TDE encryption. If your key management vendor supports Cell Level Encryption this provides a path to column level encryption in SQL Server Enterprise editions.

Application Layer Encryption
Another approach to column level encryption that works well for SQL Server Standard, Web, and Express editions is to implement encryption and decryption at the application layer. This means that your application performs encryption on a column’s content before inserting or updating the database, and performs decryption on a column’s content after reading a value from the database. Almost all modern application languages support the industry standard AES encryption algorithm. Implementing encryption in languages such as C#, Java, Perl, Python, and other programming languages is now efficient and relatively painless.

The challenge that developers face when implementing encryption at the application layer is the proper protection of encryption keys. Security best practices and compliance regulations require a high level of protection of encryption keys. This is best accomplished through the use of an encryption key management system specifically designed to create, securely store, and manage strong encryption keys. For developers, the primary challenge in a SQL Server encryption project is integrating the application with the key manager. Many vendors of key management systems make this easier by providing Software Development Kits (SDKs) and sample code to help the developer accomplish this task easily.

SQL Views and Triggers with User Defined Functions (UDFs)
Another approach to column level encryption involves the use of SQL Views and Triggers. Leveraging the use of User Defined Functions (UDFs) the database administrator and application developer can implement column level encryption by creating SQL Views over existing tables, then implementing SQL Triggers to invoke user defined functions that retrieve encryption keys and perform encryption and decryption tasks. This approach has the advantage of minimizing the amount of application programming that is required, but does require analysis of the SQL database and the use of User Defined Functions. Database administrators and application developers may be able to leverage the SDKs provided by an encryption key management solution to make this process easier.

SQL Server Always Encrypted
One promising new technology recently implemented by Microsoft is SQL Server Always Encrypted. This feature is new with SQL Server 2016 and can work with any edition of SQL Server. It is a client-side architecture which means that column data is encrypted before it is sent to the database, and decrypted after it is retrieved from the database. While there are many constraints in how you can put and get data from SQL Server, it is a promising new technology that will help some customers protect data at the column level. You can expect to see support for Always Encrypted being announced by encryption key management vendors in the near future.

SQL Server in the Azure Cloud
As Microsoft customers and ISVs move to the Azure cloud they are taking their SQL Server applications with them. And it is very common that they take full implementations of SQL Server into their Azure virtual cloud instances. When SQL Server applications run in a virtual machine in Azure they support the same options for column level encryption as described above. This includes support for Cell Level Encryption through the EKM Provider architecture as well as application layer encryption. As in traditional IT infrastructure the challenge of encryption key management follows you into the Azure cloud. Azure customers should look to their encryption key management vendors to provide guidance on support for their key management solution and SDKs in Azure. Not all key management solutions run in Azure and Azure is not a supported platform for all vendor SDKs.

Azure SQL Database
In the Azure cloud Microsoft offers the SQL Server database as a cloud service. That is, Microsoft hosts the SQL Server database in the cloud and your applications can use this service rather than a full instance of SQL Server in your cloud instance. Unfortunately, Azure SQL Database only supports Transparent Data Encryption through the EKM Provider interface and does not yet support Cell Level Encryption. It also restricts encryption key management to only the Azure Key Vault facility requiring you to share key custody with Microsoft.

Column level encryption at the application layer is fully supported for Azure SQL Database. As in the traditional IT infrastructure your C#, Java, and other applications can encrypt and decrypt sensitive data above the database level. Again, check with your key management solution provider to insure that application level SDKs are supported in the Azure cloud.

AWS Cloud and SQL Server
The Amazon Web Service (AWS) implementation of cloud workloads parallels that of Microsoft Azure. You can deploy a full instance of SQL Server in an AWS EC2 instance and use the features of SQL Server as in traditional IT infrastructure. Amazon also overs a database service called Amazon Relational Database Service, or RDS. The RDS service offers multiple relational databases including SQL Server. As with Azure there is no support for key management solutions other than the Amazon Key Management Service (KMS) requiring a shared implementation of key custody.

As you can see there are many ways to implement column level encryption in SQL Server and use good encryption key management practices. I hope this helps you on our journey to more secure data in SQL Server.

Patrick

Encryption

Topics: Encryption, SQL Server, Cell Level Encryption

Get to Know Microsoft SQL Server Data Security Options

Posted by Michelle Larson on Nov 7, 2014 8:22:00 AM

From the PASS Summit to the Worldwide User Group (SSWUG)

From Developers to Database Administrators, we have met another amazing group of people at the PASS Summit 2014. Over 5,000 members of the Professional Association for SQL Server converged on Seattle, WA and we got to talk about security with people from all over North America and from as far away as Norway, Spain, Australia, Colombia, Germany, and even Iceland.

Shayna at the PASS Summit 2014 booth

We spent most of our time talking about the importance of encrypting sensitive data, and about using an encryption key management solution to protect encryption keys away from the database. There is a huge need to meet compliance regulations, and with all the options now available (Hardware appliance, Cloud HSM, VMware virtual environment, and cloud instances in AWS or Azure) there is a solution for each scenario we encountered.

Encryption & Key Management in Microsoft Azure If you are working with SQL Server, we hope you are familiar with the SQL Server Worldwide User Group (SSWUG).  If you don’t know about them, please allow me a moment to introduce you to Stephen Wynkoop who is the founder and editor for SSWUG.org. This website is a wealth of information about everything you would want to know about SQL Server (and they are even branching out to other database systems like Oracle and IBM DB2). The emphasis at SSWUG has been on SQL Server and you will find a large number of articles, blogs, videos and other content on wide variety of topics related to it. Stephen features weekly video programs about the database and IT world, webcasts, articles, online virtual community events and virtual conferences several times a year. He is a Microsoft SQL Server MVP and the author of more than 10 books, translated into at least 7 languages. Stephen has been working with SQL Server since the very first version, with a prior experience in database platforms that included dBase and Btrieve.

SSWUG has dedicated a section of their web site to the SSWUGtv Security Edition Townsend Security Series where they present videos of Stephen and our own industry expert, Patrick Townsend, discussing security topics ranging from securing data with encryption and key management on SQL Server (not just with EKM) to protecting data in the cloud. Additionally, they post a new security segment just about every week on their homepage, so there is always something fresh. A few of the sessions include topics such as What top industries do Hackers focus on and why? and Cross-platform security: How do you have a hybrid environment and keep it secure?  

Check out this one on: PCI Compliance and Security in the Cloud - (11 minutes) 

Stephen and Patrick have a great time recording these videos, and if you haven’t seen any yet, I urge you to check them out. In addition to all the great content on the SSWUG website, SSWUG also holds virtual conferences and Summer Camps that are great online resources for developers.

You are also invited to download this latest white paper, authored by Stephen Wynkoop, on understanding options and responsibilities for managing encryption in the Microsoft Azure Cloud.

Encryption & Key Management in Microsoft Azure

 

Topics: Extensible Key Management (EKM), Encryption Key Management, White Paper, SQL Server, Cell Level Encryption, SSWUG

Encryption Options for Microsoft SQL Server

Posted by Michelle Larson on Aug 20, 2014 7:45:00 AM

Encrypting data in Microsoft SQL Server is easy to do, yet often difficult to understand because of the different encryption options offered in various versions.

SQL Server Encryption Options Podcast It used to be said that “encryption is the hardest part of data security, and key management is the hardest part of encryption”. While that may have been true a few years ago, we are constantly working to make affordable, easy-to-use, defensible solutions that meet security best practices and industry compliance regulations. Separating and managing the encryption keys from the data they protect is still one of the biggest challenges in terms of doing an encryption project right, so let’s take a look at what encryption & key management options are available for SQL Server users.

If you are running the Enterprise Edition of SQL Server, version 2008 or newer, you have access to Microsoft’s architecture for encryption called Extensible Key Management (EKM). This provider interface allows for third-party key management systems to be easily incorporated in order to separate encryption keys from the encrypted data they protect. A key management solution should provide Windows client libraries, guidance, and sample code within the solution.

The SQL Server EKM architecture supports:

Transparent Data Encryption (TDE)
With TDE, the entire database table (including the logs you are collecting) is encrypted.  It is a very easy mechanism to use for encryption and since it is transparent, no application level changes are needed, it only takes a few commands to implement. TDE protects data at rest, including backups and log files.

Cell Level Encryption
Also known as column-level encryption, this allows for you to selectively encrypt certain columns of information in your database. This option makes sense if you have large databases of information, and only access encrypted columns periodically.

If you are running older versions of SQL Server (pre-2008), or using non-enterprise editions such as standard, web, or express; you do not have access to TDE or EKM. You still have good options for protecting your data with encryption, just remember the encryption key needs to be separated from the encrypted data it protects.

When you don’t have the EKM architecture, another option for encrypting data in your SQL Server database is to perform encryption and decryption at the application layer using .NET-based encryption. All editions of SQL Server support the ability to perform encryption from within the .NET framework with very straightforward code functions.

C# and VB.NET Application Encryption
If you are developing in .NET you only need to plug in the client side application and implement a few lines of code for your encryption and decryption calls.

Column Level Encryption
Another approach would be to combine User Described Functions (UDFs) with triggers and views to help automate the encryption and decryption at the column level.

Moving SQL Server Data to the Cloud

As more companies migrate their applications and data to the cloud, there are security issues to consider before making that move. Microsoft Azure SQL Database (MASD) -which has also been called SQL Azure, SQL Server Data Services, SQL Services, Windows Azure SQL Database- is a cloud-based service from Microsoft offering database capabilities as a part of the Azure Services Platform. The service is easy to use and readily available, just know that there are some constraints and some features of EKM that are not available when using MASD.  

Most businesses migrating to the cloud will choose to run virtual machines that contain the Windows OS and a full implementation of the SQL Server database. By using a virtual machine, encryption and key management, including EKM with TDE, can be fully supported and provide the level of security you expect and compliance regulations require!  

You have many options still available for your key management solution when your data has been moved to the cloud. Our NIST validated, FIPS 140-2 compliant Alliance Key Manager solutions are available as:

    • Hardware Security Module (HSM) - a hardened appliance that you can rack up in your own data center
    • Cloud HSM - dedicated hardware device in our hosted cloud environment
    • VMware - deploy as a virtual appliance
    • Cloud - deploy in Windows Azure, Amazon Web Services, or IBM Cloud as a standard cloud instance or virtual private cloud

To learn more about encrypting data on SQL Server, managing encryption keys, and how we are helping companies protect their data with Alliance Key Manager, download the podcast on Encryption Options on SQL Server.  

SQL Server Encryption Options Podcast

Topics: Alliance Key Manager, Extensible Key Management (EKM), .NET, Encryption Key Management, SQL Server, Podcast, Cell Level Encryption, Transparent Data Encryption (TDE)

SQL Server Data Protection: Setting Up TDE or Cell Level Encryption

Posted by Michelle Larson on Jun 5, 2013 3:00:00 PM

In Microsoft SQL Server 2008/2012 Enterprise edition users can enable Extensible Key Management (EKM) and use either TDE or cell level encryption to encrypt their sensitive data and to be selective about the data they encrypt.  EKM is an architecture that allows users to incorporate a third-party* encryption key management hardware security module (HSM) in order to truly secure their data using key management best practices and meet compliance regulations.

*Townsend Security is a Microsoft Silver partner and provider of encryption key management HSMs for Microsoft SQL Server, Microsoft SharePoint, Windows, and Microsoft Azure.

SQL Server Encryption Key Management Resources

Users select from one of the two methods of SQL Server encryption available for the Microsoft SQL Server 2008/2012 Enterprise Edition and above:

1) Transparent Data Encryption (TDE): TDE encrypts the entire database and temporary files within that space with no additional programming.

On earlier versions of SQL Server deploying encryption had been a much larger and more complicated programming project.  With 2008/2012 Enterprise edition, TDE can be implemented fully without any programing at all. Once your administrator has DBA administrative rights, he or she can implement TDE through a straightforward process that requires no changes to coding, queries, or applications. TDE is a favored way to rapidly encrypt data and works well for small or medium sized databases because of its speed and ease of deployment.

2) Cell Level Encryption: Cell Level Encryption allows database administrators to select the columns they wish to encrypt in a database - a benefit for many administrators with larger databases; however, this process takes a little bit more effort to set up.

If you are leveraging EKM and using an external encryption key manager, the database administrator can encrypt data in the column (cell level) by adding a modifier on a particular fetch or update to the database. However, administrators will need to make small changes to their databases to enable their encryption key manager to do this. This is not a complicated step, however, and your encryption key management vendor should be able to help you through this. Cell level encryption works well for large databases where performance impacts must be kept to a minimum and only certain data needs to be encrypted.

Here is a very straightforward YouTube demonstration video where you can see just how easily TDE is set up.

Setting Up TDE & EKM on SQL Server 2008 / 2012 for Compliance

 

 

For a more in-depth look, we have compiled a selection of resources (webinar, white paper, podcast) that can provide additional information:

 

Download Resources

 

 

 

 

Topics: Extensible Key Management (EKM), Microsoft, Encryption Key Management, SQL Server, Cell Level Encryption, Transparent Data Encryption (TDE)

Microsoft SQL Server EKM – Should I use TDE or Cell Level Encryption?

Posted by Patrick Townsend on Sep 15, 2011 8:24:00 AM

SQL TDE or Cell Level EncryptionAs we work with Microsoft customers who are implementing encryption with Extensible Key Management in SQL Server 2008 R2, the question inevitably arises about whether to use Transparent Data Encryption (TDE) or Cell Level Encryption.

As you might guess, this comes down to tradeoffs between ease of implementation, performance, and security.

Transparent Data Encryption (TDE) is very easy to implement. It doesn’t require any changes to your existing applications, and using TDE with Alliance Key Manager, our encryption key management solution,  is very straight-forward. It typically only takes a few minutes to get up and running with our encryption key manager and TDE. Cell level encryption, on the other hand, will take at least some changes to your SQL statements or .NET application code. These changes aren’t difficult at all, but you still need to make them. For some of our customers who don’t have the source code for the application, or who don’t have IT resources available, this can be a significant barrier. The good news is that the work to set up the Alliance Key Manager key server is the same for both Cell Level Encryption as for TDE. From an ease of implementation point of view, TDE is the easy winner.

The second difference between TDE and Cell Level Encryption is performance. You might think that Cell Level Encryption would perform better because there is actually less data being encrypted, but you would be wrong! TDE is the clear winner in the performance category. Microsoft estimates that there will be a 2% to 4% performance penalty with TDE. Our own tests using the publicly available SQL Stress tool (www.sqlstress.com) shows that for most databases the performance penalty is closer to the 2% value, and in some cases less than 2%. Cell Level Encryption almost always carries a bigger performance impact. So TDE is once again the winner in the performance category.

The security tradeoffs are more complex. As Microsoft has noted, TDE does not encrypt and decrypt in memory:

“Note that neither BitLocker nor TDE encrypt data in memory. This can provide a substantial performance benefit over the encryption offered in SQL Server 2005, including the use of indexed searches (discussed later). But this also means that a system administrator with access to this memory can read the unencrypted data. All users with database permissions to access data will see unencrypted data.”

Cell Level Encryption does do encryption and decryption in memory, and this provides an incremental improvement in security.  So Cell Level Encryption provides a slightly better security strategy. If you use TDE as your encryption strategy, you will want to be sure to use a number of other techniques to lock down your environment.  You can read more about this on the Microsoft MSDN web site here.

I think for most Microsoft customers the use of TDE will fit well with their tolerance for risk and their security strategy.  Whether you choose TDE or Cell Level Encryption, you end up with your data much better protected.

You need to combine encryption and good encryption key management with other steps to properly secure your Windows and SQL Server environment.  Encryption is not a magic bullet, but without it your data is exposed to loss.

For further information, download our white paper "Encryption Key Management for Microsoft SQL Server 2008" and learn about meeting encryption and key management challenges on your Microsoft SQL Server.

Click me

Topics: Alliance Key Manager, Extensible Key Management (EKM), Microsoft, Encryption Key Management, SQL Server, Cell Level Encryption, Transparent Data Encryption (TDE)