Rotating SQL Encryption Keys in MS SQL Server 2008

What and Why?

As part of our security requirements, we have to keep certain data encrypted at rest.  Since we were talking a sizable amount of data, most of which is already in SQL Server, we decided on using TDE (Transparent Data Encryption) found in SQL Server 2008.  A year ago we set this up and encrypted 3 databases – 2 tiny, and 1 large.

As the security audit is coming around, we needed to go rotate the keys.  Here’s what we did:

I make no promises

I’m simply going to mention the steps that we took and what worked and what didn’t.  I am no SQL expert, and if you follow anything I did and it doesn’t work the same for you… sorry!

OK, now that that’s out of the way…

1. Backup.  We backed up everything.  Had copies of the databases, and even snapshots of the virtual machines.
2. Are you sure you’re backed up?  Do you have your old certificates, etc safely off of the machine?

So, after some poking around, I found my old script I used to enable TDE in the first place.  I also found the UI piece from Management Studio. It looks like this (right-click on database, Tasks –> Manage Database Encryption…)

So after reading this box, I decided the first step I needed was to create a new certificate.  My SQL from last year proved useful:

CREATE CERTIFICATE EncryptionCert2009 WITH SUBJECT = '2009 Encryption Certificate'

BACKUP CERTIFICATE EncryptionCert2009 TO FILE = '\\myserver\share\SQL\EncryptionCert2009Prod.cer' 

So, now that we’re armed with a new cert, it was back to the dialog box.  Choose the first checkbox, choose my new cert, hit OK. Poof. Worked just fine.

So, what really needs to be rotated?

The security guidelines were a little vague about what it meant to rotate keys.  Our auditor agreed that it may be sufficient to rotate just the certificate that guards the DEK (Database Encryption Key).  If that’s true, then we’re done!  However, it sure seemed easy enough to check the other checkbox in that dialog.  Since I was still on my database server in an unimportant database, I tried it.  And voila!  worked again!

So, I did both steps to another tiny database and again was successful.  Finally, I moved on to a 21Gb test copy of the larger database.  This time, it took about 30 min to complete the key change.  During this time, I executed the following query with similar results:

SELECT
    DB_NAME(e.database_id) AS DatabaseName, 
    e.database_id, 
    e.encryption_state, 
    CASE e.encryption_state 
        WHEN 0 THEN 'No database encryption key present, no encryption' 
        WHEN 1 THEN 'Unencrypted' 
        WHEN 2 THEN 'Encryption in progress' 
        WHEN 3 THEN 'Encrypted'      
        WHEN 4 THEN 'Key change in progress' 
        WHEN 5 THEN 'Decryption in progress' 
    END AS encryption_state_desc, 
    c.name, 
    e.percent_complete 
FROM sys.dm_database_encryption_keys AS e 
LEFT JOIN master.sys.certificates AS c ON e.encryptor_thumbprint = c.thumbprint 

OK, now that everything worked on the development server, I repeated all the same steps on the production server.  I made a new cert, backed it up, and did the two little databases.  All was well. 

There’s always a “But”…

However, when I did this to the large database (46Gb in production), the key rotation process never started.  The encryption_state was still 4 (Key change in progress) but the percent_complete never moved from 0!  After debating what we should do, we ended up placing a call with Microsoft for support.  We tried many things, and spent some time looking for some clue as to why it never started.  We looked for locks, etc,  but never found anything.  We also shut down other connections to the database.

In the end, we decided to try restarting SQL Services (please, do not try this because I said so). When it restarted, SQL was fine, the data was still there and readable.  In fact, it appeared as if I had never tried to regenerate the DEK.  So, we tried it again, and this time it worked with no problems.  Progress started climbing immediately and the whole process took about an hour.  We never did figure out what the problem was, but the key rotation ended up a success.

Good luck to you if you try this.  Our Microsoft technician had not heard of this problem before, and hopefully it was just an odd series of events that caused the problem.

Technorati Tags: SQL Server,TDE