Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've been moving some databases which use the service broker to new servers recently via backup and restore. I've noticed that once I do this, these databases start throwing the database master key required messages in the log. Encryption isn't being used in with the broker, and no keys are set in the original server and database, so why does it require one once I restore it? Is it maybe using the service master key if a database master key isn't set? My searching hasn't turned up specifics of what happens if you don't set one originally, it just seems to be assumed you have one set. It's been mentioned that there are issues if it is not set, but we haven't had any.

Also, does it actually require a key? I'm starting to wonder if this is just an incorrect message, and if I just do the UID update with the alter statement, if that will resolve it, or do I have to set the key when moving to a new server?

Thank you!

share|improve this question
1  
can you post the message or error that you are receiving in error logs ? –  Kin Jul 9 at 15:24
    
It's this one: Service Broker needs to access the master key in the database 'MyDB'. Error code:26. The master key has to exist and the service master key encryption is required. –  user42454 Jul 9 at 18:17
add comment

2 Answers 2

When you restore a database, TRUSTWORTHY is automatically set to OFF. For Service Broker, if you don't use encryption and do use cross-database message transmission, TRUSTWORTHY needs to be set to ON.

Try

ALTER DATABASE MyDB SET TRUSTWORTHY ON

...for all restored databases involved with Broker.

share|improve this answer
    
I will give this a try, but Trustworthy is off by default on databases, and on the source databases it is off. We aren't doing cross-database transmission as far as I know, we are using it with IIS only. I did look, and trustworthy hasn't been explicitly set on the originating databases either. If it does address the issue, the broker behaves differently on the originating server? –  user42454 Jul 9 at 18:12
    
could be a server level setting too, perhaps... –  JNK Jul 9 at 20:37
    
Don't enable trustworthy on your service broker database by default. Turning on Trustworthy opens up some major security holes in SQL Server and can allow for privilege escalation. –  mrdenny Jul 9 at 20:41
    
@mrdenny good point, I should have put a disclaimer in there. I was operating under the assumption this was a cross-DB conversation. –  JNK Jul 9 at 20:49
1  
@user42454 Can you share how you're starting dialogs? Are you specifying 'CURRENT DATABASE'? Is it possible there's a remote service binding or you're using a GUID that doesn't match the guid on the database? Did you set new broker on the db? –  JNK Jul 9 at 20:54
show 2 more comments

Service Broker uses encryption by default unless you specify that you don't want to use it when sending the messages. At some point the database master key had to be created unless like I said you were specifying that encryption was off when you send the messages (it might be when you create the conversation, I don't remember).

Anyway, when you restored the database if the key was only encrypted by the service master key on the last server then you wouldn't have been able to use it so you'd need to recreate it.

share|improve this answer
    
Yes, you specify no encryption when you BEGIN DIALOG. –  JNK Jul 9 at 20:48
    
I'll have to ask the devs to search he source and see how it's being done. While I find that out, lets say I create a brand new database. It won't have an encryption key. I can set Enable_Broker at this point without getting an error. If I then issue a begin dialog without specifying no encryption, what is it encrypted with? The service master key? I think this is the situation we are in. If I go to the source databases there doesn't appear to be database master keys to transfer, which is part of the confusion of being asked for them later. –  user42454 Jul 10 at 13:45
    
If you do a begin dialog without specifying that there's no encryption you should get an error back, or at least logged into the sys.transmission_queue DMV. –  mrdenny Jul 10 at 15:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.