+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 Encryption Key Management

Posted by Patrick Townsend on Mar 20, 2017 2:01:48 PM

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.

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

Enterprise Key Management Solutions
The proper generation, storage, protection and management of encryption keys is the core purpose of professional encryption key management solutions. As security devices an encryption key manager is responsible for creating strong encryption keys that meet industry standards, and protecting those keys from loss during the lifecycle of the keys. Encryption key managers may be hardware security modules (HSMs), virtual servers (VMware, Hyper-V, etc.), or multi-tenant or dedicated cloud instances. In addition to implementing industry standards for encryption key management, key servers will provide a variety of authentication, systems management, and audit functions to meet security best practices and compliance regulations. Microsoft SQL Server customers who want to achieve compliance with common regulations should look to deploy a professional, certified and validated key management solution.

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.

Migrating Locally Stored Keys to Key Management
Many 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 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.

To this point we have defined the SQL Server encryption architecture, options for implementing SQL Server encryption (TDE and CLE), and basic requirements for encryption key management. In the next part of this series we will look at EKM Provider implementation topics as well as business continuity topics.

Patrick
Encryption and Key Management for Microsoft SQL Server

Topics: SQL Server, SQL Server encryption

Microsoft SQL Server Automatic Encryption - Cell Level Encryption

Posted by Patrick Townsend on Feb 21, 2017 9:11:00 AM

In this third part of the series on Microsoft SQL Server encryption we look at Cell Level Encryption, or CLE, which is Microsoft 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
Encryption-Key-Management-SQL-Server Cell 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 (see below).

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 encrypts a CLE-encrypted column:

select encryptbykey(key_guid('my_key'), 'Hello World');

Implementing CLE encryption in your SQL Server database requires modifications to your applications, 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.

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.

CLE 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.

In the next part of this series we will look at encryption key management in SQL Server.

Patrick

Encryption and Key Management for Microsoft SQL Server

Topics: SQL Server, Cell Level Encryption, SQL Server encryption

Microsoft SQL Server Automatic Encryption - Transparent Data Encryption

Posted by Patrick Townsend on Feb 14, 2017 8:33:00 AM

In this second part of the series on Microsoft SQL Server encryption I want to focus on Transparent Database Encryption, or TDE. Most Microsoft customers who implement encryption in SQL Server use Transparent Data Encryption as it is the easiest to deploy. 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 TDE implementation.

Database Encryption
Download the Webinar - Just Click! 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 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-decrypts-db.png

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.

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.

In the next part of this series we will look at the other option for SQL Server encryption - Cell Level Encryption, also called column level encryption.

Patrick

Encryption and key management for SQL Server

Topics: SQL Server, Transparent Data Encryption (TDE)

Microsoft SQL Server Encryption - An Introduction

Posted by Patrick Townsend on Feb 6, 2017 2:33:16 PM

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.

Download the Webinar - Just Click! 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.

Architecture
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:

SQL-EKM.png

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.

Patrick

Encryption and key management for SQL Server

Topics: Encryption, SQL Server

Fixing the TDE Key Management Problem in Microsoft SQL Server

Posted by Patrick Townsend on Jan 10, 2017 7:31:56 AM

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.

Download the Webinar - Just Click! 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 http://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

Encryption and key management for SQL Server

Topics: SQL Server, Transparent Data Encryption (TDE)

Encryption & Key Management for SQL Server

Posted by Luke Probasco on Jul 22, 2016 3:27:11 PM

Excerpt from the eBook "Encryption & Key Management for Microsoft SQL Server."


Microsoft SQL Server has become a ubiquitous storage mechanism for all types of digital assets. Protecting these data assets in SQL Server is a top priority for business executives, security specialists, and IT professionals.  The loss of sensitive data can be devastating to the organization and in some cases represents a catastrophic loss. There is no alternative to a digital existence and cybercriminals, political activists, and state actors have become more and more adept at stealing this information.  To properly protect this information, businesses are turning to encryption and key management.

Encryption

Encryption and key management for SQL Server Encryption in the broadest sense means obscuring information to make it inaccessible to un- authorized access. But here we will use the term in its more precise and common use – the use of well accepted encryption algorithms based on mathematical proofs and which have been embodied and approved as international standards.

