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.

This page will discuss 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.

Patrick Townsend, Founder & CEO,
Townsend Security



Click here to view this eBook offline
Encryption & Key Management for SQL Server - Definitive Guide





^Back to Top







Many Microsoft applications and services implement a “Provider” interface. This is the term that Microsoft uses to describe a standardized, 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 Enterprise and SQL Server Standard 2019 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:


SQL Server Encryption EKM Provider Flowchart


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 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. Later, 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

Encryption and key management for SQL ServerEKM 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

Provider support is available in all Enterprise editions of SQL Server including Data Warehouse and Business Intelligence editions, as well as SQL Server 2019 Standard. EKM provider support is not available in Standard, Web, or Express editions of SQL Server.

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.

^Back to Top

Transparent Data Encryption

Most Microsoft Customers who implement encryption in SQL Server use Transparent Data Encryption (TDE) as it is the easiest to implement. No code changes are required and enabling encryption requires just a few commands from the SQL Server console. Let’s look at some of the characteristics of a SQL Server TDE implementation.



Database Encryption

TDE involves the encryption of the entire database space in SQL Server. There is no need or ability to select which tables or views are encrypted, all tables and views in a database are encrypted at rest (on disk). When data is read from disk (or any non-volatile storage) 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. By encrypting everything at rest you are also protected from expansion of regulatory rules about sensitive data protection.


SQL Server -Transparent Data Encryption Flowchart 

  1. SQL Server requests that the Data Encryption Key (DEK) is decrypted by the Key Encryption Key (KEK).
  2. Key Connection sends the decryption request to Alliance Key Manager (AKM).
  3. AKM decrypts the DEK with the KEK.
  4. AKM sends the decrypted DEK to Key Connection.
  5. Key Connection sends the decrypted DEK to SQL Server so that the database can be decrypted.


Protection of the Symmetric Key

When you enable Transparent Data Encryption on your SQL Server database the database generates a symmetric encryption key and protects it using the EKM Provider software from your key management 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.


Starting the SQL Server Instance

During normal operation of SQL Server there is no invocation of the EKM Provider software and therefore no communication with an external key manager. Every normal restart of the SQL Server database instance will cause the EKM Provider software to be called to unlock the database key on the key server. It should be noted that it is the responsibility of the EKM Provider software to handle network or key server failure conditions. SQL Server itself has no visibility on the connection to an encryption key management solution. If the EKM Provider software is unable to retrieve an encryption key, the SQL Server start request will fail. We will discuss business continuity issues in more detail later in this series.


Protecting Database Logs

SQL Server logs may contain sensitive data and therefore must also be encrypted. Transparent Database Encryption addresses this by fully encrypting database logs along with the database itself. It is important to remember that encryption of the logs will only start after TDE is activated AND after you stop and restart the database log. If you neglect to restart logging sensitive data may be exposed in the SQL Server log files.


Click here to view this eBook offline
Encryption & Key Management for SQL Server - Definitive Guide

Table and Index Scanning

Certain SQL operations on indexes require that the SQL Server database have visibility on the entire index of a column. An example of a SELECT statement would be something like this:

SELECT Customer_Name, Customer_ Address FROM Orders WHERE Credit_ Card=’4111111111111111’;

To satisfy this SQL query the database must inspect every row in the table Orders. With TDE this means that the column Credit_Card must be decrypted in every row. Similar operations with the ORDERBY clause can cause table or index scans.


Performance Considerations

Transparent Data Encryption is very optimized for encryption and decryption tasks and will perform well for the majority of database implementations. Microsoft estimates the performance impact of TDE of 2% to 4% and we find this accurate for most of our customers. However, Microsoft SQL Server customers with very large SQL Server databases should use caution when implementing TDE. Be sure that you fully understand the impact of TDE on your application use of large tables. It is always recommended that you perform a proof-of-concept project on very large databases to fully assess the performance impact of encryption.


^Back to Top

Cell Level Encryption

Cell Level Encryption, or CLE, is Microsoft's terminology for Column Level Encryption. With CLE the manner and timing of SQL Server’s call to the EKM Provider software is quite different than for Transparent Data Encryption. It is important to understand these differences in order to know when to use CLE or TDE. Let’s look at some aspects of the CLE implementation.


Encrypted Columns

SQL Server Column EncryptionCell Level Encryption is implemented at the column level in a SQL Server table. Only the column you specify for encryption is protected with strong encryption. You can specify more than one column for CLE in your tables, but care should be taken to avoid performance impacts of multiple column encryption.  Using the same encryption key for multiple columns can reduce the performance impact.

With Cell Level Encryption you may be able to minimize some of the encryption performance impacts on your SQL Server database. Because the EKM Provider is only called when the column must be encrypted or decrypted, you can reduce the encryption overhead with careful implementation of your database application code. If a SQL query does not reference an encrypted column, the EKM Provider will not be invoked to perform decryption. As an example, if you place the column Credit_Card under CLE encryption control, this query will not invoke the EKM Provider for decryption because the credit card number is not returned in the query result:

SELECT Customer_Number, Customer_Name, Customer_Address FROM Orders ORDERBY Customer_Name;

