What Was I Thinking?

Follies & Foils of .NET Development
posts - 95 , comments - 352 , trackbacks - 0

Migrating SQL Server Broker Service (SSBS) Objects

I recently migrated my dev environment to a new pc.  I moved over all the databases and reattached them.  Unfortunately, I soon discovered my SSBS services weren’t working. I was sending messages to my service, but nothing was showing up in the queue. I added logging and monitoring to the stored procs that act as an entry point to the services, and the stored procs I use for activation.   Still, nothing.

When I ran the Broker Server Diagnostic tool (ssbdiagnose) (available in {Program files}\Microsoft Sql Server\100\tools\binn, it reported the following issues:

Service Broker Diagnostic Utility
The EXECUTE AS for the user dbo specified for activation on queue dbo.FulfillerQueue_Initiator_Mock cannot be impersonated due to an exception Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.


The EXECUTE AS for the user dbo specified for activation on queue dbo.FulfillerQueue_Target_Mock cannot be impersonated due to an exception Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.

Could not validate the SEND permission of user dbo on service FulfillerService_Target_Mock due to exception Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.
3 Errors, 0 Warnings

So what's going on here? I use integrated security on my dev machine, why is it complaining that the principal "dbo" doesn't exist?

The problem is the result of moving my database from another server (my previous dev machine).  The underlying SID of the owner object is no longer valid in my new database.  Since the owner id of these objects don’t match the owner id of the currently executing user, SQL Server generated a permission exception. 

 

The fix is pretty easy, I just needed to change the ownership of these objects back to the SA user:

ALTER AUTHORIZATION ON DATABASE [MYDATABASE] TO [SA]

Running SSBDiagnose with the CONFIGURATION option generated happier results:

C:\Program Files\Microsoft SQL Server\100\Tools\Binn>ssbdiagnose.exe -d FulfillmentMessaging CONFIGURATION FROM SERVICE FulfillerService_Initiator_Mock TO SERVICE Fulfiller Service_Target_Mock ON CONTRACT FulfillerContract ENCRYPTION OFF
Microsoft SQL Server 10.0.2531.0
Service Broker Diagnostic Utility
0 Errors, 0 Warnings

With my permissions errors resolved, my SSBS services were working once again.

Print | posted on Friday, November 5, 2010 4:58 PM | Filed Under [ SQL ]

Feedback

Gravatar

# re: Migrating SQL Server Broker Service (SSBS) Objects

Dude!... You totally rock for posting this fix. I've been thrashing on these rocks for several hours this afternoon... And your recommendation resolved my problem.

Much appreciated!
12/27/2010 7:54 PM | Bryce
Gravatar

# re: Migrating SQL Server Broker Service (SSBS) Objects

Good one dude... Its helped me at right time... Thanks for posting..
4/20/2011 1:55 AM | Siva Kumar
Post A Comment
Title:
Name:
Email:
Comment:
Verification:
 

Powered by: