Townsend Security Data Privacy Blog

SQL Server Standard Edition & Transparent Data Encryption (TDE)

Posted by Luke Probasco on Dec 17, 2019 8:16:00 AM

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.

SQL Server Standard Edition & TDE 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.

SQL Server Standard Edition & TDE

Topics: SQL Server encryption