Cannot get the Service Broker work

Question

Some months ago I was playing with Service Broker and everything was ok. But now I just can't get it work. I opened a sample "helloworld" project and still cannot get a message in a target queue. No errors, just empty queues. We have two SQL servers sept CTP on different computers and both give the same issue. It looks like it should have a very simple solution that I cannot come up with.

The following script initializes service broker and sends a message, then selects both target and initiator queues. On my environment it just returns two empty selects.

--Initializing service broker objects

use master

GO

 

SET NOCOUNT ON

GO

 

IF NOT EXISTS

  (SELECT * FROM sys.databases

   WHERE name = 'AdventureWorks'

   AND is_broker_enabled = 1)

BEGIN

  ALTER DATABASE AdventureWorks SET ENABLE_BROKER

END

GO

 

USE AdventureWorks

GO

 

IF EXISTS (SELECT *

           FROM sys.services

           WHERE name = 'InitiatorService')

BEGIN

    DROP SERVICE InitiatorService

END

GO

 

IF EXISTS (SELECT *

           FROM sys.services

           WHERE name = 'TargetService')

BEGIN

    DROP SERVICE TargetService

END

GO

 

IF EXISTS (SELECT *

           FROM sys.service_contracts

           WHERE name = 'HelloWorldContract')

BEGIN

    DROP CONTRACT HelloWorldContract

END

GO

 

IF EXISTS (SELECT *

           FROM sys.service_message_types

           WHERE name = 'HelloWorldMessage')

BEGIN

    DROP MESSAGE TYPE HelloWorldMessage

END

GO

 

IF OBJECT_ID('[dbo].[InitiatorQueue]') IS NOT NULL AND

   EXISTS(SELECT *

          FROM sys.objects

          WHERE object_id = OBJECT_ID('[dbo].[InitiatorQueue]')

            AND type = 'SQ')

BEGIN

    DROP QUEUE [dbo].[InitiatorQueue]

END

GO

 

IF OBJECT_ID('[dbo].[TargetQueue]') IS NOT NULL AND

   EXISTS(SELECT *

          FROM sys.objects

          WHERE object_id = OBJECT_ID('[dbo].[TargetQueue]')

            AND type = 'SQ')

BEGIN

    DROP QUEUE [dbo].[TargetQueue]

END

GO

 

CREATE MESSAGE TYPE HelloWorldMessage

    VALIDATION = WELL_FORMED_XML

GO

 

CREATE CONTRACT HelloWorldContract

    ( HelloWorldMessage SENT BY INITIATOR)

GO

 

CREATE QUEUE [dbo].[TargetQueue]

GO

 

CREATE QUEUE [dbo].[InitiatorQueue]

GO

 

CREATE SERVICE InitiatorService

    ON QUEUE [dbo].[InitiatorQueue]

GO

 

CREATE SERVICE TargetService

    ON QUEUE [dbo].[TargetQueue]

    (HelloWorldContract)

GO

 

-- Starting conversation

 

USE AdventureWorks

GO

 

--BEGIN TRANSACTION

GO

DECLARE @message XML

SET @message = N'<message>Hello, World!</message>'

 

-- Declare a variable to hold the conversation

-- handle.

 

DECLARE @conversationHandle UNIQUEIDENTIFIER

 

-- Begin the dialog.

 

BEGIN DIALOG CONVERSATION @conversationHandle

    FROM SERVICE InitiatorService

    TO SERVICE 'TargetService'

    ON CONTRACT HelloWorldContract;

 

-- Send the message on the dialog.

 

SEND ON CONVERSATION @conversationHandle

  MESSAGE TYPE HelloWorldMessage

  (@message)

 

print @conversationHandle

 

GO

 

--COMMIT TRANSACTION

 

GO

 

SELECT * FROM [dbo].[TargetQueue]

SELECT * FROM [dbo].[InitiatorQueue]

 

Answer 1

Can you run this query in AdventureWorks: 

SELECT * FROM sys.transmission_queue

and look at the content of the transmission_status column? It should indicate the reason for which the message cannot be delivered.

HTH,
~ Remus

Answer 2

As I thought it is a very simple problem . I just included

WITH ENCRYPTION = OFF

to BEGIN DIALOG CONVERSATION statement and everything started working as expected (thanks Tito Jermaine for comment).

Answer 3

The other way around it is to mark your target database as trustworthy: alter database db_name set trustworthy on, and create a master key in both databases: create master key encryption by password = 'some_pwd'

Niels

Answer 4

I have "WITH ENCRYPTION = OFF" and it's still not working.  The status in the transmission queue reads:

An exception occurred while enqueueing a message in the target queue. Error: 15517, State: 1. 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.

Any idea?

Thx

Answer 5

The original Windows user that created the database is no longer available. Change the database ownership to a valid login, so that user 'dbo' can be impersonated:

ALTER AUTHORIZATION ON DATABASE::[yourdatabase] TO [sa];

HTH,
~ Remus

 

Answer 6

For those of you reading this and asking what statement the "WITH ENCRYPTION = OFF" clause applies to, it's the BEGIN DIALOG CONVERSATION statement.