Why does the service broker require a master key after restoring to a different server

encryptionservice-broker

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!

Best Answer

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.

Related Question