Restoring SQL Server Database - Master Key Not Opening

user_210780 0
Viewed 25892

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.

encryption sql-server-2008
5 Answers

First off, I don't yet have the reputation cred to comment on other's posts. If I did, I would comment on the accepted answer to say that I have seen others on here complain when someone answers with just a bunch of links but no content because over time, the links go bad. I'm joining in that complaint. Don't have the cred for my down-vote to count, but I down-voted it anyway.

Then, I am working through setting up a backup maintenance plan that includes a database with some encrypted columns. Everything was working great, except this same problem: on the restore server, I had to open the DMK (database master key) prior to opening the symmetric key (or doing inline decryption), something not needed on the original server.

I used the keywords to search in Google from the URL of the now-dead link above in the accepted answer, which led me to this:

restore database having encryption to other server

The short of it is, after restoring the DMK on the target server, I run the following so that the SMK (service master key) will hold the password for the DMK and open it automatically if needed:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'yourStrongPassword';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

Now the target mimics the behavior (seamlessly decrypting) of the source.

user_23664410
Related