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.

Microsoft introduced Always Encrypted in SQL Server 2016 as a way to protect data in SQL Server databases. Always Encrypted runs on a client side system and encrypts data before it is stored in the SQL Server database. This provided some new protection for sensitive data stored in SQL Server - at least the server administrator and the DBA would not have access to the sensitive data. Or, that was the idea.

Always Encrypted suffered from severe limitations and did not achieve wide acceptance and deployment. The types of SQL queries and operations you could perform were minimal. You could not do basic SQL query operations that most businesses rely on. So Always Encrypted has not been deployed much.

Microsoft is attempting to address these limitations in a facility called Secure Enclaves. Secure Enclaves is a special operating environment that runs on SQL Server itself. You can think of it as a special virtual environment that can’t be accessed by a server administrator or DBA, but which can decrypt sensitive data from the database and perform those more complex SQL operations. SQL Server runs in one environment, and Secure Enclaves is a separate, more secure environment on the same server that runs those SQL requests against decrypted data. 

Processing data in a Secure Enclave means that the encrypted data has to be decrypted. How does that happen if the encryption key is on the client-side system and not on the SQL Server system? There are now special drivers on the client-side system that will send the encryption key to the Secure Enclave when needed. 

So, is this more secure? That is a hard question to answer. Here are some things to think about:

• Protected execution environments, like Secure Enclaves, have their own security concerns. The operating system hypervisors that manage these secure environments bring their own attack surface. Adding new attack surfaces brings more risk.
• The client-side implementation of Always Encrypted also adds an attack surface. Again, the more places that are potentially open to an attacker the more risk you bear.
• In many cases, client-side systems are not as well protected as core SQL Server systems. Think of a user PC in your organization, or think of a remote office server. User and remote systems are notoriously hard to protect well. 
• Encryption key management is the linchpin of your encryption strategy. Unfortunately, Always Encrypted has limited options for deploying industry standard key management. Always Encrypted supports storing encryption keys in the Windows Certificate Store and in Azure Key Vault. It does not support the industry standard Key Management Interoperability Protocol (KMIP). This means you are very limited in terms of your key management options. 
• Using the Windows Certificate Store to protect your Always Encrypted encryption keys may not be compatible with the California Consumer Privacy Act (CCPA) -and using Azure Key Vault may violate PCI Data Security Standards (PCI DSS) cloud guidance. 
• A core aspect of your encryption key management strategy is monitoring who has access to encryption key credentials, and reporting on access failures. When the encryption is performed on the client system by Always Encrypted, you may have limited ability to monitor activity and detect unauthorized access attempts. That further complicates your security posture.

My thoughts:

One of the primary goals of Always Encrypted and Secure Enclaves is to protect sensitive data by implementing Separation of Duties. That is, ensuring that system administrators and DBAs do not have access to both protected data and the encryption keys. This is a core security principle when protecting data-at-rest. 

You can achieve Separation of Duties by using a proper key management solution like our Alliance Key Manager. By assigning key management duties to a security professional, and isolating key management responsibilities from DBAs, you achieve the heart of the Separation of Duties goal. I believe that when properly implemented, a SQL Server Transparent Data Encryption (TDE) implementation with good key management gives you a very strong security posture without the risks involved with Always Secure and Secure Enclaves. Of course, you have to do a lot of other things to secure your Windows server and SQL Server. Proper encryption and key management is only one part of your overall security strategy.

Microsoft is doing a lot of things right in the area of data protection. The recent implementation of encryption for SQL Server Standard Edition 2019 is exactly the right thing to do. It puts encryption and key management in the hands of a lot of SQL Server users who have not had access to this technology. I hope that Microsoft will eventually embrace open standards for encryption key management in Azure and in other Microsoft products. This will be a great step forward for Microsoft customers.

Patrick

Interest in Microsoft SQL Server database encryption is booming! What is driving the sudden rush to encrypt sensitive data? Certainly the new California Consumer Privacy Act (CCPA) is a part of this. Just a few days after the CCPA became law the first class action lawsuit was filed. No business wants to deal with a class action lawsuit, and encryption is the only safe harbor from class action lawsuits.

We have to give some credit to Microsoft, too. In the past, database encryption was only available in the Enterprise editions of SQL Server. Upgrading from SQL Server Standard, Express and Web editions was an expensive proposition. Then (... SURPRISE! ...) in November 2019 Microsoft announced that SQL Server Standard Edition 2019 would also support encryption in the same way that the Enterprise edition does. It was a great Holiday gift to the many thousands of SQL Server users and ISVs who need to meet compliance regulations.

