Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've been attempting to learn how to set up Service Broker through Microsoft's "Completing a Conversation in a Single Database" tutorial. However, when I actually try to send a message, it fails until I change the owner of the database from my account (which appears to be the default) to SA. What's going on here?

I'm using the AdventureWorks2008R2 datafile from CodePlex attached like this:

CREATE DATABASE AdventureWorks2008R2 ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.INSTANCE\MSSQL\DATA\AdventureWorks2008R2_Data.mdf') 
FOR ATTACH_REBUILD_LOG;

I can enable Service Broker and create the message types, contract, and target/initiator services and queues, as instructed in the tutorial:

-- Creating the Conversation Objects
-- http://technet.microsoft.com/en-us/library/bb839488(v=sql.105).aspx

USE master;
GO
ALTER DATABASE AdventureWorks2008R2
      SET ENABLE_BROKER;
GO
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

...and I can begin the conversation:

-- Beginning a Conversation and Transmitting Messages
-- http://technet.microsoft.com/en-us/library/bb839499(v=sql.105).aspx

DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
DECLARE @RequestMsg NVARCHAR(100);

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

However, at this point, if I try to receive the conversation, I don't see anything. This query (still following the tutorial) returns NULL:

DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg NVARCHAR(100);
DECLARE @RecvReqMsgName sysname;

BEGIN TRANSACTION;

WAITFOR
( RECEIVE TOP(1)
    @RecvReqDlgHandle = conversation_handle,
    @RecvReqMsg = message_body,
    @RecvReqMsgName = message_type_name
  FROM TargetQueue1DB
), TIMEOUT 1000;

SELECT @RecvReqMsg AS ReceivedRequestMsg;

The transmission queue ('select * from sys.transmission_queue') shows one record that is not being transmitted. In the transmission_status field I see:

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.

How is this possible? Certainly I must have permission over objects in my own server, on which I am sysadmin. And I can't imagine "dbo" doesn't exist - this is the default AdventureWorks database straight from Microsoft, and the dbo schema is right there in the schemas list.

Following the unexplained advice on a blog I found, I changed the database owner to SA:

ALTER AUTHORIZATION ON DATABASE::[dbname] TO [SA]

...and the message transmitted perfectly.

So, my question is, what did I really change here? Why does SA, who should have the same rights as me as sysadmin, suddenly make a difference? I presume I'm misunderstanding something about SQL Server's security.

share|improve this question
1  
What about if you set the database to TRUSTWORTHY? Other concerns, of course, but I don't think owner as sa is the only option (nor do I understand why you don't want your databases owned by sa). Finally, dbo is a schema, not a principal. There is a role called db_owner which a lot of people confuse with dbo. –  Aaron Bertrand Feb 3 at 20:28
    
@AaronBertrand I set TRUSTWORTHY on a new copy of the database (same steps as above, except giving sa ownership) and did not observe a difference. I actually don't have a problem with sa owning databases, I just want to understand why that fixes the issue. Finally, it seems I have some reading to do about principles... I had thought that a schema was a principle. Thank you for pointing out that error! –  Warrior Bob Feb 3 at 23:33
    
Are you connected to an LAN via AD? I found that when my domain a/c owned the database and I tried to work on stuff at home (not connected to corporate network) that SSSB wasn't happy that it couldn't authenticate my account, but it wasn't fussed when owner was SA. –  Rikalous Feb 4 at 0:25
    
@Rikalous This is an AD environment but I am connected, however the SQL Server instance is running from my workstation if that matters. Did you run into a similar issue? –  Warrior Bob Feb 4 at 4:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.