Many approaches to encryption do not meet minimal requirements for security and compliance. Our definition of encryption excludes:

  • Homegrown methods developed by even experienced and talented programmers.
  • Emerging encryption methods that are not yet widely accepted.
  • Encryption methods that are widely accepted as secure, but which have not been adopted by standards organizations.
  • Data substitution and masking methods not based on encryption.

An example of an encryption method that does meet our criteria would include the Advanced Encryption Standard (AES) which is sometimes knows as Rijndael, Triple Data Encryption Standard (3DES), RSA, and Elliptic Curve encryption methods.

In the context of protecting data in a SQL Server data- base, the most common encryption method protecting whole databases or an individual column in a table is AES. All key sizes of AES (128-bit, 192-bit, and 256-bit) are considered secure and are appropriate for protecting digital assets. Many organizations chose 256- bit AES for this purpose due to the larger key size and stronger security.

One major additional benefit of using an industry standard such as AES is that it meets many compliance requirements or recommendations for the use of industry standard encryption. This includes the PCI Data Security Standard (PCI-DSS), HIPAA, FFIEC, and the EU General Data Protection Regulation (EU GDPR).

Key Management

It is not possible to discuss an encryption strategy without discussing the protection of encryption keys. An encryption strategy is only as good as the method used to protect the encryption keys. Encryption algorithms such as AES and Triple DES are public and readily available to any attacker. The protection of the encryption key is the core to the security of the encrypted data. This is why 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.

Generating strong encryption keys and protecting them is harder that it might at first appear. The generation of strong encryption keys depends on the use of random number generation schemes, and modern computers do not excel at doing things randomly. Specialized software routines are needed to generate strong encryption keys. Encryption keys must also be securely stored away from the data they protect, and yet must be readily available to users and applications that are authorized to access the sensitive data. Authenticating that a user or application is authorized to an encryption key is a large focus of key management systems.

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 (EKM) 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 encryption, look for these core principles of key management:

  • Encryption keys are stored away from the data they protect, usually on specially designed security devices or dedicated virtual servers.
  • Encryption keys are managed by individuals who do not have access to the data stored in 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.
  • Encryption key management systems have been validated to FIPS 140-2 and the Key Management Interoperability Protocol (KMIP).

These are just a few of the core requirements for deploying a professional key management solution to protect your SQL Server data.

Encryption and key management for SQL Server

 

Topics: Encryption, SQL Server

When Encrypting Databases, Does Key Connection for SQL Server Cache the Encryption Key?

Posted by Patrick Townsend on Jul 22, 2016 8:46:30 AM

Customers who need to encrypt data in Microsoft SQL Server databases know that they must protect the encryption key with appropriate controls to meet compliance regulations and to achieve safe harbor in the event of a data breach. Townsend Security's Alliance Key Manager solution provides the Extensible Key Management (EKM) software to make proper key management a breeze. Called Key Connection for SQL Server, this EKM Provider software is installed on the server hosting the SQL Server database and it talks seamlessly to one or more Alliance Key Manager servers running in a separate server instance. Customers get proper key management that meets compliance regulations such as PCI-DSS in an easy-to-deploy solution.

Encryption-Key-Management-SQL-Server Performance is always a consideration when it comes to enabling encryption, so customers naturally ask us about key caching. Does Key Connection for SQL Server cache the encryption keys to enable better performance?

The short answer is Yes, it does.

How it does key caching depends on whether you use Transparent Data Encryption (TDE) or Cell Level Encryption (CLE). Let’s drill into each of these cases.

Transparent Data Encryption (TDE)
The implementation of TDE by Microsoft involves encrypting the entire table space and the database logs. It is the easiest type of encryption to deploy as it requires no changes to the actual application that uses the SQL Server database. You can implement TDE encryption by installing the Key Connection For SQL Server software and issuing four commands through the SQL Server management console. Restart logging to insure that it is encrypted and you are done.

So with TDE, how are keys managed? The TDE architecture involves SQL Server generating a symmetric key (usually a 256-bit AES key) and then asking Alliance Key Manager to encrypt it with an RSA key. This encrypted symmetric key is then stored on the server that hosts the SQL Server database. When you start SQL Server (or restart it, as the case may be) the SQL Server instance asks Alliance Key Manager to use RSA decryption to decrypt the symmetric key. Once that is complete the SQL Server instance has the key it needs and no longer needs to communicate with Alliance Key Manager. There is no need for key caching and the key will be decrypted the next time that SQL Server starts.

Cell Level Encryption (CLE)
The implementation of CLE by Microsoft SQL Server is quite different than for TDE. The EKM Provider software is still responsible for managing the symmetric encryption key, but it is accomplished in a different way. You must make small changes to your application SQL statements to request encryption and decryption of the cell contents. When CLE is activated the Key Connection for SQL Server software is called for each column and row that needs to be encrypted or decrypted. This means a lot more calls to the EKM Provider software and this is where key caching is very important.

The Key Connection for SQL Server software in this case does cache the symmetric encryption key (usually a 256-bit AES key) in order to improve performance. The key is cached using an equally strong RSA key to prevent key capture by malware. When SQL Server calls the Townsend Security EKM provider the software retrieves the key from the key server and will cache it locally for a 24 hour period. For the next 24 hours all subsequent requests for encryption or decryption are satisfied locally without the need to retrieve the key again. After 24 hours, the key is discarded and a fresh key is retrieved from the key server. If the connection to the key server is not available error messages are written to the Windows Event Log, but encryption processes will continue using the locally cached key, once the 24 hour period expires, network connectivity will need to be restored for a fresh key to be retrieved and operations restored. With key caching database encryption, performance is much better.

The architecture of the Alliance Key Manager EKM provider implements other core features needed to help protect your database. These include:

  • Separation of Duties between Key Administrators and Database Administrators
  • Dual Control for key management operations
  • Built-in logging to the Windows Event Manager
  • High availability failover to one or more secondary key servers
  • Automatic recovery of failed EKM Provider services
  • Security of credentials through Windows Certificate Store
  • Easy key rollover using native SQL Server commands

Key caching is important for performance, but this is just one part of an overall key management strategy for Microsoft SQL Server.

As customers move to virtualized and cloud environments, Alliance Key Manager and the Key Connection for SQL Server EKM Provider software will move with you. In addition to traditional IT data centers, all Townsend Security encryption and key management solutions run in VMware (vSphere, ESXi, etc.), Microsoft Azure, Amazon Web Services, and in any cloud service provider vCloud environment.

Encryption and Key Management for Microsoft SQL Server

Topics: Alliance Key Manager, SQL Server

Encryption & Key Management in Microsoft SQL Server

Posted by Luke Probasco on Aug 21, 2015 9:27:00 AM

NIck Trenc - CoalfireThis is a guest blog by Nick Trenc, CISSP, QSA, PA-QSA, VCP.  Nick is an IT Security Architect at Coalfire Labs.


In any environment where potentially sensitive data is stored using Microsoft’s SQL Server, one of the key issues is how to best protect that data. Microsoft SQL Server does offer several security controls natively, but almost all of them require some sort of extensive configuration and management in order to be done according to security best practices. Additionally, SQL Server’s own security controls do face some shortcomings.

VMware Encryption Key Management PCI If using SQL Server’s own encryption tools, database encryption keys are stored right next to the data they are used to protect. This makes it easier for would be malicious users to capture both the protected data and the keys used to protect that data.

This is where Townsend Security’s Alliance Key Manager (AKM) comes in to play. Utilizing the built-in SQL support, IT administrators can generate, store, and manage keys within AKM away from the data those keys are used to protect. This enables separation of duties and dual control which are both best practices and requirements of several compliance frameworks.

Alliance Key Manager utilizes the Extensible Key Management (EKM) functionality of SQL Server (Enterprise Edition 2008 and newer) to centrally manage encryption keys. In addition, AKM also includes native support for SQL Server Transparent Data Encryption (TDE) which can be used to encrypt all of the tables within SQL Server. Finally, AKM includes support for SQL Server Cell Level Encryption (sometimes called Column Level Encryption), integrates directly with the Windows Certificate store, and includes features for key caching and mirroring for high availability.

