Microsoft handed everyone a big gift with SQL Server Standard Edition 2019. The Standard edition of SQL Server did not previously support encryption. Surprise! Now it does. Prior to this new version, SQL Server Standard customers had to upgrade to the Enterprise Edition, or install a third party encryption solution. Upgrading to the Enterprise Edition was expensive for many small to midsize Microsoft customers, so bringing encryption to Standard Edition with 2019 is a big deal.

Let’s take a dive into SQL Server Standard Edition 2019 and the encryption support:

How Encryption is Implemented

Microsoft implemented encryption in Standard Edition by bringing the EKM Provider architecture from the Enterprise Edition to the Standard Edition. This means that Standard Edition users have access to the same encryption and key management capabilities that are available in the Enterprise Edition. This is great news for Microsoft customers as most are running both Standard Edition and Enterprise Edition in their IT infrastructure. You can now deploy the same encryption and key management solution across your Standard Edition and Enterprise Edition databases. If you are using Transparent Data Encryption (TDE) in the Enterprise Edition, you can now do the same thing in Standard Edition.

Earlier Versions of Standard Edition and Upgrades

The new encryption capability for Standard Edition is only in the 2019 release (version 15.x). Earlier versions of SQL Server Standard Edition will not be upgraded to support encryption. To take advantage of encryption in Standard Edition you have to upgrade to the 2019 release. You do NOT have to upgrade to the Enterprise Edition!

Encryption Key Management

How you manage encryption keys is crucial to your encryption strategy. SQL Server provides you with two key management options:

• Locally stored on SQL Server
• Deployment of a key management server through the EKM Provider interface

The only secure way to manage your encryption keys is through the use of a key management system that is registered and accessed through the EKM Provider interface. Our Alliance Key Manager for SQL Server solution implements support for the EKM Provider interface and provides you with all of the software you need to protect SQL Server encryption keys.

Compliance Regulations

Many Microsoft customers are rushing to implement encryption in order to meet the new California Consumer Privacy Act (CCPA) requirements. Your only protection from class action lawsuits in the event of a breach is through encryption of sensitive data, and proper protection of encryption keys. Storing encryption keys on the same server as the protected data will NOT provide you with CCPA protections. See California law AB 1130 for more information about encryption key management and data breaches.

Cloud Considerations

It is very common to deploy SQL Server Standard Edition in a virtual machine on a cloud platform. You can easily do this on Microsoft Azure and Amazon Web Services (AWS). When you deploy SQL Server Standard Edition 2019 in the cloud you have full access to the encryption key management using the EKM Provider interface. Be aware that many cloud service provider database services (AWS RDS, Azure SQL, etc.) do not support the EKM Provider interface and limit your ability to deploy key management. If you are concerned about cloud independence be sure to avoid these types of Database-as-a-Service offerings. 

You can run Alliance Key Manager as a dedicated key management server for your SQL Server Standard Edition database applications in Azure and AWS. You will find Alliance Key Manager in the Azure and AWS Marketplaces. You can even run Alliance Key Manager in your own data center and protect SQL Server in the cloud. You are never locked into a cloud platform.

ISV Solutions with SQL Server Standard Edition

Many software solutions are built on SQL Server Standard Edition. SQL Server is an affordable relational database and you will find it in both cloud-based SaaS solutions as well as on-premise solutions for the Enterprise. For our ISV partners we make it easy to embed our Alliance Key Manager solution into your software offering to achieve better security and compliance. If you are an end customer running an ISV application and you need encryption, talk to us about an introduction to your vendor. We will make it easy for your software vendor to upgrade and support encryption.

Alliance Key Manager for SQL Server

For more than a decade we have been helping Microsoft SQL Server customers achieve the best security for their database and applications. We now fully embrace encryption and key management for SQL Server Standard Edition. As an end user or an ISV partner, there is an affordable and easy-to-use solution waiting for you. You can learn more here.

This last November (Nov. 6-8, 2019) we had a chance to participate in the 21st annual PASS Summit in Seattle as an exhibitor. It was a great time as SQL Server professionals from around the world attended. We had an opportunity to ask them about their company's encryption and key management practices. Below are the results as well as some expert weigh-in on the findings. Enjoy!

The SQL Server Encryption Survey—2019

A special thanks to our contributors for their expertise and guidance. You all are clear-minded professionals that have a lot to offer those looking to better secure their data:

-Ed Leighton-Dick, Kingfisher Technologies
-Tim Roncevich, CyberGuard Compliance
-Justin Garren, LyntonWeb
-Sharon Kleinerman, Townsend Security
-Patrick Townsend, Townsend Security

If you are looking to protect your encryption keys for your sensitive data in SQL Server, you need a FIPS 140-2 compliant centralized key manager that:

• Never charges you additional fees for connecting a new end-point.
• Never limits the number of end-points based on the model of the KMS.
• Never limits the number of encryption keys generated or stored.
• Never forces you to pay extra fees for software patches.
• Never forces you to pay extra fees for routine software upgrades.
• Always gives you unmatched customer service.
• Always protects your keys, 24/7.

You need Alliance Key Manager for SQL Server.