And the continued publicity about data breaches, ransomware, state actors, and new zero-day exploits continued to elevate everyone’s awareness of the threats to their sensitive data. So encryption is suddenly hot.

Let’s take a look at using SQL Server encryption in Amazon Web Services (AWS). 

Encryption Key Management

If you’ve been following this blog series you know how important key management is to an encryption strategy. That is even more true in the AWS environment. While Amazon makes available a proprietary key service, it can’t be used with databases like SQL Server that implement vendor or open standards. And AWS KMS is a shared encryption key service - both you and Amazon have access to your keys. So, before you start your SQL Server encryption project, be sure to get your key management strategy right.

Local Master Key Storage

When you implement encryption with SQL Server you have a choice about where you store the master keys. You can store them next to the SQL Server database (bad), or you can store the keys in an external key management system using the SQL Server Extensible Key Management (EKM) interface (better). Using an external key management system through the EKM interface is the only way to protect your data under CCPA, and it’s a best security practice. That is what we will focus on for the rest of this blog. 

SQL Server and Extensible Key Management (EKM) Provider

Starting in SQL Server 2008 Enterprise, Microsoft implemented database encryption and added the EKM Provider interface for encryption key management. This interface pre-dated the modern KMIP interface, but provides a similar architecture for integrating encryption key management for SQL Server. The EKM Provider architecture has been a part of SQL Server Enterprise since that release more than a decade ago. Our customers have performed many upgrades to SQL Server and the EKM interface has been stable and reliable. 

The EKM Provider architecture is essentially a set of rules for implementing a plug-in module for SQL Server to integrate with a key manager such as our Alliance Key Manager for SQL Server. You code a Windows DLL to the specification, register it to SQL Server, run an activation command in the SQL Server console, and you have encrypted your SQL Server database! It is fast, easy and straightforward.

Key Management in the Cloud

Now you need a key manager that implements the EKM Provider interface, and you need a place to deploy that key manager. Our customers usually deploy Alliance Key Manager directly from the EC2 console and the AWS Marketplace when they want a dedicated key manager that runs within AWS. Alliance Key Manager runs in an EC2 instance, is dedicated to you (not shared with Amazon or us), and provides the EKM Provider software at no additional charge. You just: 

• Launch Alliance Key Manager
• Answer a few configuration questions
• Download the certificates that SQL Server needs
• Configure the EKM Provider
• And activate it

In a short period of time you can fully protect SQL Server with strong encryption and proper key management.

Key Management Outside of the Cloud

Some Microsoft SQL Server users want full control of their encryption keys outside of the AWS cloud. This is incredibly easy! You can deploy Alliance Key Manager as a VMware instance in your on-premise data center, then configure the SQL Server EKM Provider to connect to the on-premise key server. The EKM Provider interface is exactly the same in all Alliance Key Manager platforms. You will need to set network permissions in AWS, and allow a connection to the on-premise key server, but that’s it. You can get key management outside the AWS cloud very easily. Additionally, if you initially deploy in the cloud and want to migrate to your own data center, that is also fast and easy.

Key Management Across AWS Regions

Many AWS customers deploy their applications in different AWS regions in order to achieve a higher level of resilience and reliability for failover. Alliance Key Manager can fully support this approach. You can deploy the production key manager in the same region as your AWS application, and deploy the failover key manager in the remote AWS region where your failover runs. Once configured, they will automatically synchronize the keys and access policy, and will give you an optimal, real time failover across the AWS region boundary. 

Business Continuity and High Availability

The key manager you deploy with SQL Server has to match the high availability strategy you use with SQL Server and your applications. This means the key manager has to fail over in real time. Alliance Key Manager mirrors keys in real time in an active-active configuration. If your database and applications are designed for continuous operation, Alliance Key Manager will give you the immediate failover support you need - and that can be cross-region, outside the cloud, and even across cloud service providers.

Unlimited Databases

Most of our Microsoft SQL Server customers run multiple applications and databases. Alliance Key Manager does not restrict the number of SQL Server databases that you connect to it, and there are no client-side licenses per database. You can encrypt your first database with Alliance Key Manager, and then add any number of additional databases at no charge. Alliance Key Manager does not count or limit the number of databases you protect. You can even protect other databases like MongoDB and MySQL using the same key manager. This is the way enterprise key management should work!

