SQL Server 2008 Service Broker 教程——无法接收消息(transmission_status 中的异常) [英] SQL Server 2008 Service Broker tutorial -- cannot receive the message (exception in transmission_status)
问题描述
我正在学习如何使用 SQL Server 2008 R2 的 Service Broker.按照教程在单个数据库中完成对话.按照 第 1 课，我已经成功创建消息类型、契约、队列和服务.按照 第 2 课，我可能已经发送消息.但是，当尝试接收消息时，我得到 ReceivedRequestMsg 的 NULL 而不是发送的内容.
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.
查看 sys.transmission_queue 时，消息的 transmission_status 表示:
When looking at the sys.transmission_queue, the transmission_status for the message says:
在目标队列中排队消息时发生异常.错误:15517，状态:1.无法作为数据库主体执行，因为主体dbo"不存在，无法模拟此类主体，或者您没有权限.
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.
我已经使用 Mycomp\Petr 之类的 Windows 登录安装了 SQL Server.我也在课程中使用该登录名.
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?
于 2012 年 7 月 16 日为了帮助重现问题，这是我所做的.如果您按照以下步骤操作，您能否重现该错误?
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?
首先，我使用的是 Windows 7 Enterprise SP1 和 Microsoft SQL Server 2008 R2，Developer Edition，64 位(版本 10.50.2500.0，根目录位于 C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL_PRIKRYL05\MSSQL).
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).
按照教程建议，我下载了 AdventureWorks2008R2_Data.mdf 示例数据库，并将其复制到 C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL_PRIKRYL05\MSSQL\DATA\AdventureWorks2008R2_Data.mdf
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
SQL Server Management Studio 必须以管理员身份"启动才能稍后附加数据.然后我连接了 SQL Server.
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.
右键单击数据库，上下文菜单附加...，按钮添加...，指向 AdventureWorks2008R2_Data.mdf + OK.然后从下面的网格中选择 AdventureWorks2008R2_Log.ldf(报告为 Not found)并按下 Remove... 按钮.按确定"后，数据库已附加并自动创建 AdventureWorks2008R2_log.LDF.
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.
以下查询用于查看Service Broker enabled/disabled"和启用(Service Broker 已成功为数据库启用):
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
- 然后，按照教程，执行以下查询以创建消息类型、合同、队列和服务:
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.
- 然后使用以下查询来查看队列(使用时现在为空):
USE AdventureWorks2008R2;
GO
SELECT * FROM InitiatorQueue1DB WITH (NOLOCK);
SELECT * FROM TargetQueue1DB WITH (NOLOCK);
SELECT * FROM sys.transmission_queue;
GO
- 问题在发送消息时显现:
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
查看队列时，Initiator...和Target...队列为空，发送的消息可以在sys中找到.transmission_queue 通过 transmission_status 报告上述错误.
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.
推荐答案
alter authorization on database::[<your_SSB_DB>] to [sa];
EXECUTE AS 基础结构需要 dbo 映射到有效登录.Service Broker 使用 EXECUTE AS 基础结构来传递消息.遇到此问题的典型场景是在家工作时使用公司笔记本电脑.您使用缓存凭据登录到膝上型计算机，然后使用相同的 Windows 缓存凭据登录到 SQL.您发出 CREATE DATABASE 并且 dbo 被映射到您的公司域帐户.但是，EXECUTE AS 基础设施不能使用 Windows 缓存帐户，它需要直接连接到 Active Directory.令人抓狂的部分是第二天在办公室一切正常(您的笔记本电脑再次进入公司网络并且可以访问 AD...).你晚上回家，继续第 3 课......突然之间它不再起作用了.让整个事情看起来脆弱和不可靠.只是需要 AD 连接的事实...
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...
导致相同问题的另一种情况是由于数据库在恢复或附加时保留其创建者的 SID(发出 CREATE DATABASE 的 Windows 登录名).如果您在创建数据库时使用了本地帐户 PC1\Fred，然后将数据库复制/附加到 PC2，则该帐户在 PC2 上无效(当然，它的范围是 PC1).同样，受影响的不多，但 EXECUTE AS 受影响，这会导致 Service Broker 给出您看到的错误.
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.
最后一个例子是数据库是由后来离开公司的用户创建的，并且 AD 帐户被删除.看起来像是在报复他，但他是无辜的.生产数据库停止工作，仅仅是因为 dbo 也映射了 his SID.有趣...
And last example is when the DB is created by an 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...
通过简单地将 dbo 更改为 sa 登录，您可以修复整个 EXECUTE AS 事物以及依赖于它的所有活动部件(并且 SSB 可能是最大的依赖项)开始工作.
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.
这篇关于SQL Server 2008 Service Broker 教程——无法接收消息(transmission_status 中的异常)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
