+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 - Transparent Data Encryption

Posted by Patrick Townsend on Feb 14, 2017 8:33:00 AM

In this second part of the series on Microsoft SQL Server encryption I want to focus on Transparent Database Encryption, or TDE. Most Microsoft customers who implement encryption in SQL Server use Transparent Data Encryption as it is the easiest to deploy. No code changes are required and enabling encryption requires just a few commands from the SQL Server console. Let’s look at some of the characteristics of TDE implementation.

Database Encryption
Encryption and key management for SQL Server TDE involves the encryption of the entire database space in SQL Server. There is no need or ability to select which tables or views are encrypted, all tables and views in a database are encrypted at rest (on disk). When data is read from disk (or any non-volatile storage) SQL Server decrypts the entire block making the data visible to the database engine. When data is inserted or updated the SQL Server database encrypts the entire block written to disk.

With TDE all of the data in your database is encrypted. This means that non-sensitive data is encrypted as well as sensitive data. There are advantages and disadvantages to this approach - you expend computing resources to encrypt data that may not be sensitive, but you also avoid mistakes in identifying sensitive data. By encrypting everything at rest you are also protected from expansion of regulatory rules about sensitive data protection.

sql-decrypts-db.png

Protection of the Symmetric Key
When you enable Transparent Data Encryption on your SQL Server database the database generates a symmetric encryption key and protects it using the EKM Provider software from your key management vendor. The EKM Provider software sends the symmetric key to the key server where it is encrypted with an asymmetric key. The encrypted database key is then stored locally on disk in the SQL Server context.

When you start a SQL Server instance the SQL Server database calls the EKM Provider software to decrypt the database symmetric key so that it can be used for encryption and decryption operations. The decrypted database key is stored in protected memory space and used by the database. The encrypted version of the database key remains on disk. In the event the system terminates abnormally, the only version of the database key is the encrypted version on disk.

Starting the SQL Server Instance
During normal operation of SQL Server there is no invocation of the EKM Provider software and therefore no communication with an external key manager. Every normal restart of the SQL Server database instance will cause the EKM Provider software to be called to unlock the database key on the key server.

It should be noted that it is the responsibility of the EKM Provider software to handle network or key server failure conditions. SQL Server itself has no visibility on the connection to an encryption key management solution. If the EKM Provider software is unable to retrieve an encryption key, the SQL Server start request will fail. We will discuss business continuity issues in more detail later in this series.

Protecting Database Logs
SQL Server logs may contain sensitive data and therefore must also be encrypted. Transparent Database Encryption addresses this by fully encrypting database logs along with the database itself. It is important to remember that encryption of the logs will only start after TDE is activated AND after you stop and restart the database log. If you neglect to restart logging sensitive data may be exposed in the SQL Server log files.

Table and Index Scanning
Certain SQL operations on indexes require that the SQL Server database have visibility on the entire index of a column. An example of a SELECT statement would be something like this:

SELECT Customer_Name, Customer_Address FROM Orders WHERE Credit_Card=’4111111111111111’;

To satisfy this SQL query the database must inspect every row in the table Orders. With TDE this means that the column Credit_Card must be decrypted in every row. Similar operations with the ORDERBY clause can cause table or index scans.

Performance Considerations
Transparent Data Encryption is very optimized for encryption and decryption tasks and will perform well for the majority of database implementations. Microsoft estimates the performance impact of TDE of 2% to 4% and we find this accurate for most of our customers. However, Microsoft SQL Server customers with very large SQL Server databases should use caution when implementing TDE. Be sure that you fully understand the impact of TDE on your application use of large tables. It is always recommended that you perform a proof-of-concept project on very large databases to fully assess the performance impact of encryption.

In the next part of this series we will look at the other option for SQL Server encryption - Cell Level Encryption, also called column level encryption.

Patrick

Encryption and key management for SQL Server

Topics: SQL Server, Transparent Data Encryption (TDE)

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 Server A 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 and key management for 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 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