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课，我已经成功创建消息类型、合同、队列和服务.在Lesson 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.
我已经使用 Windows 登录安装了 SQL Server，例如 MycompPetr.我也在使用该登录名来上课.
I have installed SQL Server using the Windows login like MycompPetr. 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/07/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，开发人员版，64 位(版本 10.50.2500.0，根目录位于 C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL_PRIKRYL05MSSQL).
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 FilesMicrosoft SQL ServerMSSQL10_50.SQL_PRIKRYL05MSSQL).
按照教程的建议，我下载了 AdventureWorks2008R2_Data.mdf 示例数据库，并将其复制到 C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL_PRIKRYL05MSSQLDATAAdventureWorks2008R2_Data.mdf
Following the tutorial advice, I have downloaded the AdventureWorks2008R2_Data.mdf sample database, and copied it into C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL_PRIKRYL05MSSQLDATAAdventureWorks2008R2_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... 按钮.按 OK 后，附加数据库并自动创建 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 已启用/已禁用"以及启用(已成功为数据库启用 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 登录名)这一事实引起的.如果您在创建数据库时使用本地帐户 PC1Fred，然后将数据库复制/附加到 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 PC1Fred 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 映射的 他的 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屋！