You can see that judicious use of SQL queries may reduce the need to encrypt and decrypt column data.


SQL Application Changes

Unlike Transparent Data Encryption you must make a change to the SQL statement in order to implement Cell Level Encryption. The SQL Server functions “encryptbykey” and “decryptbykey” are used on SQL statements. Here is an example of a SQL query that decrypts a CLE-encrypted column:

select encryptbykey(key_guid(‘my_key’), ‘Hello World’);

Implementing CLE encryption requires application modifications, but may be well worth the additional work.


Encryption and Key Retrieval

The EKM Provider software is called for each column value to perform encryption and decryption. This means a larger number of calls to the EKM Provider compared to Transparent Data Encryption. Because the number of calls to the EKM Provider may be quite large it is important that the encryption and key management functions of the EKM Provider are highly optimized for performance (see the next section).

The EKM Provider software from your key management vendor is responsible for performing encryption of the data. From a compliance point of view it is important to understand the encryption algorithm used to protect data. Be sure that the EKM Provider software uses a standard like the Advanced Encryption Standard (AES) or other industry recognized standard for encryption. It is common to use 128-bit or 256-bit AES for protecting data at rest. Avoid EKM Providers which implement non-standard encryption algorithms.


Encryption Key Caching

When deploying CLE it is important that the EKM Provider software optimize both encryption and key management. The number of calls to the EKM Provider software can be quite high. Good EKM Providers will securely cache the symmetric key in the SQL Server context rather than retrieve a key on each call. The retrieval of an encryption key from a key server takes precious time and multiple calls to retrieve a key can have severe performance impacts. Secure key caching is important for CLE performance. The use of the Microsoft Windows Data Protection Application Program Interface (DPAPI) is commonly used to protect cached keys.


SQL Server Column Encryption - Key Caching


Performance Considerations

When properly implemented Cell Level Encryption can reduce the performance impact of encryption on your SQL Server database. For very large tables with a small number of columns under encryption control, the performance savings can be substantial. This is especially true if the column is used less frequently in your applications.

Vendor Note: Note that each vendor of EKM Provider software implements encryption and key management differently. Some EKM Providers only implement Transparent Data Encryption (TDE). If you suspect you will need Cell Level Encryption be sure that your key management support includes this capability.

^Back to Top

Encryption Key Management

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. For Microsoft SQL Server customers who have already implemented Transparent Data Encryption (TDE) or Cell Level Encryption (CLE) the biggest cause of an audit failure is the lack of good encryption key management.

This is the fourth in a series on the topic of Microsoft SQL Server encryption. Let’s look at some of the characteristics of good encryption key management for SQL Server.


Extensible Key Management (EKM) Providers

As we’ve discussed previously it is the responsibility of key management vendors to provide the Extensible Key Management (EKM) Provider software that is installed and registered to the SQL Server database enabling either TDE or CLE encryption. The software from the key management vendor is installed on the SQL Server instance and provides both encryption and key management services. The SQL Server database administrator does not need to be involved in the actual retrieval of an encryption key - that is the job of the EKM Provider software.

EKM Provider software must handle the encryption and decryption of the database key for Transparent Data Encryption, and must handle the retrieval of a symmetric key for Cell Level Encryption. Key retrieval should be performed in a manner that protects the encryption key from loss on the network, protects the key while in memory, and should properly log the key retrieval event in a system log repository. Encryption key retrieval is normally protected through the use of a secure TLS network connection between the EKM Provider software on SQL Server and the key manager hardware or virtual machine. There are many other critical aspects of EKM Provider key management implementations, and these will be discussed in a future series.


Key Management Industry Standards

Encryption key management systems are cryptographic modules that perform a variety of functions. As a cryptographic module they fall under the standards of the National Institute of Standards and Technology (NIST) and key managers should provably meet NIST standards. The relevant NIST standard for encryption key management is the Federal Information Processing Standard 140-2 (FIPS 140-2), “Security Requirements for Cryptographic Modules”. Key management solutions which implement FIPS 140-2 standards will insure the generation of strong encryption keys, the protection of those keys from corruption or substitution, and the implementation of encryption that provably meets NIST cryptographic standards.

In addition to provide standards for encryption key management NIST also provides a method for vendors to validate that their solutions meet the standard. Encryption key management solutions are tested by chartered security testing laboratories and solutions are then approved directly by NIST. NIST publishes the solutions that have passed FIPS 140-2 testing and Microsoft SQL Server customers should look for FIPS 140-2 validation of any key management solution used to protect the database. Provider software on SQL Server and the key manager hardware or virtual machine. There are many other critical aspects of EKM Provider key management implementations, and these will be discussed in a future series.


Migrating Locally Stored Keys To Key Management

New Call-to-actionMany Microsoft SQL Server users start their encryption projects by using the option to locally store the database encryption key on the local SQL Server instance. While this is not a security best practice, it is a common way to start an encryption project.

Fortunately, it is easy to migrate a locally stored encryption key to a proper key management solution. The migration involves moving the protection of the SQL Server database key to key management protection and does not require the decryption of the database. The database key which is currently protected by local keys and certificates is placed under the protection of the key manager. The EKM Provider software of your vendor then becomes responsible for unlocking the database key (TDE) or retrieving the symmetric key for Cell Level Encryption (CLE).


Oasis Key Management Interoperability Protocol (KMIP)

Many SQL Server customers ask about the KMIP standard for integrating with key managers. While KMIP is important for many reasons, it does not apply to the Microsoft EKM Provider interface. The EKM Provider interface leaves it to the key management vendor to perform the needed cryptographic functions on the key server. These functions do not map to KMIP operations and attributes. While it is advisable to deploy key management solutions that meet KMIP standards, it is not required for SQL Server encryption.


^Back to Top

EKM Provider Implementation

Extensible Key Management (EKM) Provider software can involve several components that include installation of the EKM Provider software, configuration of encryption and key management options, installation of credentials for the key server, and of course the EKM Provider software itself. The EKM Provider software is provided by your encryption key management vendor. In some cases this software may be an extra charge feature from your vendor, and in other cases there may be no charge for the EKM Provider. In any case, the EKM Provider software is specific to the encryption key management solution you are using.


Installation of an EKM Provider

Click here to view this eBook offline
Encryption & Key Management for SQL Server - Definitive Guide

The EKM Provider software that is responsible for direct integration of SQL Server with your key manager and is installed on the actual server where SQL Server is running. While different vendors approach the installation process in different ways, you can expect that a standard Windows MSI installation application will be used to install the software and perform initial configuration of the EKM Provider options. In order to support flexible system administration of your SQL Server environment, the installation of the EKM Provide software usually does not immediately start the encryption process, but this varies from one EKM Provider to another.


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.


Installing & Protecting Key Server Credentials

The protection of the credentials used to access the encryption key server is crucial to your security strategy. The method used to protect those credentials is left to the EKM Provider and varies from one vendor to the next. You should carefully review this strategy to insure that credentials and certificates are properly protected in the SQL Server context. Cyber attacks often attempt to compromise the credentials for a key server in order to compromise the protected data. The compromise of key server credentials should be considered a compromise of protected sensitive data.

In many cases the credentials for an encryption key server are based on PKI certificates. These can be stored in the Windows Certificate Store to achieve the added security and access logging provided by the Windows operating system. Take care to avoid storing certificates, passwords or other credentials in user directories or in areas that are commonly accessed by Windows administrative accounts.


Encryption Software Libraries

When you implement SQL Server Transparent Data Encryption (TDE) the encryption of the database is performed by SQL Server itself. The EKM Provider protects the symmetric encryption key used by TDE, but encryption (usually AES) is performed by SQL Server using Microsoft encryption libraries. When using AES encryption for TDE the performance is generally quite good. While Triple DES (3DES) is an option with SQL Server TDE I would recommend avoiding it. AES performs better and is expected to have a longer life as an industry standard.

When you implement SQL Server Cell Level Encryption (CLE) the encryption is performed by the EKM Provider software, and not by SQL Server. It is therefore important to understand how the vendor of the EKM Provider software has implemented encryption and which encryption library is used. Options for encryption include:

  • Use of native Windows .NET encryptionlibraries
  • Use of vendor encryption libraries that meet industry standards such as AES and 3DES
  • Use of vendor non-standard encryption libraries (not recommended)
  • Use of home-grown encryption libraries (not recommended and not compliant)

While the native Microsoft .NET encryption libraries have good performance, you should attempt to understand the performance of any non-Microsoft encryption libraries. Additionally, the use of non- standard encryption algorithms should be avoided in order to avoid non-compliance with regulatory frameworks.


Configuring EKM Provider Key Server Failover


SQL Server Encryption - EKM Provider Key Server Failover


The use of an encryption key manager requires careful attention to business continuity including high availability failover. Again, support for high availability failover is a vendor-dependent feature, but should be included in your EKM Provider architecture. Key server failover can be triggered by a number of events:

  • Network failure
  • Key server hardware failure
  • Distributed Denial of Service (DDos)
  • Failure of a SQL Server cluster
  • And other events

Because lack of access to the key server will resulting the inability of SQL Server to process information requests, it is critical that the EKM Provider software automatically respond to network or server failures in a timely fashion. Note that for some EKM Providers the failure of a network segment or a key server does not mean the immediate interruption of the SQL Server application. For example, SQL Server TDE encryption interacts with the key server when SQL Server is first started. If the SQL Server instance remains active a temporary failure of a network connection will not interrupt the normal operation of SQL Server. Likewise, if the EKM Provider implements secure key caching there may not be an interruption related to Cell Level Encryption.


EKM Provider Audit Logging

Access logs for SQL Server and EKM Providers are a critical component of a security posture for SQL Server. All components of your SQL Server implementation should generate access and usage logs that can be sent to log collection or a SIEM server in real time. The EKM Provider software should log all activity to the encryption key server. Active monitoring with a SIEM solution is one of the best security protections available. The EKM Provider software should support that aspect of threat detection.


EKM Provider Software Resilience

