gotchaMinor
Why does the service broker require a master key after restoring to a different server?
Viewed 0 times
afterwhytherestoringbrokerdifferentservicemasterdoesrequire
Problem
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!
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!
Solution
When you restore a database,
Try
...for all restored databases involved with Broker.
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.
Code Snippets
ALTER DATABASE MyDB SET TRUSTWORTHY ONContext
StackExchange Database Administrators Q#70066, answer score: 4
Revisions (0)
No revisions yet.