Townsend Security Data Privacy Blog

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.

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.

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.

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.

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.  

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.

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:

As 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.