HiveBrain v1.2.0
Get Started
← Back to all entries
gotchaMinor

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

Submitted by: @import:stackexchange-dba··
0
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!

Solution

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.

Code Snippets

ALTER DATABASE MyDB SET TRUSTWORTHY ON

Context

StackExchange Database Administrators Q#70066, answer score: 4

Revisions (0)

No revisions yet.