Our 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>
go
alter master key 
add encryption by password = 'migration_password'
go

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.

Filed under: Encryption, Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tuesday Tagged: encryption, microsoft sql server, T-SQL Tuesday