Database Master Keys can have more than one password.


August 11, 2015 by Kenneth Fisher

T-SQL TuesdayOur subject for this month’s T-SQL Tuesday blog party (#69) is Encryption, hosted by a guy with an awesome first name: Ken Wilson (b/t).

Encryption isn’t a subject I spend a lot of time on. I’ve studied it enough to know most (I hope) of the terms and have a fairly good idea how to encrypt, decrypt and deal with DR on an encrypted database. In other words, just enough to get by. Stuff happens though, and on one notable occasion I had to move a database from one server to another, and not only was there encryption involved but no one knew the password for the database master key.

I had a brief moment of panic since the old server was supposed to be decommissioned soon. So I had to get this database moved. It was a brief moment of panic though since the current database was up and I was sure there had to be a way to deal with the problem. It took me a few minutes, but I found it! Database master keys can have more than one password! I found this very nice blog post on moving databases with database master keys. If you look near the bottom you will find the steps involved in moving the database. Note step number two.

2. (Optional) If you don’t know a valid password for the Database Master Key you can create a new one. (Remember that multiple passwords can encrypt the DMK)

use <database>
alter master key 
add encryption by password = 'migration_password'

So if we don’t know the appropriate password we can add a new password, remove the service master key encryption if necessary, backup, restore and open the key up with the new password. Woo Hoo!

Oh and once done we do the most important step, backup the key and document that password somewhere securely.

3 thoughts on “Database Master Keys can have more than one password.

  1. matt.bowler says:

    Thanks for the link Kenneth, I feel like I’m in good company here. 🙂

  2. […] Kenneth Fisher (@sqlstudent144) wrote about our awesome first names and how to recover from a forgotten database master key. […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,753 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: