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

Splunk, Alliance LogAgent, and the LEEF data format

Posted by Patrick Townsend on Apr 18, 2017 7:09:08 AM

We have a lot of Enterprise customers deploying our Alliance LogAgent solution for the IBM i server to send security events to Splunk. On occasion a customer will deploy Alliance LogAgent and send data in the Log Event Extended Format (LEEF) to Splunk. The LEEF format is the preferred log data format for the IBM Security QRadar SIEM, so I’ve always found this a bit puzzling.

IBM i Security: Event Logging & Active MonitoringThe light finally came on for me this week.

Security event information in syslog format (see RFC 3164) is largely unstructured data. And unstructured data is hard for SIEM solutions to understand. Here is an example from an Apache web server log:

[Wed Oct 11 14:32:52 2000] [error] [client] client denied by server configuration: /export/home/live/ap/htdocs/test

An SIEM administrator would have to do a fair amount of work configuring the SIEM to properly understand the importance of this message and take the proper action. If only the data was in some type of normalized format!

It turns out that the IBM Security QRadar LEEF format normalizes the system log information. A message like the above might look something like this in LEEF format:

date=20001011 time=143252 ipAddress= violation=client denied severity=5 path=/export/home/live/ap/htdocs/test

With field definitions like “date” and “time” the Splunk SIEM can easily digest the message and the Splunk query tools work great. It is easy to create reports and dashboards with this type of normalized data. The LEEF format is really good about this and Alliance LogAgent supports the LEEF definition.

What most Splunk administrators do not realize is that our Alliance LogAgent solution normalizes all IBM i security events in this type of normalized fashion. That is, this format is the default data format for security events. This is already what Alliance LogAgent does for IBM i security events!

When we started the development of Alliance LogAgent more than 10 years ago we understood at the outset that system log data would be hard for a SIEM to parse. So from the first release of our solution we provided data in this normalized format. Whether you are using Splunk, LogRhythm, Alert Logic, or any other SIEM we make it really easy for the SIEM to digest and act on the information. And forensic, query, and dashboards are easy to create.

So, Splunk users - listen up! The default system log format in Alliance LogAgent is exactly what you need to make Splunk work really well. You can use the LEEF format if you really want to, but you have all of the benefits of normalized data with the default format.

Here at Townsend Security we are vendor neutral when it comes to SIEM solutions. Our customers deploy a wide range of solutions including Splunk, IBM QRadar, LogRhythm, Alert Logic, SolarWinds, McAfee, and lots more. And they can move from one SIEM to another without changing their Alliance LogAgent configurations. We believe that actively monitoring system logs in real time is one of the most important security steps you can take. Early detection of a problem is so much better than trying to remediate a breach after the fact.



Topics: Alliance LogAgent, Splunk

Microsoft SQL Server Encryption - Key Management Security Best Practices

Posted by Patrick Townsend on Apr 12, 2017 7:48:32 AM

This is part 7 in a series focusing on critical aspects of SQL Server encryption. In the previous part of this series we looked at encryption key management business recovery topics. In this part we look at encryption key management security 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 Payment Card Industry Data Security Standard (PCI-DSS) and others.

Separating Encryption Keys from the Data They Protect

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

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

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 and 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 and 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 insure 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

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.

Auditing and 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.


Encryption and Key Management for Microsoft SQL Server

Topics: SQL Server, SQL Server encryption

Microsoft SQL Server Encryption and Key Management Business Continuity

Posted by Patrick Townsend on Apr 4, 2017 8:33:26 AM

This is part 6 in a series focusing on critical aspects of SQL Server encryption. 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

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

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

Another 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 backup 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.

In summary, key management systems need all of the major business continuity components that you would expect in mission-critical business applications.


White P

Topics: SQL Server, SQL Server encryption

Trying to Outfox the Other - A Brief Look at Cryptography and Cryptanalysis