Lastly, EKM Provider software should be as resilient as possible. Software should automatically recover in the event of a SQL Server database restart, the failure of a connection to a key server, and other unexpected events. Manual intervention by a Windows network administrator or database administrator should not be necessary.


^Back to Top

Business Continuity

When a SQL Server Customer Deploys Transparent Data Encryption (TDE) or Cell Level Encryption (CLE) and protects encryption keys on an encryption key management solution, it is important that the key manager implement reliable business continuity support. Key managers are a part of the critical infrastructure for your applications and should be resilient in the face of common business continuity challenges such as data center damage or destruction ( fire, hurricanes, flood, earthquake, etc.), network failures, and hardware failures. Let’s review some aspects of key management resilience.


Key Management Hardware Resilience

Key management systems come in many form factors including network attached hardware security modules (HSMs), virtual machines for VMware and Hyper-V, cloud instances for Microsoft Azure, Amazon Web Services (AWS), IBM SoftLayer, Google Compute Engine, and other cloud platforms, and as multi- tenant key management solutions such as AWS Key Management Service (KMS) and Azure Key Vault.

When a key manager is deployed as a hardware solution it should implement a number of hardware resiliency features including:

  • RAID protected hard drives
  • Hot swappable hard drives
  • Redundant power supplies
  • Independent Network Interfaces (NICs)
  • Audible alarms

To the greatest extent possible a key management hardware system should be able to protect you from common hardware failure issues.


Key Substitution or Corruption

Key management systems store encryption keys in different types of data stores on non-volatile storage which is subject to key corruption through attack or hardware failure, or subject to key substitution through attack. Key management systems should use common integrity techniques such as hash-based message authentication code (HMAC) or similar technologies to detect this type of failure. Encryption keys should not be returned to a user or application in the event integrity checks fail, and all integrity check failures should be reported in audit and system logs. Additionally the integrity of the key database and application should be checked when the key manager initially starts processing. Early detection and quarantine of bad encryption keys helps prevent data corruption and gives the security administrator the ability to restore proper operation of the key manager.


Real-Time Key Mirroring and Access Policy Mirroring

Because key management systems are a part of an organization’s critical infrastructure, they should implement real-time mirroring of encryption keys and access policies to one or more secondary key servers. The real-time nature of key mirroring is important to prevent the loss of an encryption key after it is provisioned but before it has been copied to a secondary system.

Real-time mirroring should also be able to recover from temporary network outages. If keys cannot be mirrored because the connection between the primary and secondary servers is interrupted, the key mirroring facility should automatically recover and resume mirroring when the network is operational again. This reduces the chance that keys are lost due to latency in mirroring.

Many organizations deploy complex distributed networks that require multiple secondary key servers. While most key management installations involve just one production and one secondary key server, good key management mirroring should involve the ability of a primary key server to mirror to multiple secondary key servers.


Active-Active Key Mirroring

SQL Server EKM Provider - Active-Active Key Mirroring


Expanding on the topic of encryption key and access policy mirroring, it is important that key management systems fully support role-swap system recovery operations and this involves the dynamic change in roles between a primary and secondary key server. When a primary key server is unavailable a secondary key server automatically steps in to serve various encryption key functions. In this situation it is important that the secondary key server now becomes the primary key server for a period of time. New encryption keys may be created, the status of existing keys may change, and access policies may also change. A good key mirroring architecture will allow for these changes to migrate back to the original primary key server when it becomes available. This is the central feature of Active-Active mirroring implementations.


Key Management Monitoring

Because key management systems are critical infrastructure it is important to deploy monitoring tools to insure a high service level. Key management systems should generate and transmit system log information to a monitoring solution, and the key management system should enable monitoring by external monitoring applications. In the event a key server becomes unavailable it is important to identify the outage quickly.


Key Management System Logging and Audit

Podcast: VMware and SQL Server EncryptionAnother important aspect of key management business continuity is proper system logging of the key management server. Key management systems are high value targets of cyber criminals and active monitoring of key management system logs can detect an attack early in the cycle.

Additionally, key management systems should audit all management and use of encryption keys and policies. A good key management solution will audit all actions on encryption keys from creation to deletion, all changes to key access policies, and all access to keys by users and applications. These audit logs should be transmitted to a log collection or SIEM monitoring solution in real time.


Key Management Backup and Restore

As critical systems key managers must implement backup and restore functions. In the event of a catastrophic loss of key management infrastructure, restoring to a known good state is a core requirement. Good key management systems enable secure, automated backup of the data encryption keys, key encryption keys, server configuration, and access policies.

Key management systems differ from traditional business applications in one important aspect - data encryption keys should be backed up separately from key encryption keys. You should be able to backup data encryption keys automatically or on demand, but you should take care to separately backup and restore key encryption keys. This is a core requirement for key management systems.


^Back to Top

Key Management Best Practices

Protecting encryption keys from loss is the most important part of an encryption strategy and there is good documentation on security best practices for encryption key management. Security best practices for key management also appear in many compliance regulations such as the PCI-DSS and others.


Separating Encryption Keys from the Data They Protect

One of the core best practices for encryption key management is to separate the storage of encryption keys away from the data that they protect. Using a key management system designed for the creation and storage of keys is central to this security best practice. The separation of encryption keys away from protected data makes the compromise of sensitive data much harder. Compromising and retrieving locally stored encryption keys is usually a simple task, and this is true for SQL Server locally stored keys.

These common practices are weak security for SQL Server encryption keys:

  • Encryption keys stored in application programs
  • Encryption keys stored in a SQL Server table
  • Encryption keys stored in folders on a local or remote Windows server
  • Encryption keys stored with password protection
  • Encryption keys stored locally by SQL Server Transparent Data Encryption (TDE)

Separating encryption keys from protected data substantially raises the bar for attackers, and largely eliminates the threat of loss from replaced hard drives, stolen virtual machine or cloud images, and lost backup images.


Separation of Duties

Separation of Duties for Encryption Key Management

Separation of Duties (SOD), sometimes called Segregation of Duties, is a core security principle in financial, medical and defense applications. In the context of protecting sensitive data separation of duties is important to minimize accidental or intentional loss of sensitive data by insiders. As applied to Information Systems separation of duties requires that those who create and manage encryption keys should not have access to sensitive data, and those who manage databases (database administrators) should not have access to encryption keys.

Organizations should assign encryption key management duties to specific security administrators who do not have database administration duties, and not assign key management duties to DBAs. In modern key management systems this is managed by the assignment of user-friendly names to encryption keys. The user-friendly names for encryption keys, sometimes call key aliases, are exchanged between the security administrator and the SQL Server DBA. This avoids sharing the actual encryption keys.


Dual Control

Dual Control for Encryption Key Management

The NIST guide for Key Management Best Practices defines the encryption key management role as critical part of the security strategy. Management of encryption key systems should implement Dual Control. This means that two or more security administrators should authenticate to the key server before any work is performed. Requiring a quorum of security administrators to authenticate minimizes the threat of insider damage or theft of critical encryption key secrets.








Split Knowledge

Split Knowledge for Encryption Key Management

Because encryption keys are critical to the security of protected data, this security best practice requires that no one person sees or takes possession of an encryption key that is visible in the clear. Modern key management systems minimize this threat by not exporting or displaying encryption keys to administrators or users, and not using passwords as a part of the key creation process. If you use a key management system that generates or exports keys based on passwords, or which exposes encryption keys in the clear to administrators or users, you should implement split knowledge controls. SQL Server protects Transparent Data Encryption keys by never storing them in the clear on the SQL Server instance.





Minimum Number of Key Administrators

Another security best practice designed to reduce insider threats and the loss of administrative credentials is to keep the number of people who manage your key management system to the smallest reasonable number. The fewer administrators who have access to the key management system the fewer opportunities for accidental or intentional loss of encryption keys.


Multi-Factor Authentication

Like any critical component of our information management system, encryption key management systems should implement multi-factor authentication, sometimes called two factor authentication, to reduce the threat of the theft of administrative credentials. Cyber criminals use a number of techniques to capture important administrative credentials including phishing, social engineering, memory scraping, and other types of attacks. Multi-factor authentication is an important security control and best practice for encryption key management systems.


Physical Security

Physical security controls are also an important security best practice for encryption key management and similar security applications and devices. Physical controls in the data center include keyed access to server rooms, locked cabinets and racks, video monitoring and other controls. While physical security of key management hardware security modules (HSMs) is fairly easy to accomplish, it is also necessary to insure physical controls for virtual environments that use VMware or Hyper-V, and for cloud environments. In cloud environments you may have to work with your cloud service provider to insure proper protection of virtualized key management server instances.


Data Encryption Key Rotation

Periodically changing the data encryption key (DEK) of your protected data is also a security best practice and required by some compliance regulations like PCI- DSS. This is sometimes referred to as “key rotation” or “key rollover”. Your key management system may help in this area by allowing the specification of the crypto-period of the key and automatically changing the key for you. Of course, the retention of the older key is needed to insure that encrypted data can be decrypted. Changing encryption keys and re-encrypting sensitive data is a security best practice.


Key Encryption Key Rotation

In proper key management systems the data encryption keys (DEK) are protected by separate key encryption keys (KEK). Key encryption keys are only used to protect DEK and are never used to directly protect sensitive data. Key encryption keys reside only on the key management system and must not leave that system except as a part of a secure backup. KEK rotation is generally less frequent than DEK rotation, but should be a part of your key management system.


Administrator & User Authentication

Key management systems are designed to generate strong encryption keys and protect them from loss. Of course, it must also enable the use of encryption keys to protect sensitive data. The key management system should implement strong authentication controls for access to the key server, and further should implement strong authentication for the use of specific encryption keys. This is normally implemented using PKI infrastructure and mutual authentication between clients and servers. This exceeds the typical authentication that you might encounter using a web browser with a secure session. A key management system should insure that a secure session is negotiated by a known and trusted client. To ensure this most key management systems incorporate a private certificate authority and do not rely on public certificate authorities to insure the highest level of trust in the authentication.


Network Segmentation

Click here to view this eBook offline
Encryption & Key Management for SQL Server - Definitive Guide

As critical security systems it is a best practice to use network segmentation of key management systems and of the applications that access the key management systems. Network segmentation can be accomplished through normal IT infrastructure, through virtualized network management as implemented by VMware, and in cloud platforms using cloud service provider network segmentation rules. Further network access controls can often be implemented in the key management system using firewall rules.


Audit & Logging

Lastly, all security devices including key management systems should collect and transmit audit and system logs to a log collection server or SIEM monitoring solution. Active monitoring of critical application and security systems is an important security control and best practice. Key management systems should fully implement support for active monitoring.

In summary, security best practices for key management systems used for SQL Server data protection should reflect well-understood and documented best practices for security devices. The core source of these best practices is the National Institute for Standards and Technology’s Special Publication 800-57, “Recommendation for Key Management.” Your key management solution for SQL Server should implement these best practices.


^Back to Top

Key Management Standards

For may customers in highly regulated industries creating an encryption strategy means adopting industry standards and the standards requirements of compliance regulations. In this part of the series on Microsoft SQL Server encryption we will look in more detail at the relevant standards for encryption, encryption key management, and key management interfaces.

It is important to note that there are different industry standards across the international landscape. We will primarily at the standards published by the National Institute of Standards and Technology (NIST) but it is important to understand that other standards bodies work in this area including the International Organization for Standardization (ISO) and the American National Standards Institute (ANSI). There are some differences between the published standards, but there is a great deal of interconnection and overlap. We will focus here on standards that are common across different standards bodies as many organizations must meet a variety of international standards.


Standards for Encryption

AES Encryption for SQL Server Encryption

In 2001 the National institute for Standards and Technology worked with an international group of cryptographers and security experts to evaluate encryption algorithms and to eventually adopt the Rijndael algorithm as the Advanced Encryption Standard (AES). AES is now also an adopted standard within ISO and other international standards organizations. NIST published the standard as Federal Information Processing Standard 197, or FIPS-197.

AES is now the predominant choice for encrypting data at rest, and is a part of common Internet protocols that combine asymmetric key operations with symmetric key operations. AES is a symmetric block cipher using 128-bit blocks and supporting multiple key sizes of 128, 192 and 256-bits. Most new implementations of AES encryption use the 256-bit key size for the stronger security it provides.

Microsoft SQL Server customers should choose the AES encryption algorithm when encrypting SQL Server databases with Transparent Data Encryption (TDE) or Cell Level Encryption (CLE). While other standard methods such as Triple DES are available, using AES is recommended for better ongoing compliance.


Standards for Encryption Key Managers

FIPS 140-2 for SQL Server EncryptionNIST classifies encryption key management systems a “Cryptographic Modules” and applies the Federal Information Processing Standard 140-2 (FIPS 140-2, “Security Requirements for Cryptographic Modules”) to them. In addition to promulgating this standard, NIST also provides a certification and validation program via the National Voluntary Laboratory Accreditation Program (NVLAP). This means that encryption key management systems can be formally certified that they meet the FIPS 140-2 standard. All professional key management systems have been validated through the NVLAP program and Microsoft SQL Server customers should look for this level of compliance.

While encryption key management systems can be validated to the FIPS 140-2 standard it does not automatically follow that a software vendor with a SQL Server TDE solution also uses a validated key server. Always be sure to check with the NIST web site to insure a key management vendor’s FIPS 140-2 compliance.


Standards for Secure Key Management Interfaces

KMIP for SQL Server EncryptionWhile the NIST FIPS 140-2 validation of a key server indicates compliance with an important industry cryptographic standard, it does not specify how client applications actually communicate and interoperate with a key server. The Key Management Interoperability Protocol (KMIP) provides this interface standard. The KMIP protocol is promulgated through the OASIS standards group in the KMIP Technical Committee.

The KMIP standard defines the interface to a key management solution for creating encryption keys, assigning various attributes and status values to keys, performing encryption key retrieval, executing encryption services, and a variety of other operations that are common to encryption key management systems. The KMIP standard does not specify operational functions of a KMIP key server such as network configuration, firewall rules, system logging and other server functions.

The Microsoft SQL Server Extensible Key Management (EKM) interface specification pre-dates the OASIS KMIP standard and does not implement that standard. The interface to the key management system is left to the particular key management vendor to implement. However, KMIP remains important to the SQL Server customer as other database and application services may need to use key management services.


Standards for Secure Key Management Connections

Client-side applications that need to connect to a key server have traditionally used one of two methods:

  • Vendor-supplied software libraries
  • A secure Transport Layer Security (TLS) connection

Prior to the promotion of the OASIS KMIP standard it was common for encryption key management vendors to implement software libraries that performed the functions of securely connecting to a key server and retrieving keys or performing key management functions. This required that the customer install vendor-supplied software on each client-side system, configure the software, install updates on a periodic basis, and manage the software environment. This could be a labor-intensive process.

The OASIS KMIP protocol defines a secure TLS interface to the key manager that does not require vendor-supplied software libraries. Instead the client- side system uses the Internet standard TLS protocol to create the secure connection. This is sometimes referred to as an “agentless” connection. Almost all professional key management systems now support the KMIP protocol and use an agent-less, secure TLS session for the connection.

Microsoft SQL Server customers that deploy Transparent Data Encryption (TDE) or Cell Level Encryption (CLE) depend on software libraries provided by the key management vendor. The SQL Server interface pre-dates the KMIP specification. Note that the vendor-supplied solution may still use a secure TLS interface to the key manager in their own solution.


SQL Server Standards Summary

Microsoft SQL Server customers are well-advised to use standard encryption methods and key management systems that meet industry standards. This includes the use of standard AES encryption for TDE or CLE encryption, and the use of an encryption key management solution that meets FIPS 140-2 and KMIP compliance. Implementing encryption and key management based on industry standards ensures compliance with common industry regulations.

Microsoft SQL Server customers should choose the AES encryption algorithm when encrypting SQL Server databases with Transparent Data Encryption (TDE) or Cell Level Encryption (CLE). While other standard methods such as Triple DES are available, using AES is recommended for better ongoing compliance.

^Back to Top

Platform Support

Microsoft SQL Server Customers often run applications in complex environments that span the on-premise data center, hosting platforms, VMware data centers, cloud SQL Server database as a service, and full Infrastructure-as-a-Service cloud platforms. Hybrid combinations of these platforms are more the rule than the exception and this adds complexity to the IT strategy. When we look at SQL Server encryption it is important to understand where database server support is located, and where encryption key management servers are located.


Traditional IT Glass House

Encryption and key management for SQL ServerWhile there has been a dramatic move to virtualize data centers with VMware and other virtualization technologies, the traditional customer data center still houses a large number of SQL Server applications. Some of these applications process sensitive data that the Enterprise does not want to expose to the Internet, or core intellectual property that must remain inside the data center to meet governance requirements, or applications that have not yet moved to virtualized or cloud platforms. Whatever the reason for housing SQL Server applications in the data center, the SQL Server encryption strategy should support that environment. In many ways this is the easiest environment in which to deploy SQL Server encryption and key management. Almost all vendors of encryption key management solutions for SQL Server support a traditional data center deployment.


On-Premise VMware Infrastructure

For good reasons most SQL Server customers have moved to virtualize the data center using VMware technologies. The administrative and cost benefits of virtualizing Windows and Linux workloads are compelling and most of us are taking advantage of VMware technologies. For SQL Server customers deploying encryption in the VMware infrastructure can present some challenges.

The first challenge is ensuring vendor support for SQL Server encryption running in a VMware virtualized Windows server. Not all vendors of SQL Server Transparent Data Encryption and Cell Level Encryption solutions support the VMware environment, nor all common versions of VMware.

The second major challenge is how to deploy encryption key management in VMware infrastructure. Some vendor key management solutions only support deployment as hardware security modules (HSMs), and this architecture is exactly what VMware customers are trying to avoid. An optimal key management solution for SQL Server in VMware environments would also be virtualized and be installable in an appropriate VMware security group. Securing encryption key management systems in VMware has different and more stringent requirements that securing SQL Server applications. Fortunately VMware has provided good guidance on the steps you should take to secure true VMware instances of key managers.


Hosted VMware Infrastructure

Several hosting providers and cloud service providers have implemented support for full VMware deployments. Rackspace and IBM SoftLayer are the first that come to mind, but there are many other service providers in this area. These service providers offer the full VMware application stack as a part of the deployment and this can be an attractive way for a SQL Server customer running on-premise VMware infrastructure to move the cloud. In most cases the same VMware infrastructure that runs on-premise can be replicated in this hosted environment. This means that SQL Server encryption and key management solutions can also easily move.

VMware also implements an architecture called vCloud. This is a special implementation of VMware infrastructure in a hosted environment. The range of services that surround a vCloud implementation varies from one hosting provider to another. In some cases the vCloud implementation only supports the simple case of running a VMware virtual machine. In other cases the full range of VMware management facilities are available. SQL Server customers must carefully evaluate vCloud implementations to insure that they will support the necessary SQL Server encryption and key management solutions.


Cloud (AWS, Azure, Etc.)

Migrating or implementing SQL Server applications in pure cloud platforms represents a significant challenge related to encryption and key management. In most cases a migration from on-premise VMware infrastructure to cloud will involve many changes to the methods with which applications are deployed, configured, managed, and secured. And new challenges arise around SQL Server encryption and key management.

For SQL Server encryption the first challenge has to do with the deployment of an EKM Provider to integrate with the key management system. Some cloud platforms provide a minimal implementation of an EKM Provider to their own shared key management infrastructure. Some provide no native cloud support for EKM Providers. SQL Server customers typically turn to key management vendors for the EKM Provider support needed to integrate SQL Server encryption with a key management system. Care should be taken to insure that the key management vendor fully supports the cloud platform and the method of deployment.

Encryption key management for SQL Server presents even larger challenges for the Enterprise customer. Cloud platforms may not provide flexible choices for encryption key management, and the issue of key custody (does the cloud service provider have access to your encryption keys) can be very difficult. In almost all cases a key management service provided by a cloud platform is accessible either logically or physically by cloud service provider employees. Great care should be taken to ensure that your selection of a key management solution in the cloud meets your compliance, governance and risk management strategies.

Ideally you will have a complete range of choices on where to deploy the SQL Server key management solution. Being able to deploy a fully cloud-based key management solution that is dedicated to you, or choosing to deploy a key management solution outside of the cloud as a VMware instance or hardware security module should be reasonable choices available to you. You may start with a cloud- based key management solution and then decide to migrate to on-premise key management. This should be a well-supported strategy by your cloud service provider and your key management vendor.



As mentioned above the Enterprise SQL Server customer generally has a mix of on-premise and hosted or cloud applications. These applications often need to integrate data exchange. While SQL Server encryption is relatively easy to implement on any of the above platforms, a seamless integration of key management across platforms can be a challenge. Traditional hardware security modules often have different interfaces than more modern virtual or cloud key managers. When this is the case the automation of key and key access policy sharing can be very difficult to accomplish. In addition, business continuity functions such as backup/restore and failover may in some cases be impossible to accomplish. You will want to ensure that you key management vendor can easily integrate across these disparate platforms.



Rapidly evolving cloud and virtualization platforms present on-going challenges to the SQL Server customer. VMware infrastructure remains important and organizations of all sizes are looking to leverage the benefits of the cloud. It is likely that hybrid deployments of applications across all of the above platforms will remain the rule rather than the exception. SQL Server customers should take care when selecting and deploying an encryption and key management solution that they do not hinder cloud and virtualization efforts.

For good reasons most SQL Server customers have moved to virtualize the data center using VMware technologies.


SQL Server EKM Provider - Multiple Platforms


^Back to Top

Vendor Considerations

Generally, the considerations for sourcing encryption and key management solutions for SQL Server will be similar to any relationship you develop with a vendor. The limited number of vendors in this space can limit the choices you have, but there are good solutions to choose from.



Vendors take a variety of approaches to licensing their EKM Provider software and their key management solution. The main difference is in licensing constraints on the SQL Server side. You may start your first SQL Server encryption project with a rather limited scope. But as you continue to encrypt more sensitive data you may need to scale up the number of SQL Server client-side license. Some encryption vendors license software based on the number of SQL Server instances that you place under protection. Others provide unlimited numbers of client –side licenses after you acquire the key manager. Be sure you understand the licensing terms of each solution you evaluate, and be sure to understand your long term needs.



Documentation on your SQL Server implementation will be crucial for long term success. In addition to documentation on the installation and configuration, be sure that your vendor provides documentation on key rotation, applying patches to the key manager, upgrading the key manager to new versions, and problem determination. All of these aspects should be covered in vendor documentation.



While key management solutions have become much simpler over time, you should still expect to receive some operational and technical training from your encryption and key management vendor. Gone are the days when this meant a lot of on-site educational expense. Modern encryption and key management solutions may require only a few hours of coaching and training to deploy and maintain. Be sure your encryption and key management vendor has a program to deliver training in a timely fashion.


Customer Support

Many businesses have devalued the customer support experience and this can present a problem for SQL Server users. When you have a problem with encryption or key management, it is likely to affect your application service levels. Before acquiring your SQL Server encryption solution be sure to schedule time with the customer support group. Do they have a formal problem tracking system? Do you have access to all problem tickets you raise? Does the customer support group respond in a timely fashion? Is there a 24/7 response number? All of the normal customer support questions you might ask are relevant to a SQL Server encryption solution. We all know what really bad customer support looks like, be sure there is a good team standing behind the solution you deploy.



The modern Enterprise is often geographically distributed and this can make deployment and training difficult. While SQL Server encryption and key management solutions can be simple to deploy and configure, you may want to be sure that you vendor can send staff on site for this type of support.

Vendors take a variety of approaches to licensing their EKM Provider software and their key management solution. The main difference is in licensing constraints on the SQL Server side.

^Back to Top

Alliance Key Manager

Alliance Key Manager for SQL Server“A very cost effective solution in terms of performance, manageability, security, and availability. As a result, my company was quickly able to implement full database encryption leveraging the AKM as our key management solution in weeks. Comparable solutions could have taken months.”

Townsend Security is helping Microsoft SQL Server customers with Alliance Key Manager. The solution includes the Key Connection for SQL Server application to help Microsoft users implement Trans- parent Data Encryption (TDE) and Cell Level Encryption (column level encryption) without the need for application development. This application installs as a service on SQL Server and provides the Extensible Key Management (EKM) provider software. With integrated support for multiple, redundant AKM key servers Microsoft customers can deploy encryption rapidly and without programming.

Alliance Key Manager is FIPS 140-2 compliant and in use by over 3,000 organizations worldwide. The solution is available as a hardware security module (HSM), VMware instance, and in the cloud (Amazon Web Services, Microsoft Azure, and VMware vCloud). Townsend Security offers a 30-day, fully-functional evaluation of Alliance Key Manager.


Click here to view this eBook offline