Home Forums Article Discussions Article Discussions by Author Discuss Content Posted by Sachin Dedhiya The SQL Server Postal Service RE: The SQL Server Postal Service

  • May 10, 2006 at 12:36 pm

    #637086

    Hello Sachin,

    Thanks a lot for the explanation, I do understand the process now, it was VERY helpful.

    But I dicovered that my problem was due to security. I told you I did not have any error messages. It was not the case. I did not get any messages in the Messages tab or Results tab. But after I commented out END CONVERSATION line I did not get any messages in the TargetQueue too. So I took a look what exactly I had in the InitiatorQueue. I did have my output to Grid so I could see only the beginning of the msg_body. I new first lines were some XML before the actual lines of messages, so I copied and pasted into text editor. The mesages were:

     ?<?xml version="1.0"?><Error xmlns="-8494You'>http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8494</Code><Description>You do not have permission to access the service &apos;ResponseService&apos;.</Description></Error>

    I started to look up what do I have on the Broker Security. I also enabled Cross Database Ownership Chaining and changed ownership of both databases to SA and logged in as SA (connected my MS as SA). I could not access the service. So in following the article:

    http://msdn2.microsoft.com/en-us/library/ms166036.aspx

    SQL Server 2005 Books Online 
    Service Broker Dialog Security 
     
    I found out that: "For a conversation that uses anonymous security, the connection runs as a member of the fixed database role public in the target database. In this case, the fixed database role public must have permission to send a message to the service. However, the role needs no other permissions in the database."
     
    Then I granted the permissions:

    GRANT

    CONTROL ON

    SERVICE :: ResponseService

    TO Public

    And it Finally Worked! I was able to see my sent and response messages in the TargetQueue.

    Couple of comments:

    First, the GRANT statement is case-sensitive, when I typed Responseservice it could not find it. I install all my servers as AS_CI (Accent Sensitive Case Insensitive) but I do know that XML stuff is usually case sensitive

    Second, I worked as SA with a DBO SA on both databases! I HAVE to have permissions, and I dont! I also tried as another sysadmin login, does not matter. What happened to the concept of the server administration?

    Third, why would error messages go into the table (into queue)? They should be displayed as we used to or go into the special table (ErrorTable for example)  for auditing.

    Thanks again,

    Yelena

     

     

     

    Regards,Yelena Varsha