SQL Server Magazine

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.

End of Article