+1.800.357.1019

+1.800.357.1019

Feel free to call us toll free at +1.800.357.1019.

If you are in the area you can reach us at +1.360.359.4400.

Standard support
6:30am - 4:00pm PST, Monday - Friday, Free

Premium support
If you own Townsend Security 24x7 support and
have a production down issue outside normal
business hours, please call +1.800.349.0711
and the on-call person will be notified.

International customers, please dial +1.757.278.1926.

Townsend Security Data Privacy Blog

Microsoft SQL Server EKM Providers Implementation Topics

Posted by Patrick Townsend on Mar 28, 2017 11:14:29 AM

This is the fifth in a series looking at the architecture and implementation of SQL Server Transparent Data Encryption (TDE) and Cell Level Encryption (CLE). In this series we look specifically at EKM Provider software architecture and features.

Encryption-Key-Management-SQL-ServerExtensible 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

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 and 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 encryption libraries
  • 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)

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

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 result in 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 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.

Encryption and Key Management for Microsoft SQL Server

Topics: SQL Server, SQL Server encryption


Subscribe to Email Updates

Posts by Topic

see all