+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

Fixing the TDE Key Management Problem in Microsoft SQL Server

Posted by Patrick Townsend on Jan 10, 2017 7:31:56 AM

Many Microsoft SQL Server users have taken the first step to protect sensitive data such as Personally Identifiable Information (PII), Protected Health Information (PHI), Primary Account numbers (PAN) and Non-Public Information (NPI) by encrypting their databases with Transparent Data Encryption (TDE). It is extremely easy to implement TDE encryption as it does not require program changes.

Encryption and key management for SQL ServerA common cause of audit failures might not be so obvious and that is the failure to properly protect the SQL Server key encryption key once you activate encryption in SQL Server. With Transparent Data Encryption you have the choice of storing the service master key within the SQL Server context itself, or protecting the master key with a key management system using the SQL Server Extensible Key Management (EKM) interface. Why is it important to do this?

It turns out that it is easy for cyber criminals to recover the SQL Server master key when it is stored within SQL Server itself. (Examples: https://blog.netspi.com/decrypting-mssql-credential-passwords/ and http://simonmcauliffe.com/technology/tde/#hardware)

Simon McAuliffe provides the clearest explanation I’ve seen on the insecurity of locally stored TDE keys in SQL Server. I don’t agree with him on the question of using a key manager to improve security. Given that there is no perfect security, I believe that you can get significant security advantages through a properly implemented key management interface.

If your TDE keys are stored locally, don’t panic. It turns out to be very easy to migrate to a key management solution. Assuming you’ve installed our SQL Server EKM Provider called Key Connection on your SQL Server instance, here are the steps to migrate your Service Master Key to key management protection using our Alliance Key Manager solution. You don’t even need to bring down SQL server to do this (from the Alliance Key Manager Key Connection manual):

Protecting an existing TDE key with Alliance Key Manager

First create a new asymmetric key pair within the AKM Administrative Console using the “Create EKM Key” and the “Enable Key for EKM” commands.

Then return to SQL Server and call the following command to create the asymmetric key alias for the new KEK that you created on the AKM server:

use master;

create asymmetric key my_new_kek from provider KeyConnection with provider_key_name = ’NEW_TDE_KEK’, creation_disposition = open_existing;

In this example, NEW_TDE_KEK is the name of the new key on AKM, and my_new_kek is the key alias.

Then use the ALTER DATABASE statement to re-encrypt the DEK with the new KEK alias assigned in the previous statement:

ALTER DATABASE ENCRYPTION KEY

ENCRYPTION BY SERVER

   {  ASYMMETRIC KEY my_new_kek}

Note that you do not have to take the database offline to perform this action.

Of course, there are other steps that you should take to secure your environment, but I wanted to demonstrate how easy it is to make the change.

The SQL Server DBA and the network administrator will have lots of other considerations in relation to SQL Server encryption. This includes support for clustering and high availability, automatic failover to secondary key servers, adequate support for separation of duties (SOD) and compliance, and the security of the credentials needed to validate SQL Server to the key manager. All of these concerns need to be addressed in a key management deployment.

For SQL Server users who deploy within a VMware or cloud infrastructure (AWS, Azure), Alliance Key Manager can run natively in your environment, too. It does not require a hardware security module (HSM) to achieve good key management with SQL Server. You have lots of choices in how you deploy your key management solution.

It turns out not to be difficult at all to address your SQL Server encryption key insecurities!

Patrick

Encryption Key Management for Microsoft SQL Server

Topics: SQL Server, Transparent Data Encryption (TDE)

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 PodcastIt 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 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

As always, we welcome your comments and question.

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)

 

 

Subscribe to Email Updates

Recent Posts

Posts by Topic

see all