This last November (Nov. 6-9, 2018) we had a chance to participate in the 20th annual PASS Summit in Seattle as an exhibitor. It was a great time as SQL Server professionals from around the world attended. We had an opportunity to ask them about their company's encryption and key management practices. Below are the results as well as some expert weigh-in on the findings. Enjoy!

A special thanks to our contributors for their expertise and guidance. You all are clear-minded professionals that have a lot to offer those looking to better secure their data:

-Sebastian Meine, Ph.D., sqlity.net
-Steve Brown, Rutter Networking Technologies
-Tim Roncevich, CyberGuard Compliance
-Sharon Kleinerman, Townsend Security
-Patrick Townsend, Townsend Security

If you are looking to protect your encryption keys for your sensitive data in SQL Server, you need a FIPS 140-2 compliant centralized key manager that:

• Never charges you additional fees for connecting a new end-point.
• Never limits the number of end-points based on the model of the KMS.
• Never limits the number of encryption keys generated or stored.
• Never forces you to pay extra fees for software patches.
• Never forces you to pay extra fees for routine software upgrades.
• Always gives you unmatched customer service.
• Always protects your keys, 24/7.

You need Alliance Key Manager for SQL Server.

Microsoft SQL Server customers ask us whether they should use Always Encrypted or Transparent Data Encryption (TDE) to protect sensitive data. It is a relevant question, especially given the concern for the GDPR, and it is relevant to many other compliance regulations and data protection needs. Let’s explore these technologies in more detail and I think the answer will emerge.

Always Encrypted

Always Encrypted is a relatively new, client-side implementation of encryption for SQL Server. That is, data is encrypted on the source system before you insert it into a SQL Server database. Implemented as a Windows driver, Always Encrypted intercepts your SQL statement before it leaves your client-side system (PC, web server, etc.), determines if there are any fields in the SQL statement that need to be encrypted, establishes or retrieves an encryption key, encrypts the field, and sends the modified SQL statement to SQL Server for processing.

One advantage of this approach is that the data is encrypted as it travels over the internal or external network, and as it resides in the database. A retrieval of the encrypted data reverses this process ensuring that the data is protected in transit.

One significant limitation of Always Encrypted is that it can only be applied to a small subset of SQL operations. Many SQL operations are complex and cannot be processed by Always Encrypted.

Transparent Data Encryption and Cell Level Encryption

SQL Server Transparent Data Encryption (TDE) and Cell Level Encryption (CLE) are server-side facilities that encrypt the entire SQL Server database at rest, or selected columns. The client-side application is completely unaware of the implementation of TDE or CLE and no software is installed on the client-side system. All of the encryption tasks are performed by the SQL Server database itself. It is easy to implement and performs very well for most SQL Server customers.

TDE and CLE are a part of the Microsoft SQL Server Extensible Key Management (EKM) strategy which has been a part of SQL Server since the 2008 edition. It is a mature technology and many organizations deploy TDE or CLE to protect their sensitive information.

Encryption Key Management

An encryption strategy is only as good as the encryption key management strategy. Creating, protecting, and using encryption keys is the hardest part of encryption, and for the sake of your overall security, it is important to get key management right.

With Transparent Data Encryption the key management strategy is well defined through the Microsoft Extensible Key Management (EKM) Provider interface. Key management systems such as our Alliance Key Manager for SQL Server provide software written to the EKM interface specification and SQL Server customers get full encryption key management integrated through the EKM Provider interface.

Always Encrypted does not provide a formal interface for key management. Instead, third party vendors must provide Always Encrypted drivers that implement an interface to a key management system. This means that the interface to the key management system is proprietary. Additionally, since the Always Encrypted implementation is a client-side deployment, each client-side application will have to have access to the key manager in order to properly protect and use encryption keys. This can be a challenge in distributed network topologies.

The bottom line: you can achieve proper key management with either approach, but expect to deal with more complexity with Always Encrypted when you have distributed clients.

When to Use Always Encrypted

Because Always Encrypted functions by modifying the SQL operation before it interfaces with the SQL Server database, and because many complex SQL operations will not work with Always Encrypted, I would only recommend using Always Encrypted when the application architecture is very simple. For example, you might want to use Always Encrypted to send data from an internal SQL Server database to a web-hosted SQL Server database and application. The data will be protected in transit and encrypted in the database. As long as your web application makes simple SQL queries to the database this approach can work well.

When to use TDE or CLE

Transparent Data Encryption and Cell Level Encryption are implemented directly in the SQL Server database itself. This means that complex SQL operations are fully supported and you will not find limitations imposed by your encryption or application strategy in the future. The SQL Server implementation of TDE and CLE also support a standardized interface for encryption key management. This means you will have choices in key management vendors and can readily find a solution.  TDE also has good performance metrics for the large majority of SQL Server customers, and is exceptionally easy to implement.

Summary

For the reasons outlined above I believe that Transparent Data Encryption or Cell Level Encryption implemented within SQL Server will almost always be the better choice for most SQL Server customers. You will not be locked into restrictions on the use of SQL operations, key management is well defined, and the encryption performance metrics are impressive.

I hope this overview of the two SQL Server encryption approaches has been helpful. Please feel free to reach out with any questions you have.

Patrick

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

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

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

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.

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 https://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