Cloud Independence - It’s real

Amazon Web Services provides a great number of cloud services for applications and storage. Unfortunately, most of the AWS services implement a proprietary interface. The result is cloud lock-in restricting your ability to easily move to other cloud platforms. A business opportunity, merger, acquisition and other events can be painful when you have cloud lock-in. Alliance Key Manager runs in a number of cloud and virtualized environments and will help you avoid cloud lock-in. Cloud independence is real.

Evaluations and Proof-of-Concept

At Townsend Security we know that key management is a part of your critical infrastructure. We make evaluations and Proof-of-Concept projects extremely easy. You can launch Alliance Key Manager for AWS directly from the AWS Marketplace, get access to Quick Start guides for SQL Server, and be up and running quickly. Alliance Key Manager will automatically license for a free 30-day evaluation period, and you will have access to our technical support group for assistance.

HINT: When you launch Alliance Key Manager from the AWS Marketplace, be sure to register with us. Amazon does not share your company information with us, so we won’t be able to help unless you register. Here is the link to register.

True Enterprise Key Management for SQL Server, dedicated to you, is a couple of clicks away right from the AWS Marketplace. 

Patrick

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.

Alliance Key Manager supports Transparent Data Encryption (TDE) in Microsoft SQL Server 2019 Standard Edition. 

Townsend Security today announced Alliance Key Manager, its affordable FIPS 140-2 compliant encryption key manager, supports Microsoft SQL Server 2019 Standard Edition.  Users of Microsoft SQL Server Standard Edition can now easily meet compliance (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), coupled with Townsend Security’s Alliance Key Manager for SQL Server, organizations can protect their private data at a lower cost.

Alliance Key Manager, a FIPS 140-2 compliant encryption key management solution, allows enterprises to effectively encrypt data and meet security requirements in less time with a flexible, centralized offering. The solution provides full life-cycle management of encryption keys for a wide variety of applications, including Microsoft SQL Server Enterprise and Standard editions.

“We were pleased to see Microsoft announced that SQL Server 2019 Standard Edition would support TDE and EKM, bringing encryption and proper key management without application changes to their popular Standard Edition. By lowering the technical and financial bar to protecting private data, companies of all sizes can easily protect private information,” said Patrick Townsend, Founder and CEO of Townsend Security.  “Since the initial release of TDE and EKM in Microsoft SQL Server Enterprise ten years ago, we have been proud to offer an affordable, industry leading solution - and now extend that to SQL Server Standard users.”

Microsoft SQL Server users can deploy Alliance Key Manager as a hardware security module (HSM), VMware virtual machine, or in the cloud as a native AWS EC2 instance or Microsoft Azure virtual machine. Alliance Key Manager supports seamless migration and hybrid implementations, providing Enterprise’s with options for their high availability strategy. 

“By providing both on-premise and cloud solutions, Enterprise's can easily rely on a centralized key manager to protect their data regardless of where it resides or will in the future. Further, while key management solutions offered by CSPs provide convenience, they leave an organization’s encryption keys accessible to third-party administrators - increasing the risk to their security posture,” continued Townsend. “Our simplified licensing model that avoids charging by the number of endpoint databases and number of keys, makes the upgrade to SQL Server 2019 Standard Edition a no-brainer for many Microsoft users. Microsoft has really done well by its customers.”

Alliance Key Manager for SQL Server is available for a free 30-day evaluation.

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.

Microsoft customers attempting to meet security best practices, compliance regulations, and protection of organization’s digital assets turn to encryption of sensitive data in Microsoft SQL Server databases. The easiest way to encrypt data in SQL Server is through Transparent Data Encryption (TDE) which is a supported feature in SQL Server Enterprise Edition. For a variety of reasons, TDE may not be the optimal solution. Microsoft customers using SQL Server Standard, Web, and Express Editions do not have access to the TDE feature. And even when using SQL Server Enterprise Edition, TDE may not be the best choice for very large databases.

Let’s look at some approaches to column level encryption in SQL Server. The following discussion assumes that you want to meet encryption key management best practices by storing encryption keys away from the protected data, and retain full and exclusive control of your encryption keys.

Column Level Encryption (aka Cell Level Encryption) 
Starting with the release of SQL Server 2008, all Enterprise editions of the database have supported the Extensible Key Management (EKM) architecture. The EKM architecture allows for two encryption options: Transparent Data Encryption (TDE) and Column Level Encryption (CLE). Cell Level Encryption is the term Microsoft uses for column level encryption. SQL Server Enterprise edition customers automatically have access to column level encryption through the EKM architecture.

