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.

Answer 7

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

 

This works

Answer 8

Hi All,

was running into the same problem because I attached the AdventureWorks database from CodePlex. Like Fran mentioned that the user that created the database doesn't exist and you have to change the authorization. 

This is what fixed the issue in my case.

Thanks

channa