Posted by Ken Mafli on Mar 31, 2017 10:35:55 AM

 A few months ago I wrote a definitive guide to Cryptographic Key Management. In it I wrote a section: A Brief History - the Need for Encryption Key Management. I wanted to expand upon the Classical Era of cryptography a bit because the story of data security goes back for millennia, and the twists and turns of this story can be felt even today.


eBook: Definitive Guide to Encryption Key ManagementThere has been a competition playing out through the centuries all the way from the highest corridors of power down to the shadiest back alleys. It is a struggle of those with a secret and those who want to uncover it. It is the story of cryptography and cryptanalysis.

As with every competition, each side is constantly trying to outfox the other. Peter Baofu described the competition this way, it is “the never ending cycle of replacing old broken designs” of cryptography and “new cryptanalytic techniques invented to crack the improved schemes.” In fact, “in order to create secure cryptography, you have to design against [all] possible cryptanalysis.” This means that both sides are in a never-ending arms race.

In his book, “The Future of Post-Human Mass Media,” Peter Baofu describes two main types of cryptanalysis: Classical and Modern Cryptanalysis. Let’s take a look at the Classical Period to see how this cat and mouse game has played out through the centuries:

The Classical Cat-and-Mouse Game

Classical Cryptography

One of the earliest forms of “secret writing” is the Substitution Cipher where each letter of the message is systematically replaced by another set of predetermined letters. In it’s most famous form, the Caesar Cipher, used by Julius Caesar himself (1st century, B.C.E):

“each letter in the plaintext is 'shifted' a certain number of places down the alphabet. For example, with a shift of 1, A would be replaced by B, B would become C, and so on.”

Another technique was Steganography, which literally means: “covered writing,” is the art of concealing a message in plain sight. Mehdi Khosrowpour recounts one of the first recorded instances (in the 5th century, B.C.E):

“Demaratus, a Greek who lived in Persia, smuggled a secret message to Sparta under the cover of wax.” It “ was to warn Sparta that Xerxes, the King of Persia, was planning an invasion ... by using his great naval fleet. He knew it would be very difficult to send the message to Sparta without it being intercepted. Hence, he came up with the idea of using a wax tablet to hide the secret message. In order to hide the secret message, he removed all the wax from the tablet, leaving only the wood underneath. He then wrote the secret message into the wood and recovered the tablet with the wax.”

Classical Cryptanalytic Response

While steganography is only hard to crack if you don’t uncover the message; substitution ciphers were meant to remain a secret even if the message fell into enemy hands. It remained a fairly reliable means of securing messages, so long as the cipher was not revealed.

All that changed with the first recorded technique of cryptanalysis: Frequency Analysis. This technique “can be traced back to the 9th-century [C.E.], when the Arabian polymath Abu Yusef Yaqub ibn Ishaq Al-Kindi (also known as ‘Alkindus’ in Europe), proposed in A Manuscript on Deciphering Cryptographic Messages.” It comes from the observation that certain letters appear more often than others in a given language (the letter “E,” for example, occurs most often in English). There also also common letter pairings (like “TH” in English).

So, in the case of the Caesar Cipher where the plaintext message is :

meet me at the theater

If each letter is shifted one letter in alphabet, it becomes:

nffu nf bu uif uifbufs

Frequency analysis would note that the most common letter in the ciphertext is “f” (which would suggest it is an “e”) and only letter pairing is “ui” (which would suggest the “u” is “t” and the “i” is “h”). If we replace these portions of the ciphertext we reveal:

_eet _e _t the the_te_

With these two facts of frequency analysis alone we have more than half the message deciphered. With a few logical leaps we could decipher the remaining the five letters. The simple substitution cipher was rendered useless.

The Classical Cryptography Counterattack


Over the centuries other ciphers were introduced like the Polyalphabetic Substitution Cipher where a repeating, offset key is used to encrypt the plaintext (see picture, courtesy of the Library of Congress). First perfected by Johannes Trithemius in 1518 (although other variants existed beforehand), the person encoding the message would switch alphabets for each letter of the message.

So, “meet me” would now become: “lcbp gy,” a ciphertext that simple frequency analysis could not break since most of the letter and pairing statistics of a given language are not easily recognized.

Although, in time, this type of cryptography was broken by the likes of Charles Babbage using modular arithmetic, the existence of his cryptanalytic techniques remained a military secret for some years.

Final Thoughts

Fascinatingly, it was the use of math to break a cipher that led to our current arms race in data security. The use of math and algorithms to break cryptography means you need longer keys to encrypt the data and prevent a brute force attack; which, in turn, means you need faster computers to break the encryption; which, in turn, means you need longer keys; etc.

Unlike today, however, it took centuries to break a cipher back then. Now, it is just decades. From the Hebern Electric Super Code Cipher Machine in the 1920s, to the Enigma Machine of the 1930s and 40s, to the Data Encryption Standard (DES) of the 1970s and 80s, each seemed invincible until enhanced cryptanalytic techniques or greater computing power toppled it. Our current cryptography is reliable and secure, but quantum computers loom on the near horizon and their non-binary logic could brute force attack our current public key cryptography and make them insecure.

And so the arms race continues. Fortunately, NIST has already forecasted this threat and called for replacements to our current standards, well before it is a crisis.  

eBook: Definitive Guide to Encryption Key Management

Topics: Encryption

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

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

Encryption and Key Management for Microsoft SQL Server

Topics: SQL Server, SQL Server encryption

Case Study: Citizens Security Life Insurance

Posted by Luke Probasco on Mar 13, 2017 10:54:24 AM

CSLI-Logo.pngCompliance Made Easy - Protecting Private Information with Alliance AES/400 Encryption for IBM i and Alliance Key Manager for VMware

“Townsend Security was extremely easy to work with - from the sales process to deploying our proof of concept to post-sales support.”

- Adam Bell, Senior Director of IT

Citizens Security Life Insurance

MCitizens Security Life Insurance Company is a life and health insurance carrier. The company offers group benefits including dental and vision coverage, and individual ancillary insurance products. The company was founded in 1965 and is headquartered in Louisville, Kentucky.

The Challenge: Protect ePHI & PII on the IBM i

In order to meet growing partner requirements and pass a data security audit for protecting electronic Protected Health Information (ePHI) and Personally Identifiable Information (PII), Citizens Security Life Insurance (CSLI) needed to deploy an encryption solution on the IBM i. The solution needed to be easy to implement with excellent performance.

While FIELDPROC on the IBM i makes it very easy to encrypt data without application changes, CSLI also understood that for encrypted data to truly be secure, they would need to store and manage encryption keys with an external key manager.

By using a VMware-based encryption key manager, the company could meet encryption and key management best practices for separating encryption keys from the data they protect.

The Solutions

Alliance AES/400 Encryption

“The performance we are seeing with Alliance AES/400 encryption is excellent,” said Adam Bell, Senior Director of IT, Citizens Security Life Insurance. “The solution was easy to integrate and completely met our expectations.”

Alliance AES/400 FIELDPROC encryption is NIST-compliant and optimized for performance. The solution is up to 100x faster than equivalent IBM APIs on the IBM i platform.

With Alliance AES/400, businesses can encrypt and decrypt fields that store data such as credit card numbers, social security numbers, account numbers, ePHI, and other PII instantly without application changes.

Alliance Key Manager for VMware

Alliance Key Manager for VMWare was very easy to implement and the resources Townsend Security provided made deployment a smooth process,” continued Bell. By deploying Alliance Key Manager for VMware, CSLI was able to meet their business needs with a solution that could not only deploy quickly, but was also easy to set up and configure.

Alliance Key Manager for VMware leverages the same FIPS 140-2 compliant technology found in Townsend Security’s hardware security module (HSM) and in use by over 3,000 customers. The solution brings a proven and mature encryption key management solution to VMware environments, with a lower total cost of ownership. Additionally, the key manager has been validated to meet PCI DSS in VMware environments.

Integration with the IBM i Platform

