What do Mick Jagger/Keith Richards and Transparent Data Encryption (TDE)/ Extensible Key Management (EKM) have in common? On their own, they are just OK, but when together, are unstoppable. In this article, we will discuss encryption key management and how TDE and EKM come together to protect private data and meet security best practices.
There are many reasons to encrypt private data. Protect customer PII. Compliance. Guard enterprise IP. Fortunately, Microsoft introduced TDE and EKM many years ago in SQL Server 2008 Enterprise, which gives organizations the ability to protect data at rest without modifying their business applications. Unfortunately, however, it is way too easy for businesses who need to protect private information to think they are encrypting their data, but really leaving it vulnerable to a breach.
“How is this possible?” you might ask yourself. When people think about encrypting data, they often only think about the part where the data is obfuscated, failing to consider how they are securing the key that unlocks their encrypted data – essentially leaving the keys to their house underneath the welcome mat. In the security world, this is referred to as storing encryption keys “locally”.
While a quick Google search will yield many results for “SQL Server TDE”, EKM has not been given the attention it deserves. TDE encrypts the storage of an entire database by using a symmetric key called the Data Encryption Key (DEK) which is stored in the database boot record for availability during recovery. Microsoft realized that storing the database encryption key locally represented a significant security risk to “encrypted’ data.
Using the EKM provider architecture, administrators can protect DEK keys by using an asymmetric key stored outside of SQL Server in an external key manager. This model adds an additional layer of security and separates the management of keys and data. 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.
A TDE Overview
TDE encrypts the database data files and database logs in SQL Server Enterprise. By design, there is no need or ability to select which tables are encrypted - all pages that make up the database are encrypted. When data is read from disk SQL Server decrypts the entire block making the data visible to the database engine. When data is inserted or updated, the SQL Server database encrypts the entire block written to disk.
With SQL Server TDE, all of the data in your database is encrypted. This means that non-sensitive data is encrypted as well as sensitive data. There are advantages and disadvantages to this approach. You expend computing resources to encrypt data that may not be sensitive, but you also avoid mistakes in identifying sensitive data. Recent studies show that many businesses have more difficulty identifying their private data than they do encrypting it!
How TDE and EKM Work Together
When you enable TDE on your SQL Server database the database generates a symmetric encryption key and protects it using the EKM Provider software from your external key manager vendor. The EKM Provider software sends the symmetric key to the key server where it is encrypted with an asymmetric key. The encrypted database key is then stored locally on disk in the SQL Server context.
When you start a SQL Server instance, the SQL Server database calls the EKM Provider software to decrypt the database symmetric key so that it can be used for encryption and decryption operations. The decrypted database key is stored in protected memory space and used by the database. The encrypted version of the database key remains on disk. In the event the system terminates abnormally, the only version of the database key is the encrypted version on disk.
Configuration of an EKM Provider
Once the EKM Provider software is installed you must configure usage options. These options may include:
- The hostname or IP address of a key server
- The hostname or IP address of one or more failover key servers
- The name of the SQL Server instance being protected
- The Windows account under which the EKM Provider software will operate
- The location of credentials for the key server
- The fingerprint of the HSM certificate used to protect the TDE key, or a password
- The state of application logging options
- License codes for the EKM Provider
- And possibly other configuration options
The configuration of the EKM Provider may be initiated by the installation process, or may be available from a Windows menu or command line facility. Properly configuring the EKM Provider software is a necessary first step for activating SQL Server encryption through the SQL Server management console. Note: this is provider dependent and not covered in this article.
Encryption Key Management
An encryption strategy is only as good as the method used to protect the encryption keys. The protection of the encryption key is the core to the security of the encrypted data. Security professionals consider the loss of the encryption key as equivalent to the loss of the digital assets. Once an attacker has the encryption key it is trivial to decrypt and steal the data.
Over the years standards and best practices have emerged for encryption key management and these have been embodied in specialized security applications called Key Management Systems (KMS), or Enterprise Key Management (EKMS) systems. The National Institute of Standards and Technology (NIST) has taken a lead in this area with the creation of Special Publication 800-57 entitled “Recommendation for Key Management”. In addition to this important NIST guidance, the organization publishes the Federal Information Processing Standard (FIPS) 140-2 “Security Requirements for Cryptographic Modules”. To serve the needs of organizations needing independent certification that a key management application meets this standard, NIST provides a validation program for FIPS 140-2 compliant systems. All professional key management systems have been validated to FIPS 140-2.
When protecting sensitive SQL Server data with TDE and EKM, it is important that your external key manager allows for these key management best.
- Encryption keys are stored away from the data they protect, usually on specially designed security devices or dedicated virtual services (your key management vendor should have EKM provider software that allows for this).
- Encryption keys are managed by individuals who do not have access to the data storedin the SQL Server database (Separation of Duties).
- Encryption key management requires more than one security administrator to authenticate before performing any critical work on keys (Dual control).
- Key retrieval requests from users and applications are authenticated using industry standard methods.
- Encryption management and key usage are logged in real time and logs are stored on secure log collection servers.
Summary
The hardest part of an encryption strategy is the proper management of encryption keys. Failing to protect encryption keys puts protected data at risk and fails to meet security best practices and compliance regulations. Fortunately, Microsoft provides us with the combination of Transparent Data Encryption (TDE) and Extensible Key Management (EKM) to protect our data. For Microsoft SQL Server customers who have already implemented TDE, the biggest cause of an audit failure or data breach is the lack of good encryption key management – but is an easy problem to solve with EKM.
To properly protect encryption keys, businesses need to consider an external encryption key manager and not store encryption keys locally. Vendors offer key managers based on industry standards (look for FIPS 140-2) in a variety of configurations – physical hardware security module (HSM), VMware images, and in the cloud. Deploying external encryption key management is easier and more affordable than ever, leaving organizations with few excuses for not properly protecting their private data.