For more information on using AKM to specifically meet PCI DSS compliance within a virtual environment (but also applicable to most environments), please see the VMware Product Applicability Guide for PCI 3.0 published by Coalfire Systems with collaboration with Townsend Security and VMware.

VMware Encryption Key Management PCI DSS

Topics: Encryption Key Management, SQL Server

Securing SQL Server in the Cloud

Posted by Liz Townsend on Dec 19, 2014 10:21:00 AM

Organizations running SQL Server Enterprise edition gain the added benefit of SQL Server transparent data encryption (TDE) and extensible key management (EKM). The encryption capabilities of Enterprise edition enable users to easily encrypt data at the column level of a database, and EKM allows users to store encryption keys using a third-party encryption key management solution. These streamlined capabilities of SQL Server Enterprise Edition have made SQL Server one of the easiest databases to encrypt, and therefore it’s popularity hasn’t waned. SQL Server Resource Kit on Encryption & Key Management

One of the biggest issues facing SQL Server users today is maintaining security as users move their SQL databases to the cloud. While Microsoft Azure remains a popular cloud service provider (CSP) for SQL users, Amazon Web Services (AWS) and VMware are also common amongst organizations moving to the cloud, especially those migrating a multi-platform environment. Each of these top-tier CSPs offer security solutions to help you protect your cloud environment; however, when considering security in the cloud there are two important things to remember: The security offered by your CSP won’t provide you with a complete security solution, and the security solutions you bring to protect your data in the cloud can fail if not implemented correctly.

Don’t rely on the cloud for complete security!

Your CSP should provide your business with some security, but their solutions are likely limited. Most CSPs will offer firewall protection, for example. Top-tier CSPs have also undergone some certifications such as Payment Card Industry (PCI) and FedRAMP compliance. It is important to remember, however, that relying on firewalls alone is not enough to prevent intruders, and cloud certifications never mean that your company will automatically meet these compliance regulations as well. A comprehensive data security plan is required for any business operating in the cloud, and this typically requires using third-party security solutions to ensure your business meets compliance and is adequately protecting data.

Remember these two things when protecting data in the cloud:

  • The security solutions offered by your cloud vendor are rarely enough to prevent a data breach.
  • Just because your cloud service provider is compliant, doesn’t mean you are.

Storing data in SQL Server in the cloud presents new security challenges. Hackers or malicious users can gain access to sensitive data easily through common hacks. Easy hacking of SQL Server is a result from:

  • Incorrect configuration of cloud provider’s firewall
  • Attacks through weaknesses that could have been addressed by updating and patching SQL Server
  • Missing or weak passwords
  • social engineering and account hacking
  • Lax administrative access

When it comes to securing SQL Server in the cloud, you should also always consult your legal and auditing team (or consultants) before assuming that your data is safe and you are compliant with any industry security regulations. On a general level, it’s important to include these security measures in your holistic security plan:

  • Intrusion prevention
  • System logging and monitoring
  • Encryption & key management
  • SSH in place of passwords
  • Limited access to sensitive data
  • Separation of duties and split knowledge when accessing encryption keys and sensitive data.

It’s important to remember that your business continuity relies on your own security plan. Regardless of the environment, when your organization experience a data breach, ultimately the responsibility is yours. Your customers, as well as your employees, rely on you to protect their data, and if you fail to do so, the consequences may include loss of customer loyalty and a severely damaged brand. The ultimate way to prevent access to sensitive data is using encryption and encryption key management.

To learn more about how Microsoft SQL Server Enterprise Edition can easily be secured in the cloud, download:

Resource Kit: Encrypting Data on SQL Server

 

Topics: Encryption, Encryption Key Management, Resource Kit, SQL Server, Cloud Security

VMware and SQL Server Encryption

Posted by Michelle Larson on Dec 12, 2014 9:38:00 AM

Questions and Answers on Encryption and Key Management Projects

VMware® is hands-down the virtualization choice of large and small organizations, and it is easy to see why. Not only is it a highly reliable and scalable platform, VMware also provides a complete set of tools you need to deploy, manage, monitor, and protect virtual machines.

Earlier this month, Paul Taylor with Security Insider - Podcast Edition spoke with our founder, Patrick Townsend about encrypting data on Microsoft SQL Server in VMware environments, steps to encrypting data on SQL Server (with and without TDE), as well as talk about Townsend Security’s Alliance Key Manager for VMware. Here are a few highlights (download the podcast for the whole conversation): Podcast: VMware and SQL Server Encryption

Paul Taylor: We’ve talked about the Townsend Security encryption and key management solutions for VMware. Today let’s put the focus on Microsoft SQL Server and encryption in the VMware customer environment. Can you give us an overview of how VMware customers can protect data in SQL Server databases?

Patrick Townsend: Just to recap, we really need two things to get encryption right: A key management solution to protect the critical encryption keys, and an encryption solution for the SQL Server database. And they have to talk to each other.

For the first part, our Alliance Key Manager for VMware solution provides a fully functional, enterprise key management solution that protects SQL Server databases as well as other databases and other operating systems.

For encrypting SQL Server, our Alliance Key Manager solution comes with a full Microsoft SQL Server Extensible Key Management Provider. We call this Key Connection for SQL Server and it is one of the modules that our key management customers receive without paying additional license fees. Key Connection for SQL Server provides the encryption and integration with our key server to provide a complete, end-to-end solution for encrypting data in the SQL Server database.

Paul Taylor: Can you talk a little about how Microsoft enables encryption in SQL Server?

Patrick Townsend: If you are running SQL Server Enterprise Edition or higher, you have access to Microsoft’s automatic, full database encryption facility called Transparent Data Encryption, or TDE. You also have access to Microsoft’s automatic, column level encryption facility which Microsoft calls Cell Level Encryption. Both of these options, TDE and Cell Level Encryption,  are implemented without any programming work at all. And both are fully supported by Alliance Key Manager and the Key Connection for SQL Server software from Townsend Security.

Paul Taylor: What about Microsoft customers who aren’t using the Enterprise Edition of SQL Server? Can they encrypt their data with the Townsend Security solution?

Patrick Townsend:  With SQL Server Standard and Web Editions we provide two paths to encrypt data. The first is to use SQL Views and Triggers along with our .NET DLL to provide automatic encryption without any changes to applications. And the second path is to modify your C# or Java applications to use our .NET DLL to perform encryption at the application level.

Both approaches leverage our Microsoft .NET DLLs to perform encryption with integrated key management. Both are very simple to implement. And there are no additional license fees to deploy and use our Microsoft .NET DLLs to accomplish this.

Paul Taylor: So, walk me through the steps for encrypting data in my SQL Server Enterprise Edition database. How difficult is it?

Patrick Townsend: Encrypting data in Enterprise SQL Server is really very easy. The first step is to install our Alliance Key Manager for VMware solution. It launches like any other virtual machine using the normal VMware applications and you can have a key management solution up and running very quickly.

The second step is to install the Key Connection for SQL Server application on the virtual machine running SQL Server in Windows. This is a normal install process with an MSI file. You answer some questions, install a certificate and private key in the Windows Certificate Store, and run a handful of commands to start SQL Server TDE encryption or Cell Level Encryption. You also restart the log file to be sure that it is encrypted as well. That’s about it.

Of course, you will want to follow the instructions on how to set up a high availability key server, and point your Key Connection for SQL Server configuration to it as failover. That is a normal configuration process and also very easy to do. We find that VMware customers can deploy SQL Server encryption very quickly.

Paul and Patrick also cover which versions of SQL Server are supported, the availability of Alliance Key Manager in other platforms (hint: it’s quite versatile), and our 30-day evaluation program (you can do a full proof-of-concept in your own environment at no charge). Be sure to download the podcast to hear the rest of their conversation:

Podcast: VMware and SQL Server Encryption

Topics: Data Security, Encryption, Security Insider Podcast, Encryption Key Management, VMware, SQL Server


Subscribe to Email Updates

Posts by Topic

see all