Random Thunks

I had a situation occur recently where I needed to restore a DB to a new server but I didn’t have the ability the bring the Service Master Key over with it. Nor did I have the original password used to encrypt the Database Master Key (it was before my time).

What I did have was access to the box with the database still running and the associated key hierarchy still operational (symmetric keys and the like).

I’ve not done too much messing around with the database master key before, feeling it was best left well alone, however the need to migrate this DB was there and I needed to pull it across without loosing access to the encrypted data.

To prototype this activity out I created a database on my local DB server and utilized the services of another Sql Server 2008 Server elsewhere in the organization.

So, first off I needed to create the key hierarchy and a place to store my test encrypted data:

Create Master Key Encryption By Password = 'abc123!!!ORIGINAL';
Create Asymmetric Key AKey With Algorithm = RSA_2048;
Create Symmetric Key SKey With Algorithm = TRIPLE_DES_3KEY
    Encryption By Asymmetric Key AKey
Create Table dbo.ASaucerfulOfSecrets
    (
        TheSecret        VarBinary(MAX)
    )
Open Symmetric Key SKey Decryption By Asymmetric Key AKey;
Insert Into dbo.ASaucerfulOfSecrets Values
    ( EncryptByKey(Key_Guid('SKey'), N'Ssssh! This is a BIG secret!'
                                   , 1, 0x12345));
Close Symmetric Key SKey;

I confirmed that encryption was working as expected by simply pulling the data back:

Select *
From dbo.ASaucerfulOfSecrets
Open Symmetric Key SKey Decryption By Asymmetric Key AKey;
Select Cast(DecryptByKey(TheSecret, 1, 0x12345) As NVarChar(MAX))
  From dbo.ASaucerfulOfSecrets;
Close Symmetric Key SKey;

Which gave me the following results

TheSecret

0x00496C61F87C4941BF7CBE8C879146BA0100000043C4B3F8824C73EBF3C0…

(No column name)

Ssssh! This is a BIG secret!

Life so far was operating within the expected parameters. Now I backed up the DB and restored it to a different server.

Running the select again I got the expected exceptions raised due to the inability to open the Database Master Key:

(1 row(s) affected)

Msg 15581, Level 16, State 3, Line 3

Please create a master key in the database or open the master key in the session before performing this operation.

(1 row(s) affected)

Msg 15315, Level 16, State 1, Line 5

The key ‘SKey’ is not open. Please open the key before using it.

So, now came the exercise of correcting the issue. Given that we had supposedly lost the password to the original DB, it’s safe to assume that one would want to recreate it (and this time, write the bloody thing down somewhere!). So that’s the approach I took. Back on the source server I ran the following:

Alter Master Key Regenerate
    With Encryption By Password = 'abc123!!!New'

The Books OnLine warns that this can be an intensive process and I suppose it comes down to just how many keys you have defined in your hierarchy. For the purposes of this test I had but one key so I wasn’t worried. Now I had a Master Key password that I knew of so I performed another backup, moved the DB to the secondary server and restored it. Just for giggles I reran the select again and received (rather unsurprisingly) the same results.

Next I tried the select again but only after explicitly opening the Database Master Key (with the ‘new’ password):

Open Master Key Decryption By Password = 'abc123!!!New'
Select *
From dbo.ASaucerfulOfSecrets
Open Symmetric Key SKey Decryption By Asymmetric Key AKey;
Select Cast(DecryptByKey(TheSecret, 1, 0x12345) As NVarChar(MAX))
  From dbo.ASaucerfulOfSecrets;
Close Symmetric Key SKey;

This did indeed pull back the decrypted data. Now for the final action – adding the Service Master Key to the Database Master Key:

Open Master Key Decryption By Password = 'abc123!!!New';
Alter Master Key Add Encryption By Service Master Key;

This enabled the encryption to use either an explicitly opened master key, or use the service master key – which is exactly what I was after.

I was able to confirm the results by performing the select again (without opening the master key first) and this gave me my encrypted data back. Tada!

I’ve been fiddling around with some jobs recently that required values to be kept from one job step to another. Not finding anything much exciting I went for plan Z – roll my own.

They’re simple procedures but do work nicely.

First I create a Schema that everything will be bundled under:

Now we’ll need a backing store:

Next I create the SessionDestroyer Procedure (since it can be called from the next one this keeps the dependencies happy and calm):

Along comes the SessionSaverStore next:

Finally the SessionRetriever:

Use is pretty straightforward. As an Proof of concept I created a Sql Agent Job with two steps – Store’ and ‘Fetch’

Store contained the following:

And Fetch:

Granted it’s missing some bells and whistles (like exception handling) and I’d wager I could stuff the Retriever in as a function. However as a first hit I was pretty chuffed with it!