Restoring SQL Server Database - Master Key Not Opening

Question

I have to make a local copy of a remote SQL Server database. I did this by using Tasks > Backup from Management Studio. I then locally restored the backup, which seems to have everything -- tables, users, symmetric key, and certificate.

When I try to perform a select that requires me to open the symmetric key and decrypt by the certificate, I get this error:

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

Why am I being asked for this, and why doesn't it open automatically like it does on the remote server?

I've tried changing the master key, but without the original password, I can't do much.

Answer 1

The problem is the SMK has changed (since the machine has changed). There's an article explaining it here. Just export and import the SMK -- bearing in mind that any encrypted data in your copied-to system will be unreadable.

MSDN articles:

• Backing up the SMK
• Restoring the SMK

Answer 2

Here is a good article specifically on migrating a database that includes encryption:

http://www.sql-server-performance.com/2009/migrating-databases-checklist-part3/3/

But in short, you need to know the original password in order to move it.

You can backup and restore the key (i.e. replicate as you mention) but you'll need access to the remote server, ability to create backup, or copy of backup with original passwords:

http://msdn.microsoft.com/en-us/library/ff848768.aspx

This forum conversation may also prove useful for insight:

http://www.sqlservercentral.com/Forums/Topic775644-146-1.aspx