An encryption strategy is only as good as the key management strategy, and it can be difficult to get key management right. For companies doing encryption, the most common cause of an audit failure is an improper implementation of key management. The seamless integration between Alliance AES/400 and the external Alliance Key Manager for VMware allowed CSLI to pass their data security audit with flying colors.

“The relationship we developed with Townsend Security enabled us to have a painless sales and support process, and in turn, enabled us to easily pass our data security audit,” finished Bell.

Meeting HIPAA and protecting ePHI with encryption and key management.


Topics: Alliance Key Manager, Alliance AES/400, Case Study

A Brief History of KMIP

Posted by Ken Mafli on Mar 6, 2017 1:31:39 PM

KMIP Logo.pngKey Management Interoperability Protocol (KMIP) is quickly becoming the industry standard for ensuring your product or software can communicate seamlessly with cryptographic key managers.  In fact, a study by the Ponemon Institute in 2013 reported on the state of encryption trends and found that “more than half of those surveyed said that the KMIP standard was important in cloud encryption compared with 42% last year.”  This is surprising since KMIP v1.0 was first ratified three short years earlier on October 1st, 2010!

How Did it All Start?

eBook: Definitive Guide to Encryption Key ManagementThe first meeting held to start discussing the new set of standards was on April, 24th 2009 in San Francisco in conjunction with the RSA convention that year.  In attendance were representatives from RSA, HP, IBM, Thales, Brocade, and NetApp. Their initial scope was to “develop specifications for the interoperability of key management services with key management clients. The specifications will address anticipated customer requirements for key lifecycle management”

But why was KMIP necessary to begin with?  The short answer: more and more organizations were deploying encryption in multiple environments.  But with encryption comes the need to properly manage the encryption keys. With encryption increasing across multiple enterprise applications it became harder to easily manage the keys from the different enterprise cryptographic applications.  Better standards were needed to create uniform interfaces for the centralized encryption key manager.

Companies soon saw the benefits of adopting KMIP.  Both large and small organizations need their key management to work every time and need it to scale as their organization grows.  And while other work was done to address this issue, like OASIS EKMI, IEEE P1619.3,  and IETF Keyprov KMIP was designed to have a broader scope than it’s predecessors and give more comprehensive standards for the industry.

How Was KMIP Initially Received?

In 2010, KMIP debuted at RSA.  HP, IBM, and others demonstrated that their client programs using the KMIP version 1.0 protocol could “communicate securely with key management servers. The clients and servers [demonstrated] essential use cases such as generating cryptographic keys, locating existing keys, and retrieving, registering, and deleting keys.”

In 2011 at the RSA Conference major players like IBM, RSA, and HP demonstrated KMIP 1.0 compatibility with their client programs.  And again in 2012 and in 2013 even more companies like Thales, NetApp, and Townsend Security demonstrated KMIP compliance.  With all these prominent players becoming KMIP compatible, it was a major signal to the industry that KMIP was rapidly becoming the industry standard for interoperable communications for key managers.

How is KMIP Thought of Now?

Fast forward to 2014.  The The Storage Networking Industry Association (SNIA) announced a testing program for KMIP conformance for its members.  In their words, “By introducing the KMIP Test Program for the industry, we’re helping to encourage not only the adoption of enterprise–class key management, but a means for vendors to test for conformance and provide an assurance of interoperability and a layer of trust to their customers.”

At  OASIS’ Interoperability Showcase at RSA 2016 16 companies, including Townsend Security, demonstrated KMIP compatibility.  And with the likes of VMware, Oracle, Quantum, and many others  demonstrating KMIP compatibility, KMIP has become a dominant standard in key management interoperability.

Final Thoughts

Encryption is your last, best defense for data at rest.  But encryption is only as good as your key management.  If the key is exposed to hackers, the data is lost as well.  This is why key management standards like KMIP have already attracted considerable interest, and will continue to do so.  The ability to have a variety of vendor applications, platforms, and databases all able to communicate with a centralized key manager enhances the data security posture of the enterprise.  And this is what organizations should strive to achieve.

