Sunday, June 19, 2005 4:02 AM nielsb

SQL Server 2005 Service Broker Stuff in June CTP

As Bob is doing, whenever a new build is released I tend to check out various stuff to see if it works as before. This however jumped up and bit me the other day.

You create two databases, say SSB1 and SSB2, in the same server instance. You have learnt your lesson from the April CTP that you have to create a master key in the database if you want to start a dialog conversation with encryption (which is the default). Now you create your various SSB objects in the two databases (obviously using SSB Admin, which makes life so much easier <g>).

You start the dialog and send a message, go over to the other database, selects from the queue and … no message!! What the ****. So you go back to the initiator db and sure enough, in sys.transmission_queue you find your message with an error saying something like: "This message could not be delivered because the user with ID 1 in database ID 6 does not have permission to send to the service. Service name: 's2'. " 

Hmm strange, the user is dbo in both databases, what goes on??? Well it turns out that MS has tightened up the security a bit, so in this CTP (June) you need to create/alter your database as TRUSTWORTHY: “alter database db_name set trustworthy on”. In coming CTP’s you’ll also need to do some more stuff, like granting the owner of the database certain permissions in the other database. That is however topic for another blog-post.

Oh and BTW, don’t forget Rushis challenge.


No Comments