SQL Server 2008 Service Broker tutorial -- cannot receive the message (exception in transmission_status)

Question

I am learning how to use the Service Broker of SQL Server 2008 R2. When following the tutorial Completing a Conversation in a Single Database. Following the Lesson 1, I have successfully created the message types, contract, the queues and services. Following the Lesson 2, I have probably sent the message. However, when trying to receive the message, I get the NULL for the ReceivedRequestMsg instead of the sent content.

When looking at the sys.transmission_queue, the transmission_status for the message says:

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.

I have installed SQL Server using the Windows login like Mycomp\Petr. I am using that login also for the lessons.

Can you guess what is the problem? What should I check and or set to make it working?

Edited 2012/07/16: For helping to reproduce the problem, here is what I did. Can you reproduce the error if you follow the next steps?

Firstly, I am using Windows 7 Enterprise SP1, and Microsoft SQL Server 2008 R2, Developer Edition, 64-bit (ver. 10.50.2500.0, Root Directory located at C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL_PRIKRYL05\MSSQL).

1. Following the tutorial advice, I have downloaded the AdventureWorks2008R2_Data.mdf sample database, and copied it into C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL_PRIKRYL05\MSSQL\DATA\AdventureWorks2008R2_Data.mdf

2. The SQL Server Management Studio had to be launched "As Administrator" to be able to attach the data later. Then I connected the SQL Server.

3. Right click on Databases, context menu Attach..., button Add..., pointed to AdventureWorks2008R2_Data.mdf + OK. Then selected the AdventureWorks2008R2_Log.ldf from the grid below (reported as Not found) and pressed the Remove... button. After pressing OK, the database was attached and the AdventureWorks2008R2_log.LDF was created automatically.

4. The following queries were used for looking at "Service Broker enabled/disabled", and for enabling (the Service Broker was enabled successfully for the database):

---

USE master;
GO

SELECT name, is_broker_enabled FROM sys.databases;
GO

ALTER DATABASE AdventureWorks2008R2
      SET ENABLE_BROKER
      WITH ROLLBACK IMMEDIATE;
GO

SELECT name, is_broker_enabled FROM sys.databases;
GO

• Then, following the tutorial, the queries below were executed to create the message types, the contract, the queues, and the services:

---

USE AdventureWorks2008R2;
GO

CREATE MESSAGE TYPE
       [//AWDB/1DBSample/RequestMessage]
       VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE
       [//AWDB/1DBSample/ReplyMessage]
       VALIDATION = WELL_FORMED_XML;
GO

CREATE CONTRACT [//AWDB/1DBSample/SampleContract]
      ([//AWDB/1DBSample/RequestMessage]
       SENT BY INITIATOR,
       [//AWDB/1DBSample/ReplyMessage]
       SENT BY TARGET
      );
GO

CREATE QUEUE TargetQueue1DB;

CREATE SERVICE
       [//AWDB/1DBSample/TargetService]
       ON QUEUE TargetQueue1DB
       ([//AWDB/1DBSample/SampleContract]);
GO

CREATE QUEUE InitiatorQueue1DB;

CREATE SERVICE
       [//AWDB/1DBSample/InitiatorService]
       ON QUEUE InitiatorQueue1DB;
GO

So far, so good.

• Then the following queries are used to look at the queues (now empty when used):

---

USE AdventureWorks2008R2;
GO

SELECT * FROM InitiatorQueue1DB WITH (NOLOCK);
SELECT * FROM TargetQueue1DB WITH (NOLOCK);
SELECT * FROM sys.transmission_queue;
GO

• The problem manifests when the message is sent:

---

BEGIN TRANSACTION;

BEGIN DIALOG @InitDlgHandle
     FROM SERVICE
      [//AWDB/1DBSample/InitiatorService]
     TO SERVICE
      N'//AWDB/1DBSample/TargetService'
     ON CONTRACT
      [//AWDB/1DBSample/SampleContract]
     WITH
         ENCRYPTION = OFF;

SELECT @RequestMsg =
       N'<RequestMsg>Message for Target service.</RequestMsg>';

SEND ON CONVERSATION @InitDlgHandle
     MESSAGE TYPE 
     [//AWDB/1DBSample/RequestMessage]
     (@RequestMsg);

SELECT @RequestMsg AS SentRequestMsg;

COMMIT TRANSACTION;
GO  

When looking at the queues, the Initiator... and the Target... queues are empty, and the sent message can be found in sys.transmission_queue with the above mentioned error reported via the transmission_status.

Answer 1

alter authorization on database::[<your_SSB_DB>] to [sa];

The EXECUTE AS infrastructure requires dbo to map to a valid login. Service Broker uses the EXECUTE AS infrastructure to deliver the messages. A typical scenario that runs into this problem is a corporate laptop when working from home. You log in to the laptop using cached credentials, and you log in into the SQL using the same Windows cached credentials. You issue a CREATE DATABASE and the dbo gets mapped to your corporate domain account. However, the EXECUTE AS infrastructre cannot use the Windows cached accounts, it requires direct connectivity to the Active Directory. The maddening part is that things work fine the next day at office (your laptop is again in the corp network and can access to AD...). You go home in the evening, continue with Lesson 3... and all of the sudden it doesn't work anymore. Make the whole thing seem flimsy and unreliable. Is just the fact that AD conectivity is needed...

Another scenatio that leads to the same problem is caused by the fact that databases reteint the SID of their creator (the Windows login that issues the CREATE DATABASE) when restored or attached. If you used a local account PC1\Fred when you create the DB and then copy/attach the database to PC2, the account is invalid on PC2 (it is scoped to PC1, of course). Again, not much is affected but EXECUTE AS is, and this causes Service Broker to give the error you see.

And last example is when the DB is created by a user that later leaves the company and the AD account gets deleted. Seems like revenge from his part, but he's innocent. The production DB just stops working, simply because it's his SID that the dbo maps too. Fun...

By simply changing the dbo to sa login you fix this whole EXECUTE AS thing and all the moving parts that depend on it (and SSB is probably the biggest dependency) start working.

Answer 2

You would need to grant receive on your target queue to your login. And it should work!

USE [YourDatabase]
GRANT RECEIVE ON [dbo].[YourTargetQueue]
TO [Mycomp\Petr];
GO

And you also need to grant send for your user, permission on Target Service should be sufficient, but let's enable on both services for the future.

USE AdventureWorks2008R2 ;
GO

GRANT SEND ON SERVICE::[//AWDB/1DBSample/InitiatorService]
TO [Mycomp\Petr] ;
GO

GRANT SEND ON SERVICE::[//AWDB/1DBSample/TargetService]
TO [Mycomp\Petr] ;
GO