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.

Like Microsoft SQL Server Enterprise Edition users, SQL Server 2019 Standard Edition users can now easily meet compliance regulations (PCI DSS, GDPR, CCPA, etc.) and protect private data like customer PII and intellectual property without modifying existing applications or the database.  By using the database’s Transparent Data Encryption (TDE) capability, coupled with Extensible Key Management (EKM), and an encryption key manager, organizations can protect their private data at a lower cost.

I recently sat down with Patrick Townsend, Founder and CEO of Townsend Security to talk about TDE in Microsoft SQL Server 2019 Standard Edition and what it means for smaller businesses who don’t have Enterprise Edition, as well as deploying encryption key management in the cloud.

Patrick,  It was great to see Microsoft bring Transparent Data Encryption to the standard edition of SQL Server 2019.

We were pleased to see Microsoft announced that SQL Server 2019 Standard Edition would support Transparent Data Encryption (TDE) and Extensible Key Management (EKM).  By doing this, Microsoft was able to bring encryption and proper key management to a huge user base and not require them to make application changes, which can be a barrier for some companies.  TDE and EKM were originally introduced back in SQL Server 2008 Enterprise, so the technology itself has been around a while. There are a lot of users of the Standard Edition of SQL Server and by lowering the technical and financial bar to protecting private data, companies of all sizes can easily protect their private information - including customer information and IP.

As you mentioned, TDE and EKM are considered a pretty mature technology at this point.  You have had an EKM provider for Enterprise Edition for over 10 years, right?

Yes! Since the initial release of TDE and EKM in Microsoft SQL Server 2008, we have been proud to offer an affordable, industry leading solution - and now extend that to SQL Server Standard Edition users.  As far as platforms go, we started a decade ago with a hardware security module (HSM), though most of our customers are now running VMware environments or are in the cloud (AWS, Azure, IBM Cloud). Fortunately, because all of the platforms that we offer our key manager on run exactly the same software, we are able to maintain our FIPS 140-2 compliance.  We even have customers running hybrid deployments with key managers in the cloud and on-premises.

It is really easy to get started as well.  Deploying an enterprise encryption key management solution  used to take several months and lots of resources but can now be done relatively quickly.  Essentially, here is what you need to do:

• Set up Alliance Key Manager (just takes a few minutes)
• Install our EKM provider software on your instance of SQL Server
• Configure SQL Server
• Turn on TDE

That’s it!  It is a very straightforward deployment.

By using standards based encryption, Microsoft is positioning their customers for success.  But they still leave key management up to the customer.

Yes, and standards are just as important with key management as they are with encryption.  People often think that the encryption algorithm is the secret part of securing data and don’t realize the importance of key management.  Only YOU should have access to your encryption keys - and this goes for administration of the key manager as well. Enterprises need to look for a FIPS 140-2 validation and avoid multi-tenant solutions offered by CSPs.  On more than one occasion I have heard customers say that prior to using Alliance Key Manager they were storing their keys in an Excel spreadsheet, on a USB key, or even burnt in their application code. Not very secure locations, to say the least, and the keys were very likely not “cryptographically strong.” Encryption keys based on passwords will never meet minimum standards for strong encryption keys. Keys should be generated using a cryptographically secure random bit generator (CS-RBG) validated to international standards.

On the topic of Cloud Service Provider key management, while key management solutions offered by CSPs can provide some convenience, they leave an organization’s encryption keys accessible to third-party administrators - increasing the risk to their security posture.  Finally, bringing it back to SQL Server, remember, it is poor security practice to store encryption keys locally to the SQL Server database. It takes a hacker just a few seconds to recover those keys!

Let’s dive into this a little deeper, because I think there is a little confusion in the market. I attend Pass every year, and when people think about key management, they sometimes talk about using open source software or even storing their encryption keys in something like “Last Pass”.

People need to understand that an encryption key manager is more than just a secure key store.  A key manager like our Alliance Key Manager creates and manages encryption keys through their entire lifecycle.

I’d like to elaborate a little more on CSP-offered key managers like AWS KMS or Azure Key Vault, since I think many people are familiar with these offerings.  If you look at Information Supplement: PCI SSC Cloud Computing Guidelines, you’ll find them state: 

“Because compromise of a Provider could result in unauthorized access to multiple data stores, it is recommended that cryptographic keys used to encrypt/decrypt sensitive data be stored and managed independently from the cloud service where the data is located.”

This is a pretty common sense warning from the PCI Security Standards Council.  Consolidating services under one shared umbrella dramatically increases an organization’s risk.

True Key Management Systems (KMS) need to go where your data goes - on-premise, cloud, multi-cloud, or VMware.  Alliance Key Manager is a full enterprise key management system. The AWS KMS, for example, is a key storage facility and can’t leave the AWS cloud, which provides CSP lockin.

And when thinking about the security principles of Confidentiality and Availability, it just doesn’t make sense to use something other than a full-fledged key manager.

You’re right.  Again, with Confidentiality, you don’t want your CSP to have administrative access to your keys.  In terms of Availability what about high availability? What if you need to run applications that deal with private data in multiple clouds?  By using an enterprise level key manager, enterprises can rely on a centralized key manager to protect their data regardless of where it resides or will in the future.

Also, for those who are using older versions of SQL Server Standard Edition, you can use Netlib's Encryptionizer along with our Key Connection for Encryptionizer to transparently encrypt private data.

To hear this conversation in its entirety, download our podcast SQL Server Standard Edition & TDE to hear Patrick Townsend further discuss encrypting data in Microsoft SQL Server Standard 2019, encryption key management in the cloud, and the importance of data security standards.

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.

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.