| Executive Summary:
The Extensible Key Management (EKM) feature in Microsoft SQL Server 2008 (Enterprise, Developer, and Evaluation Editions only) lets third-party EKM and hardware security module (HSM) vendors register their devices in SQL Server. This capability makes it possible for DBAs to use third-party EKM products along with SQL Server’s built-in encryption. Learn how EKM works and the steps for enabling and disabling HSM providers in SQL Server 2008.
|
Encryption is an important tool for protecting
databases and log files from unauthorized access
and hacking. SQL Server built-in data encryption
lets you encrypt files and store encryption keys
within SQL Server. However, SQL Server 2005
doesn’t allow the use of third-party encryption
keys or key management applications in a native
SQL Server 2005 encryption environment. Thus,
companies using a third-party encryption product
to secure data in other applications can’t use that
product to encrypt SQL Server data or manage
SQL Server encryption keys.
A new feature in SQL Server 2008, Extensible
Key Management (EKM), remedies this shortcoming
by enabling encryption keys to be stored
outside of the database in special hardware (e.g.,
smart card, USB device) or software modules
called Hardware Security Modules (HSMs). In
this introductory look at EKM, I’ll explain how
the feature works, and some new metadata views
and functions that you can use to obtain information
about EKM usage in SQL Server.
EKM and HSM
EKM, which is available in the Enterprise, Developer,
and Evaluation editions of SQL Server 2008,
makes it possible for encryption keys to reside outside
of the database in HSMs and not with the encrypted
data. Storing encryption keys in an HSM
protects them from database owners and other
higher-level database users who don’t have access
to the HSM used to store the encryption keys.
Only those end-users who have the HSM device
available during the encryption and decryption of
the data can use the keys to encrypt new data or
view already encrypted data. (Note that if an HSM
device isn’t used, members of the sysadmin group
have access to the encryption keys.) To enable users to use third-party
HSMs, SQL Server
2008’s EKM
lets third-party
vendors register
their EKM/HSM
modules in SQL
Server 2008.
Enabling and Disabling
EKM
To use EKM in your database, you must first use
the sp_configure system stored procedure to enable
the SQL Server instance to allow EKM, as callout
A in Listing 1 shows. After you enable EKM,
you need to create an EKM provider (or more, if
you’re using more than one HSM) by using the new
CREATE CRYPTOGRAPHIC PROVIDER Data
Definition Language (DDL) statement, as callout B
shows. Once you’ve enabled EKM and created the
provider, you can use this provider either for encryption
keys, as in Listing 2, or for credentials if you
want to use the EKM provider module to secure
logins, as in Listing 3.
At some point, you might need to disable a provider
and enable a new provider in your database—
for example, if your company chooses a different
third-party encryption-key provider as its standard.
SQL Server 2008 lets you disable providers
while keeping them in the database until
you’re ready to delete the provider object
permanently. This gives you ample opportunity to
find all objects that use the old provider and change
them to use the new provider, while keeping the old
provider on hand in case of error. To disable a provider,
you use the ALTER CRYPTOGRAPHIC
PROVIDER statement, as Listing 4 shows.
Auditing EKM
Usage
Database administrators and developers who have
implemented EKM will need to understand the
metadata views used to store EKM providers and
information. SQL Server 2008 provides a series of
new catalog views, dynamic management views, and
dynamic management functions that DBAs can use
to audit EKM providers and the usage of EKM.
The examples in Listing 5 show how you can use
these various views and functions to obtain information
such as a list of providers currently in use in
a SQL Server instance, a list of credentials that use
EKM, a list of keys for each provider ID in your
database, provider properties, and the encryption
algorithms used for each provider ID.
A More Secure SQL Server
As we’ve seen in this quick tour of EKM, SQL
Server’s encryption capabilities have taken a big
step forward in the 2008 release. Database administrators
who hesitated to use encryption because
of concerns that the encryption keys were being
stored with the encrypted data now have a new tool in SQL Server 2008 which they can use to address
these concerns. This article will help you get
started using EKM; to continue your learning about
EKM, see the SQL Server 2008 Books Online article
“Understanding Extensible Key Management
(EKM)” at msdn2.microsoft.com/en-us/library/bb895340(SQL.100).aspx.
