CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2008-r2sql-server-2012service-broker
Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5787

Service broker does not use user specified in the route

I am trying to send the message from 1 server to another using windows login user. I created route for that:

CREATE ROUTE [SB_Server1_Server2Route]  
      AUTHORIZATION [COMPANY\SVC.SQLServiceBroker] 
      WITH SERVICE_NAME = '//Server1/Server2/UpdatedRecord_TargetService',
      ADDRESS = 'tcp://Server2:4022';

I am sending message using following query/procedure:

EXEC [dbo].[SB_SendBrokerMessage] '//Server1/Server2/UpdatedRecord_InitiatorService',
'//Server1/Server2/UpdatedRecord_TargetService', @XML;


CREATE PROCEDURE [dbo].[SB_SendBrokerMessage]
    @FromService sysname ,
    @ToService sysname ,
    @MessageBody XML
AS
    BEGIN
        SET NOCOUNT ON;
        IF ( @MessageBody IS NOT NULL
             AND @FromService IS NOT NULL
             AND @ToService IS NOT NULL
           )
            BEGIN
                DECLARE @conversation_handle UNIQUEIDENTIFIER;

                BEGIN TRANSACTION;

                BEGIN DIALOG CONVERSATION @conversation_handle
                    FROM SERVICE @FromService
                    TO SERVICE @ToService
                    WITH ENCRYPTION = OFF;

                SEND ON CONVERSATION @conversation_handle (@MessageBody);

                COMMIT TRANSACTION;
            END;
    END;
GO

And I am getting following error in the log:

Service Broker login attempt by user 'COMPANY\AnotherUser.' failed with error: 'Connection handshake failed. The login 'COMPANY\AnotherUser' does not have CONNECT permission on the endpoint. State 84.'. [CLIENT: 192.168.21.61]

So the question is: why it does not use the user specified in the route?

Upvotes: 0

Views: 1748

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294407

Ben already told you why using the AUTHORIZATION clause has nothing to do with Service Broker connections.

To set up two instances to be able to exchange Service Broker messages you need to set up Service Broker Transport Security. You can choose between certificate based authentication (see How does Certificate based authentication work) or Windows based authentication. The transport layer security has absolutely nothing to do with the current user. It does not matter what current user is running the Service Broker verbs, it only allows the two instances involved to connect to each other. I strongly recommend using certificate based authentication, but if you must* use Windows the you must grant CONNECT permission to the peer service account.

A second layer of security exists in Service Broker, namely dialog security (see Conversations Authentication). This allows two Service Broker services to authenticate each other and exchange messages. This, again, has nothing to do with current connected user. If specified then it requires creation of remote service binding objects. Is always certificate based.

In the end, when all the Service Broker security is set up both at transport and at conversation layers, then you must also specify what SQL users have the permission to use this infrastructure, and this is controlled through the RECEIVE permission granted on the queue that hosts a service.

Upvotes: 1

Ben Thul
Ben Thul

Reputation: 32737

The authorization clause on the route (or anything else) specifies who owns the route, not who people will connect as. Luckily, there's an easy fix for your situation: grant connect permission on the endpoint to whatever users need it.

Upvotes: 2

Related Questions