Restoring database with unsafe assembly on another server

Question

On Server1 I have a database with an unsafe assembly. Database has Trustworthy=False. Assembly has strong name and I used asymmetric key as described here: http://msdn.microsoft.com/en-us/library/ms345106.aspx

I need to migrate this database to Server2.

I made full backup on Server1 and restored it to Server2 (CLR is enabled on Server2)

Now I need to create new Asymmetric key on Server2.

When I issue this command on master database:

CREATE ASYMMETRIC KEY MyAppKey FROM file ='c:\MyApp.snk'

I get this error message:

Msg 15581, Level 16, State 3, Line 1
Please create a master key in the database or open the master key in the session before performing this operation.

I am confused about where I supposed to create master key - on master database or on my application database? It's been a long time since I initially deployed this assembly on Server1 and I just don't remember all details anymore.

Answer 1

You need a master key in the master database (where you’re creating your certificate) if you don’t use a specific password with CREATE CERTIFICATE. That was the way you must have done it on Server1 originally.

 

Make sure you review your original instructions (I got an error on the link you referenced); you’ll also need a LOGIN in master for the cert, and that LOGIN must have UNSAFE_ASSEMBLY permission. I posted some code here: http://www.sqlskills.com/BLOGS/BOBB/post/Security-in-SQL-Server-2005-unsafe-assemblies-in-Sept-CTP.aspx that will still work.

 

Cheers, Bob

Answer 2

You need a master key in the master database (where you’re creating your certificate) if you don’t use a specific password with CREATE CERTIFICATE. That was the way you must have done it on Server1 originally.

 

Make sure you review your original instructions (I got an error on the link you referenced); you’ll also need a LOGIN in master for the cert, and that LOGIN must have UNSAFE_ASSEMBLY permission. I posted some code here: http://www.sqlskills.com/BLOGS/BOBB/post/Security-in-SQL-Server-2005-unsafe-assemblies-in-Sept-CTP.aspx that will still work.

 

Cheers, Bob

Yes, this is the step I've been missing - create master key in master database. Where would I use password specified while creating master key in master database in the future? How can I check if master key in master database already exists?

Answer 3

You have to use a password with a database master key, there are no other choices.

 

You can see if the master key exists (in any database) by using

select * from sys.symmetric_keys

where name = '##MS_DatabaseMasterKey##';

 

Cheers, Bob

Answer 4

You have to use a password with a database master key, there are no other choices.

 

You can see if the master key exists (in any database) by using

select * from sys.symmetric_keys

where name = '##MS_DatabaseMasterKey##';

 

Cheers, Bob

I understand I have to use a password with a database master key.

What I am trying to find out - I would probably need to use this password for some purpose in the future (there has to be a reason why I backed it up :D). I'd like to know what this purpose is.

Answer 5

Hopefully, one or more of these answers is the answer to the question you’re asking...

 

You didn’t back up the password for the database master key, you backed up the database master key. In case “something happens” to it, you can restore it with “restore master key”.

 

Any secrets (keys) stored in a database aren’t stored unencrypted, so creating a certificate needs a mechanism to encrypt it. The default if you don’t specify one (on CREATE CERTIFICATE) is to encrypt with the database master key. Therefore, it complained when you didn’t have one.

 

You need the password for the database master key to restore it with “restore master key”. In addition, database master keys have two encryptions by default (with password and with service master key). If you choose to drop the encryption by service master key (you can do this explicitly, but most often this happens when you move a database from one instance of SQL Server to another), you must specify the password to open it before you can use it.

 

If you have encryption by service master key (the usual case) it uses that one to “auto-open” the database master key. Otherwise, you’d have to open the database master key to use your cert. When you move database to another instance, you can “fix up” encryption by the new service master key by using “alter master key add encryption by service master key”. You must have database master key open (by supplying the password) to do this also.

 

It is not a requirement to use the SAME password for different database master keys (in master or anywhere).

 

Hope this helps,

Cheers,

Bob

Answer 6

Hopefully, one or more of these answers is the answer to the question you’re asking...

 

You didn’t back up the password for the database master key, you backed up the database master key. In case “something happens” to it, you can restore it with “restore master key”.

 

Any secrets (keys) stored in a database aren’t stored unencrypted, so creating a certificate needs a mechanism to encrypt it. The default if you don’t specify one (on CREATE CERTIFICATE) is to encrypt with the database master key. Therefore, it complained when you didn’t have one.

 

You need the password for the database master key to restore it with “restore master key”. In addition, database master keys have two encryptions by default (with password and with service master key). If you choose to drop the encryption by service master key (you can do this explicitly, but most often this happens when you move a database from one instance of SQL Server to another), you must specify the password to open it before you can use it.

 

If you have encryption by service master key (the usual case) it uses that one to “auto-open” the database master key. Otherwise, you’d have to open the database master key to use your cert. When you move database to another instance, you can “fix up” encryption by the new service master key by using “alter master key add encryption by service master key”. You must have database master key open (by supplying the password) to do this also.

 

It is not a requirement to use the SAME password for different database master keys (in master or anywhere).

 

Hope this helps,

Cheers,

Bob

Thanks, this helps.