OASIS built the standard to address a broader scope of issues than what older industry standards addressed. But KMIP still is actively being matured by OASIS (we are on version 1.3) and we should expect to see further enhancements and revisions to the standard as well as broader industry adoption.  This should give us confidence that KMIP as a well-accepted, road-tested standard will continue to grow in industry popularity in years to come.

eBook: Definitive Guide to Encryption Key Management

Topics: Encryption Key Management

Hillary's email data breach taught us all the wrong lessons

Posted by Ken Mafli on Feb 28, 2017 9:11:00 AM

In an unprecedented October surprise, Wikileaks dumped thousands of emails onto the internet from the Democratic National Committee (DNC), most of them concerning Hillary Clinton’s presidential campaign.  Later, in defending this move, Wikileaks founder Julian Assange, in an interview with FOX News, “said a 14-year-old could have hacked into the emails of Hillary Clinton's campaign chairman,” reported the Daily Mail.  Assange later revealed in the interview that John Podesta’s, Hillary’s campaign chairman, password was 'password.'  Politifact has gone on to challenge that assertion, saying that “Podesta was using a Gmail account, and Google doesn’t allow users to make their passwords ‘password.’”

Whatever John Podesta’s password was, it has sparked a good deal of renewed interest in good password management.  And far be it from me to downplay this crucial bit of data security.  We still have a long way to go.  In fact, SplashData just completed their survey of over 5 million people’s passwords and found that over 10% of people still use the most commonly guessable passwords like:

  • password
  • 123456
  • qwerty
  • passw0rd
  • Password1
  • zaq1zaq1

If you use any of these, stop it. Now.

But if that is all that we learn from the hack and subsequent data breach, we have missed the lesson.  As far back as June of 2016, it was widely reported, by the likes of Brian Krebs and Jeremy Kirk, that the DNC was vulnerable to attacks do to systemic weaknesses in cybersecurity.  In fact, in Jeremy Kirk’s article, it was noted that a press assistant emailed everyone a new password after a recent breach (a strong password at that: 'HHQTevgHQ@z&8b6').  The irony is, some of the email accounts had been compromised.  The hackers needed only to open the email and use the new password.

Strong passwords are not enough to rebuff the efforts of hackers to gain entry and to render the data useless in case of a breach.  We need proven security measures in order to keep the data safe.  

The data security measures below reflect specific things you can do to secure your data-at-rest in general. While there are more more specific measures you can take for email servers, it is important to remember that organizations have sensitive data everywhere, not just in emails.  That being said, since even seemingly benign emails at the DNC can blow up into political controversy, they probably need to follow these along with more email specific recommendations.  Follow along to find some of the best methods your organization should be using today to better secure your data security posture.

Multi Factor Authorization

2FA.pngAs we have already mentioned, usernames and passwords, by themselves, are not enough to authenticate users.  Truly strong passwords are hard to manage and remember.  And once a system is compromised, login credentials can be scraped with keyloggers, malware, or other such attacks.

You need an external verification process.  You need multi factor authentication (MFA). MFA has traditionally relied on verifying you by two of three ways:

  • Something that you know (i.e.: username, password, challenge questions/responses, one-time-use code, etc.)
  • Something that you have (i.e.: token, RFID cards or key fobs,  mobile phones, etc.)
  • Something that you are (biometrics)

Each of these methods have their advantages and drawbacks. For example:

  • Challenge Questions:
    • PRO: do not require any physical equipment on the user side
    • CON: do rely on the user’s memory, which can be fuzzy when it comes to precisely writing the correct response
    • CON: are vulnerable to deduction through inspection of social media accounts, etc.
    • CON: are “something you know” and so fall into the same category as login credentials, thereby not taking advantage of any other kind of authentication
  • Physical Equipment: (like RFID cards and tokens)
    • PRO: do not rely on a person’s memory
    • CON: can be stolen or lost
    • CON: require active device management from an administrator

