Introduction

Recently, I was tasked to setup SQL Server 2008's new features of Transparent Database Encryption (TDE) and Extensible Key Management (EKM). Transparent Database Encryption (TDE) is a new feature of SQL Server 2008. With TDE turned on for a database, SQL Server 2008 encrypts the data when writing it to disk and decrypts it when reading from disk into cache. The database backups will also be encrypted. Extensible Key Management (EKM) is another new feature that gives SQL Server the ability to store the encryption key used for TDE on hardware specifically design for key security and management. Such devices are called High Security Modules (HSM), whose vendors are referred to as EKM providers.

There is quite a bit of work in setting up these HSM devices. The device requires a number of USB keys to access to access it - similar to what you would imagine what it would be like accessing a nuclear control panel. I won't go into details on the HSM's setup, partly because I don't know everything involved, but more so that it's mostly out of the realm of SQL Server and DBA land. The one part that is SQL Server related is the EKM provider's DLL file, which is created on the server.

The EKM Provider's DLL is registered within SQL Server 2008 to allow communication between the instance and the HSM device. Registering the DLL is straight forward, and the steps are detailed in Books Online, see “EKM, TDE how to”. In addition, there are few online articles covering the setup of EKM/TDE to further your understanding. Instead, I want to address the recovery of the encryption key a database that has been encrypted using EKM/TDE and is stored on an HSM device.

Lost Key

Maybe the server died and because of it SQL Server had to be reinstalled, or the encrypted database has to be restored to another SQL Server or the key was mistakenly deleted. Whatever the cause, we are in the situation where we have to restore the key from the HSM device to decrypt the database files either backup or data files so we can bring it online. To reduce the number of steps and variables, we are going to assume the HSM device still has the key stored on it either because it was unaffected by what caused the server's failure or the HSM device has been already recovered from its own backup.

Recovery

If we are going to a new SQL Server instance, we have to be sure that the work has been done to allow communication between the HSM with the server hosting SQL Server. So, the necessary EKM Provider's DLL needs to have been created on the server. It may require working with the engineer responsible for the HSM to accomplish these steps.

To recover the key, one option would be to restore the Master database from a backup that had the EKM provider settings and the asymmetric key required to decrypt the database files and backups. But, restoring Master might not be an option. For instance, if you were to move the database to another SQL Server instance, then you might not want restore master; especially, if other application databases are on the instance. Whether this is the situation or we are concerned with the implications of restoring Master, then we will have to recreate the key from its file stored on the HSM device.

Register the EKM Provider's DLL

Once the EKM provider's DLL was created, two pieces of information are needed to register the EKM provider's DLL file EKM provider login and password information. The EKM provider login be provided be the by the engineer responsible for the HSM. We can use the following SQL as guide to accomplish this:

1. Enable EKM Provider option

 exec sp_configure'EKM provider enabled', 1
 reconfigure with override

2. Add the cryptographic provider.

CREATE CRYPTOGRAPHIC PROVIDER EKMProvider
 FROM FILE = 'c:\program files\EKMProvider\ekm\EKM.dll'

3. Create the credential to give access to the HSM device.

create credential EKMcredential
 with identity = 'EKM_User_Name',secret = 'EKM_Password'
 for cryptographic provider EKMProvider

4. Add the credential to the account creating the asymmetric from the file on the device.

alter login sa
add credential EKMcredential

Now, the EKM Provider has been enabled and the EKM Provider's DLL has been registered with SQL Server. You can list the EKM providers with the following SQL:

SELECT [provider_id]
 ,[name]
 ,[guid]
 ,[version]
 ,[dll_path]
 ,[is_enabled]
 FROM [model].[sys].[cryptographic_providers]

Or, use the following SQL to view the provider properties:

 SELECT [provider_id]
 ,[guid]
 ,[provider_version]
 ,[sqlcrypt_version]
 ,[friendly_name]
 ,[authentication_type]
 ,[symmetric_key_support]
 ,[symmetric_key_persistance]
 ,[symmetric_key_export]
 ,[symmetric_key_import]
 ,[asymmetric_key_support]
 ,[asymmetric_key_persistance]
 ,[asymmetric_key_export]
 ,[asymmetric_key_import]
 FROM [master].[sys].[dm_cryptographic_provider_properties]

Key File

Next, we have to know which file the key was stored in. If you don't know the name of the file the key was stored in on the HSM device, then things just got complicated. The only option you are left with is trying to recreate the key from each file on the HSM and test it to see if it decrypts the backup file. We may also need to know the directory or partition the file was stored. Again, this is something the engineer that configured the HSM and would have been stored in the Provider's EKM DLL.

Here's an example listing the files on the partition taken from a Putty session to the HSM device:

Storing the SQL used to create the asymmetric or symmetric key in a script would go a long way in this situation. At the very least, you should document and store the necessary information in a secure, common area for you or your team to refer to in case of an emergency.

Thumbprint

Assuming we know the filename or, at least, have a list of files our key is stored in. There's one more piece needed to recreate the key – the previous key's thumbprint. The thumbprint is a unique identifier of the key that stored on the database backups files. Fortunately, if we don't have it, this is relatively easy to find.

The first, and perhaps simpler, method to finding the thumbprint of our key would be to attempt a restore of the encrypted backup. This will return an error message with the thumbprint it is looking for:

Cannot find server asymmetric key with thumbprint '0x58914660BBC7630245F92290BD1CE5F7EAD4EC22'.

Another way we could find the thumbprint would be to restore the backup of the master database, under an alternate name, and view the sys.asymmetric_keys table for the row that has our key's name and its corresponding thumbprint:

SELECT * FROM SYS.ASYMMETRIC_KEYS

- Results -

Recreating the key

We now should have all the pieces to recreate our asymmetric key to unlock/decrypt the database, so let's get to it. Before we start: we'll be updating the system table sys.asymmetric_keys table later on. In order to this, we have need restart the SQL Server in single-user mode (i.e. with the –m option). Once in single-user mode, we'll connect to the instance using the dedicated administrator connection (DAC) using the “ADMIN:” prefix (i.e. ADMIN:SQL_Server_name).

Once that has been done, let's begin:

1. Create the asymmetric key from the existing key stored on the HSM. The “CREATION_DISPOSTION = OPEN_EXISTING” tells SQL Server to use the key within the file, instead of creating a new one.

create asymmetric key SQL_EKM_RSA_2048_KEY
 from provider EKMProvider
 with
   provider_key_name ='ASM_KEY_SQLServerName' -- filename on HSM device
 , creation_disposition = OPEN_EXISTING 

2. Update the asymmetric key you just created with the correct thumbprint.

select * from sys.asymmetric_keys
 where name =  'SQL_EKM_RSA_2048_KEY'
 Change the SQL Server to allow updates.
 sp_configure 'allow updates', 1
 reconfigure with override
 go

Set the thumprint for the key. Remember, this is the thumbprint that TDE is looking for and will be in the error log when SQL fails to open the encrypted database.

Begin transaction 
 go
 update sys.asymmetric_keys
 set thumbprint = '0x58914660BBC7630245F92290BD1CE5F7EAD4EC22'
 where name = 'SQL_EKM_RSA_2048_KEY'
 go
 select * from sys.asymmetric_keys
 go
 commit -- if the update is good, otherwise ROLLBACK
 go
 -- Turn “allow updates” off.
 sp_configure 'allow updates', 0
 reconfigure with override
 go

At this point, we have completed recreating the key from the file stored on the HSM device, and we've updated it's thumbprint to match the thumbprint the encrypted backup file is looking for. Lastly, we have to create a login from the asymmetric key and give it the credentials to connect to the HSM device to allow SQL Server to read the key file and decrypt the files. The following SQL statements complete this step:

1. Create a login from asymmetric key.

create login TDELogin
 from asymmetric key SQL_EKM_RSA_2048_KEY

2. Create the credential that has the access information for EKM provider.

CREATE CREDENTIAL TDEProviderCredential
 WITH IDENTITY = 'EKM_User_Name',secret = 'EKM_Password'
 FOR CRYPTOGRAPHIC PROVIDER EKMProvider

3. Add the credential to the login

ALTER LOGIN TDELogin
 ADD CREDENTIAL TDEProviderCredential

If we had the correct key file, the database restore (or attach) will be successful, and we have (once again) saved the day. If not, we might have recreated the asymmetric key from the wrong file. If so, we're going to have to try each file on the HSM repeating the steps until we find the right one. Also, don't forget to restart SQL Server in normal mode (without the –M option) to allow normal access.

Summary

To repeat for emphasis on their importance, we have to have the following information to recover:

1. The EKM Provider's user account name and password, which is needed to register the EKM Provider's DLL.
2. The filename that the asymmetric key is stored in on the device.
3. The system generated thumbprint for the previous asymmetric key.

This information should be documented and secured somewhere you and your team know have access because, as most things to do being a DBA, success boils down to having right backup.