In 2008 the Payment Card Industry Data Security Standard (PCI-DSS) was gaining serious traction and Microsoft released SQL Server 2008 with built-in support for encryption. This was no coincidence. In addition to the PCI standard which mandated encryption of credit card numbers, numerous states in the US had also adopted data breach notification laws with strong recommendations for encryption. The compliance environment was changing dramatically and the SQL Server group at Microsoft provided a path to meet those new compliance regulations. This was a prescient and crucially important enhancement for Microsoft customers - the security threats have increased over time and compliance regulations have become more stringent.
In this multi-part series I want to talk about how Microsoft implemented encryption in SQL Server, how you can leverage this capability to achieve better security and compliance, and the critical issues involved in getting encryption right with SQL Server. I hope you will find this series helpful as you negotiate your SQL Server encryption projects.
Many Microsoft applications and services implement a “Provider” interface. This is the term that Microsoft uses to describe a standardardized, pluggable architecture for third party software companies to integrate and extend the capabilities of Microsoft solutions. With Provider architectures Microsoft enables a method for third parties to register their software to the Microsoft application, and the Microsoft application will then call that software as needed. The third party software must obey rules about the data interface and behavior of their applications. If done correctly the Provider interface provides powerful extensions to Microsoft applications.
Starting with SQL Server 2008 the database implements a Provider interface for encryption and key management. This is named the “Extensible Key Management” Provider interface, or the “EKM Provider”. EKM Provider software performs encryption and key management tasks as an extension to the SQL Server database. The EKM Provider architecture opened the door for third party key management vendors to extend encryption to include proper encryption key management.
From a high level point of view the EKM architecture looks like this:
Every version of SQL Server since 2008 has fully implemented the EKM Provider architecture. This has provided a stable and predictable interface for Microsoft customers and encryption key management vendors.
EKM Architecture - Column and Database Encryption
The EKM Provider architecture supports two different methods of database encryption:
- Cell Level Encryption
- Transparent Database Encryption
Cell level encryption is also known as column level encryption. As its name implies it encrypts data in a column in a table. When a new row is inserted into a table, or when a column in a row is updated, the SQL Server database calls the EKM Provider software to perform encryption. When a column is retrieved from the database through a SQL SELECT or other statement the EKM Provider software is called to perform decryption. The EKM Provider software is responsible for both encryption and key management activity. Implementing cell level encryption requires minor changes to the SQL column definition.
Transparent Database Encryption, or TDE, provides encryption for the entire database and associated log files. All tables and views in the database are fully encrypted. Data is encrypted and decrypted as information is inserted, updated, and retrieved by users and applications. As its name implies, transparent data encryption requires no changes to applications, SQL definitions, or queries. The database works seamlessly after encryption is enabled.
Transparent Data Encryption is the easiest of the two encryption methods to implement. In a following part of these series I will discuss when it makes sense to use TDE and when Cell Level Encryption is a better choice.
Activating the EKM Provider
After installing the EKM Provider software from a third party, the SQL Server database administrator uses the SQL Server management console to activate the EKM Provider and place the database or columns under encryption control. The activation of the EKM Provider software causes the database to be immediately encrypted and all further data operations on the database will invoke the EKM Provider software.
Microsoft EKM Provider for Locally Stored Encryption Keys
Recognizing that some SQL Server customers wanted to encrypt data but did not have the resources or time to implement a key management solution, Microsoft provided a built-in EKM Provider that performs encryption but which stores encryption keys locally in the SQL Server context. Understanding that this was not a security best practice, Microsoft recommends that customers use a proper encryption key management solution that separates encryption keys from the SQL Server database. That was good advice - locally stored encryption keys can be recovered by cyber criminals and the use of external key management systems provides better security and compliance.
EKM Provider Software
EKM Provider software is usually provided by your encryption key management vendor. This means that the features and functions of the EKM Provider software can vary a great deal from one vendor to another. Be sure that you fully understand the architecture and capabilities of the EKM Provider before you deploy SQL Server encryption.
SQL Server Versions That Support EKM
EKM Provider support is available in all Enterprise editions of SQL Server including Data Warehouse and Business Intelligence editions. EKM provider support is not available in Standard, Web, or Express editions of SQL Server.
In the following series I will go into more detail on the EKM Provider interface, transparent data encryption, cell level encryption, business continuity, compliance, and other topics.