One method of authentication that is gaining ground because of its ease of use is authentication that relies on OAuth (an open standard for authorization).  It does not rely on physical fobs (which can be lost) or an SMS text (which can be intercepted).  It, instead, relies on cryptographic code that generates a time specific one-time-use codes based on the user’s secret key and the time. Since the code operates simultaneously (and separately) on the user’s device (typically a mobile phone) and on an internal server, with no need for an internet connection; it greatly reduces downtime because of internet issues and hackers intercepting the one-time-use code.


lock.pngStrong, Advanced Encryption Standard (AES) encryption as put forward by NIST should be used to encrypt all sensitive customer and company data.  In 2001 NIST formally adopted the AES encryption algorithm.  Since then, it has been proven countless times to render the data useless in the event of a breach.  In fact, it would take the fastest supercomputer 375 x 1050 years to brute force AES encryption by running through all permutations of an AES 256-bit encryption key.  In comparison, the Sun will reach its Red Giant stage in 54 x 108 years, engulfing Mercury, Venus, and possibly Earth.  In other words, the Earth will be incinerated by the then rapidly expanding Sun before a hacker could effectively crack AES encryption through brute force.

The good news, AES encryption comes standard in most database’s native encryption libraries.  Along with those free versions, there are a number of commercial products that rely on AES encryption available.  So finding a way to secure your data with AES encryption will be fairly easy.  That being said, it is important to understand the development time and performance hits each solution takes. Native encryption libraries are generally free but take a bit of development time.  Commercial solutions take less time to deploy but many times are file/folder level encryption products and have performance hits because they take a longer to encrypt/decrypt than column level encryption products.

Centralized Encryption Key Management

key.pngAs we mentioned, AES encryption is extremely difficult to brute force attack.  It’s strength lies in its ability to encrypt the data with a very long key (typically 256-bit). But it’s strength is also its weakness.  If your encryption key becomes known to a bad actor, your encrypted data becomes compromised.  That is why any encryption strategy worth its salt will include proper, centralized encryption key management.  

When defending your encryption key with full lifecycle key management, consider these things:

  • The encryption keys should be logically or physically separated from the encrypted data.  This way, if the encrypted data is compromised, they will not be able to decipher it.
  • The encryption keys should only be generated with a cryptographically secure pseudo-random number generator (CSPRNG).
  • Restrict administrator and user access to the keys to the least amount of personnel possible.
  • Create clear separation of duties to prevent improper use of the keys by database administrators.
  • Manage the full lifecycle of the keys from key creation, activation, expiration, archive, and deletion.

For a more comprehensive view of encryption key management, please view the Definitive Guide to Encryption Key Management.

Real Time Log Monitoring

Forrester, in 2013, promulgated the cybersecurity model of “Zero Trust.”  In it, they put forward the motto: “never trust, always verify.”  By this, they mean that all users should be authenticated, restricted to the least amount of data possible, and verified that they are doing the right thing through real-time monitoring.  Of which, they advocate for:  

  • Real Time Event Collection in which you collect and log all events, in real time.
  • Event Correlation in which you analyze all events and narrow in on the ones that do not conform to expected patterns.
  • Resolution Management in which you investigate all suspect behavior and either classify them as either benign or a possible threat for further investigation.

There are many Security Information Event Management (SIEM) tools available that accomplish this.  For more information, refer to Gartner’s SIEM Magic Quadrant to find the tools that fit your needs.

Final Thoughts

Defending data-at-rest is a never ending struggle of building robust defenses and continuous improvement.  But, it's not a question of if, but when, a data breach will happen.  And if the DNC data breaches taught us anything is that breaches can be embarrassing and costly.  Since  hackers are only growing more sophisticated in their techniques, it is incumbent upon us to respond in ever increasing levels of agility and sophistication of our own.

The old models of the high, guarded perimeter with complex passwords to gain entry are just not enough.  We need a higher degree of authentication, sensitive data rendered useless, and constant real-time monitoring of all traffic.  You data depends on it.

Turning a Blind Eye to Data Security eBook

Topics: Data Security

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


Encryption and Key Management for Microsoft SQL Server

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

Subscribe to Email Updates

Posts by Topic

see all