Russ Thomas – SQL Judo

The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded

TSQL Tue 63: Extensible Key Management

T-SQL TuesdayIt’s TSQL Tuesday!  Edition 63 is being hosted by Kenneth Fisher (@sqlstudent144).  The topic this month is security, and what a globally relevant topic it is.

Target, Home Depot, Blue Cross – chances are by naming those three entities I’ve invoked a mental chord in all of you.  Each of these organizations have suffered large scale data breaches in recent history making headlines and likely some form of pending legislation and/or enhanced regulation for us all.  As these types of large scale breaches continue to occur, security will continue to claim a larger and larger portion of your company’s tech budget, resources, job reqs, and much of your own time and knowledge as a database professional.

If you are a career database administrator working in an environment that requires encryption it’s probably time to start familiarizing yourself with two additional acronyms.  HSM and EKM.

nuke_keyEKM stands for Extensible Key Management.  This is a feature of SQL Server Enterprise Edition that allows cryptographic keys to be managed outside of the SQL Server instance being secured.  This is done through a common API interface provided by Microsoft and enabled via sp_configure.

-- Enable advanced options.
sp_configure 'show advanced options', 1 ;
-- Enable EKM provider
sp_configure 'EKM provider enabled', 1 ;

HSM stands for Hardware Security Module.  Simply put, this is a hardware (or server) based appliance that can manage your keys and interact directly with the EKM capabilities of your SQL Server instance via the API for encryption duties.  This is going to be a third party product and the features, benefits, and costs may vary wildly.  HSMs (or EKM providers) can do all sorts of tricky things such as automated key rotation and logging/auditing of all decrypt attempts.  HSMs come from industry names such as SafeNet or “ma and pa” shops who simply know what API stands for.

Why would an organization want to deploy an HSM or an EKM infrastructure?  If you are a regulated industry (PCI, HIPPA, etc.) it may be that managing the encryption keys for your database server on the same server being protected is a violation.  If you are just extremely security conscious it might be because you understand that managing keys and certificates on a separate server makes a breach just a little bit less likely.  Not necessarily because those keys and certs aren’t in the same place mind you, more because having them in the same place increases the likely hood that a single actor can act maliciously.

To me, that is the real strength of an HSM system.  Implemented correctly an HSM pulls the key management responsibilities away from the database administrator and places them within the hands of a dedicated security asset.  This only works if you actually manage it that way.  Like “accidentally” launching a thermo-nuclear warhead, decrypting a large TDE backup in order to make a copy for yourself, is much less likely when it requires a consolidated effort on the part of two individual key holders instead of just one.  Give both keys to one individual and what was the point?

Just make sure that your security team has a 24 hour a day on call rotation and matching HA and DR policies on the HSM.  Finding out your 5-9 synchronously mirrored database failover partner can’t come online because the HSM holding the encryption keys is un-pingable can really stink.  Yeah, been there.  If you have an instance failing to come online in an EKM/HSM setup, check that server first, service and service account second.

Image Credits:  Nuclear Launch Button

2 comments on “TSQL Tue 63: Extensible Key Management

  1. Pingback: TSQL Tuesday #63 – How do you manage security: Rollup | SQL Studies

  2. Pingback: TSQL Tuesday #63 – How do you manage security: Rollup - SQL Server - SQL Server - Toad World

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s


This entry was posted on February 10, 2015 by in T-Sql Tuesday.
%d bloggers like this: