Published on

May 18, 2010

Recovering Encryption Key in SQL Server

Introduction

SQL Server 2008 introduced new features called Transparent Database Encryption (TDE) and Extensible Key Management (EKM). TDE encrypts data when writing it to disk and decrypts it when reading from disk into cache. It also encrypts database backups. EKM allows SQL Server to store the encryption key on a hardware device called High Security Module (HSM). In this article, we will discuss the recovery process of the encryption key for a database that has been encrypted using EKM/TDE and is stored on an HSM device.

Recovery Process

If we need to recover the encryption key for a database, we first need to ensure that the necessary setup has been done to allow communication between the HSM and the SQL Server instance. This includes creating the EKM Provider’s DLL file and registering it within SQL Server. Once this is done, we can proceed with the recovery process.

One option to recover the key is to restore the Master database from a backup that contains the EKM provider settings and the asymmetric key required to decrypt the database files and backups. However, if restoring the Master database is not an option, we can recreate the key from its file stored on the HSM device.

To register the EKM Provider’s DLL file, we can use the following SQL:

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 key from the file on the device.
   alter login sa
   add credential EKMcredential

Once the EKM Provider has been enabled and the DLL has been registered, we can list the EKM providers using the following SQL:

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

Next, we need to know the filename and location of the key stored on the HSM device. If we don’t have this information, we will need to try recreating the key from each file on the HSM and test it to see if it decrypts the backup file.

Assuming we know the filename or have a list of files, we also need the previous key’s thumbprint. The thumbprint is a unique identifier of the key stored on the database backup files. We can find the thumbprint by attempting a restore of the encrypted backup, which will return an error message with the thumbprint it is looking for. Alternatively, we can 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.

Once we have all the necessary information, we can recreate the key using the following steps:

1. Create the asymmetric key from the existing key stored on the HSM.
   create asymmetric key SQL_EKM_RSA_2048_KEY
   from provider EKMProvider
   with provider_key_name = 'ASM_KEY_SQLServerName', creation_disposition = OPEN_EXISTING

2. Update the asymmetric key 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
   Set the thumbprint for the key.
   Begin transaction
   update sys.asymmetric_keys
   set thumbprint = '0x58914660BBC7630245F92290BD1CE5F7EAD4EC22'
   where name = 'SQL_EKM_RSA_2048_KEY'
   commit
   Turn “allow updates” off.
   sp_configure 'allow updates', 0
   reconfigure with override

Lastly, we need to create a login from the asymmetric key and give it the credentials to connect to the HSM device. This allows SQL Server to read the key file and decrypt the files. The following SQL statements complete this step:

1. Create a login from the asymmetric key.
   create login TDELogin from asymmetric key SQL_EKM_RSA_2048_KEY

2. Create the credential that has the access information for the 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

Conclusion

Recovering the encryption key for a database encrypted using EKM/TDE and stored on an HSM device can be a complex process. It requires proper setup and documentation of the necessary information. By following the steps outlined in this article, you can successfully recover the key and decrypt the database files.

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.