Encryption Key Management solution providers can support both TDE and Column Level Encryption through their EKM Provider software. However, not all key management providers support both - some only support TDE encryption. If your key management vendor supports Cell Level Encryption this provides a path to column level encryption in SQL Server Enterprise editions.

Application Layer Encryption
Another approach to column level encryption that works well for SQL Server Standard, Web, and Express editions is to implement encryption and decryption at the application layer. This means that your application performs encryption on a column’s content before inserting or updating the database, and performs decryption on a column’s content after reading a value from the database. Almost all modern application languages support the industry standard AES encryption algorithm. Implementing encryption in languages such as C#, Java, Perl, Python, and other programming languages is now efficient and relatively painless.

The challenge that developers face when implementing encryption at the application layer is the proper protection of encryption keys. Security best practices and compliance regulations require a high level of protection of encryption keys. This is best accomplished through the use of an encryption key management system specifically designed to create, securely store, and manage strong encryption keys. For developers, the primary challenge in a SQL Server encryption project is integrating the application with the key manager. Many vendors of key management systems make this easier by providing Software Development Kits (SDKs) and sample code to help the developer accomplish this task easily.

SQL Views and Triggers with User Defined Functions (UDFs)
Another approach to column level encryption involves the use of SQL Views and Triggers. Leveraging the use of User Defined Functions (UDFs) the database administrator and application developer can implement column level encryption by creating SQL Views over existing tables, then implementing SQL Triggers to invoke user defined functions that retrieve encryption keys and perform encryption and decryption tasks. This approach has the advantage of minimizing the amount of application programming that is required, but does require analysis of the SQL database and the use of User Defined Functions. Database administrators and application developers may be able to leverage the SDKs provided by an encryption key management solution to make this process easier.

SQL Server Always Encrypted
One promising new technology recently implemented by Microsoft is SQL Server Always Encrypted. This feature is new with SQL Server 2016 and can work with any edition of SQL Server. It is a client-side architecture which means that column data is encrypted before it is sent to the database, and decrypted after it is retrieved from the database. While there are many constraints in how you can put and get data from SQL Server, it is a promising new technology that will help some customers protect data at the column level. You can expect to see support for Always Encrypted being announced by encryption key management vendors in the near future.

SQL Server in the Azure Cloud
As Microsoft customers and ISVs move to the Azure cloud they are taking their SQL Server applications with them. And it is very common that they take full implementations of SQL Server into their Azure virtual cloud instances. When SQL Server applications run in a virtual machine in Azure they support the same options for column level encryption as described above. This includes support for Cell Level Encryption through the EKM Provider architecture as well as application layer encryption. As in traditional IT infrastructure the challenge of encryption key management follows you into the Azure cloud. Azure customers should look to their encryption key management vendors to provide guidance on support for their key management solution and SDKs in Azure. Not all key management solutions run in Azure and Azure is not a supported platform for all vendor SDKs.

Azure SQL Database
In the Azure cloud Microsoft offers the SQL Server database as a cloud service. That is, Microsoft hosts the SQL Server database in the cloud and your applications can use this service rather than a full instance of SQL Server in your cloud instance. Unfortunately, Azure SQL Database only supports Transparent Data Encryption through the EKM Provider interface and does not yet support Cell Level Encryption. It also restricts encryption key management to only the Azure Key Vault facility requiring you to share key custody with Microsoft.

Column level encryption at the application layer is fully supported for Azure SQL Database. As in the traditional IT infrastructure your C#, Java, and other applications can encrypt and decrypt sensitive data above the database level. Again, check with your key management solution provider to insure that application level SDKs are supported in the Azure cloud.

AWS Cloud and SQL Server
The Amazon Web Service (AWS) implementation of cloud workloads parallels that of Microsoft Azure. You can deploy a full instance of SQL Server in an AWS EC2 instance and use the features of SQL Server as in traditional IT infrastructure. Amazon also overs a database service called Amazon Relational Database Service, or RDS. The RDS service offers multiple relational databases including SQL Server. As with Azure there is no support for key management solutions other than the Amazon Key Management Service (KMS) requiring a shared implementation of key custody.

As you can see there are many ways to implement column level encryption in SQL Server and use good encryption key management practices. I hope this helps you on our journey to more secure data in SQL Server.

Patrick

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