10

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.

ashes999
  • 9,925
  • 16
  • 73
  • 124
  • Hmm, I hope it's not easy to restore a master key without the password, that's where the security of it all comes from. I guess this could be a lesson in using public key cryptography to protect your master key, and backup the certificate somewhere safe. – Petey B Jun 16 '11 at 19:31
  • 1
    @Petey maybe I'm missing something obvious here; on the original machine, you don't need to open the key, you can just run the stored procedure. Is there some flaw in my export/import? – ashes999 Jun 16 '11 at 19:36
  • The simplest way to solve this is to back up the database master key and assign it a password to protect the file. Then restore the database master key using the password to the restored database. This will work without you needing to know the original password used to create the DMK as long as the DMK is also encrypted by the SMK on your production system, which it is by default unless the encryption by key is dropped. – Michael Keleher Aug 25 '16 at 17:44

5 Answers5

5

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:

ashes999
  • 9,925
  • 16
  • 73
  • 124
2

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.

miken32
  • 42,008
  • 16
  • 111
  • 154
John Chase
  • 328
  • 3
  • 10
1

Same problem solved without data loss - if you have the password creating the master key

The master key in the database was encrypted by the Service Master Key. It automatically opened when you used the master key.

Now the Service Master Key is unable to open the master key, and SQL is prompting you to "OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'" manually or create the master key.

Adding encryption by the NEW SERVER's Service Master Key will again allow the automatic opening of the master key.

Kudos to the guys that answered the question in the link

Community
  • 1
  • 1
Alocyte
  • 293
  • 4
  • 13
1

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

RThomas
  • 10,702
  • 2
  • 48
  • 61
0

You cannot bypass the encryption. See this link for the official word from Microsoft.

RC_Cleland
  • 2,274
  • 14
  • 16
  • I'm not looking to bypass. I'm looking to replicate, and now I can no longer unencrypt data on the new machine. I've edited my question to make it more clear. – ashes999 Jun 17 '11 at 12:46