+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

Microsoft SQL Server Automatic Encryption - Cell Level Encryption

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

In this third part of the series on Microsoft SQL Server encryption we look at Cell Level Encryption, or CLE, which is Microsoft terminology for Column Level Encryption. With CLE the manner and timing of SQL Server’s call to the EKM Provider software is quite different than for Transparent Data Encryption. It is important to understand these differences in order to know when to use CLE or TDE. Let’s look at some aspects of the CLE implementation:

Encrypted Columns
Encryption-Key-Management-SQL-Server Cell Level Encryption is implemented at the column level in a SQL Server table. Only the column you specify for encryption is protected with strong encryption. You can specify more than one column for CLE in your tables, but care should be taken to avoid performance impacts of multiple column encryption (see below).

With Cell Level Encryption you may be able to minimize some of the encryption performance impacts on your SQL Server database. Because the EKM Provider is only called when the column must be encrypted or decrypted, you can reduce the encryption overhead with careful implementation of your database application code. If a SQL query does not reference an encrypted column, the EKM Provider will not be invoked to perform decryption. As an example, if you place the column Credit_Card under CLE encryption control, this query will not invoke the EKM Provider for decryption because the credit card number is not returned in the query result:

SELECT Customer_Number, Customer_Name, Customer_Address FROM Orders ORDERBY Customer_Name;

You can see that judicious use of SQL queries may reduce the need to encrypt and decrypt column data.

SQL Application Changes
Unlike Transparent Data Encryption you must make a change to the SQL statement in order to implement Cell Level Encryption. The SQL Server functions “encryptbykey” and “decryptbykey” are used on SQL statements. Here is an example of a SQL query that encrypts a CLE-encrypted column:

select encryptbykey(key_guid('my_key'), 'Hello World');

Implementing CLE encryption in your SQL Server database requires modifications to your applications, but may be well worth the additional work.

Encryption and Key Retrieval
The EKM Provider software is called for each column value to perform encryption and decryption. This means a larger number of calls to the EKM Provider compared to Transparent Data Encryption. Because the number of calls to the EKM Provider may be quite large it is important that the encryption and key management functions of the EKM Provider are highly optimized for performance (see the next section).

The EKM Provider software from your key management vendor is responsible for performing encryption of the data. From a compliance point of view it is important to understand the encryption algorithm used to protect data. Be sure that the EKM Provider software uses a standard like the Advanced Encryption Standard (AES) or other industry recognized standard for encryption. It is common to use 128-bit or 256-bit AES for protecting data at rest. Avoid EKM Providers which implement non-standard encryption algorithms.

Encryption Key Caching
When deploying CLE it is important that the EKM Provider software optimize both encryption and key management. The number of calls to the EKM Provider software can be quite high. Good EKM Providers will securely cache the symmetric key in the SQL Server context rather than retrieve a key on each call. The retrieval of an encryption key from a key server takes precious time and multiple calls to retrieve a key can have severe performance impacts. Secure key caching is important for CLE performance. The use of the Microsoft Windows Data Protection Application Program Interface (DPAPI) is commonly used to protect cached keys.

Performance Considerations
When properly implemented Cell Level Encryption can reduce the performance impact of encryption on your SQL Server database. For very large tables with a small number of columns under encryption control, the performance savings can be substantial. This is especially true if the column is used less frequently in your applications.

CLE Vendor Note
Note that each vendor of EKM Provider software implements encryption and key management differently. Some EKM Providers only implement Transparent Data Encryption (TDE). If you suspect you will need Cell Level Encryption be sure that your key management support includes this capability.

In the next part of this series we will look at encryption key management in SQL Server.

Patrick

Encryption and Key Management for Microsoft SQL Server

Topics: SQL Server, Cell Level Encryption, SQL Server 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 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

Posts by Topic

see all