Service Broker problems
-
Hi,
After four days of trying i'm losing the will to live...
I'm using windows authentication for both servers with sql server running under the same domain account on both boxes.
My initiator sends and my target says that: This message could not be delivered because the user with ID 0 in database ID 9 does not have permission to send to the service.
As i understand it ID o is the sa account. I've checked that the servicename is the correct one in the right case. I just can't see why sa is involved.
Can anyone help me?
Thank you.
-
Running:
GRANT
CONTROL ON SERVICE ::ErrorLoggerReceiverService TO Public
On the target fixed this error.
Any ideas why i it works ok on two boxes but not on two instances on the same box? I am using different ports.
Thanks in advance.
-
Try:
GRANT CONTROL ON SERVICE ::ErrorLoggerReceiverService TO SELF
If it works I'll explain why.
Christopher Ford
-
Oh... I just re-read your message.
You need to make your sending database trusted.
ALTER DATABASE <DB_NAME> SET TRUSTWORTHY ON
Christopher Ford
-
I have trustworthy on both instances.
On my initiator i get the error: an error occurred while receiving data: (error not found).
On my target i get the error: Connection handshake failed. An OS call found (error not found) state 67.
Both services run under the same account which is an administrator on the box and sa on each instance.
Any ideas anyone...?
Thanks.
-
Did you try this as well?
GRANT CONTROL ON SERVICE ::ErrorLoggerReceiverService TO SELF
Instead of using public?
Also, It is pretty frustrating trying to get your Service Broker up and running the first time, but once you get it, the light bulb comes on and you wonder why it wasn't so clear cut in the documentation.
Is there anyway you can post more of the code you used to set up the broker service on both instances?
Christopher Ford
-
Yes tried that but still get the same.
remember that both instances run as cc\agr-admin and is an administrator on the box and the code runs fine if on different boxes.
Here is the initiator code:
use
master
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'SourceDB') DROP DATABASE [SourceDB]
Create
Database SourceDB
use SourceDB
ALTER AUTHORIZATION ON DATABASE::[SourceDB] TO [sa];
ALTER
DATABASE SourceDB SET TRUSTWORTHY ON;
ALTER
DATABASE SourceDB SET ENABLE_BROKER;
CREATE MESSAGE TYPE ErrorLoggerMessage VALIDATION = NONE;
CREATE
CONTRACT ErrorLoggerContract (ErrorLoggerMessage SENT BY INITIATOR);
CREATE
QUEUE ErrorLoggerSendQueue WITH status = ON;
CREATE
SERVICE ErrorLoggerSendService ON QUEUE ErrorLoggerSendQueue (ErrorLoggerContract);
CREATE
ROUTE ErrorLoggerSendRoute WITH SERVICE_NAME = 'ErrorLoggerReceiverService',
BROKER_INSTANCE
= '42665FF5-FE45-4CC5-BE74-AC91DD043D4C',
ADDRESS
= 'TCP://5.0.8.195:4024';
IF
EXISTS (SELECT name from sys.service_broker_endpoints where name = N'ServiceBrokerEndpoint') DROP endpoint ServiceBrokerEndpoint
CREATE
ENDPOINT ServiceBrokerEndPoint STATE=STARTED AS TCP (LISTENER_PORT = 4022) FOR SERVICE_BROKER
(
AUTHENTICATION
= WINDOWS ,
ENCRYPTION
= DISABLED
);
use
master;
GRANT CONNECT ON ENDPOINT::ServiceBrokerEndPoint To [cc\agr-admin];
_______________________________________________________________
Here is the target code:
use
master;
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'TargetDB') DROP DATABASE [TargetDB]
create
database TargetDB;
use
TargetDB;
ALTER DATABASE TargetDB SET TRUSTWORTHY ON;
ALTER
DATABASE TargetDB SET ENABLE_BROKER;
CREATE MESSAGE TYPE ErrorLoggerMessage VALIDATION = NONE;
CREATE
CONTRACT ErrorLoggerContract (ErrorLoggerMessage SENT BY INITIATOR);
CREATE
QUEUE ErrorLoggerReceiverQueue WITH status = ON;
CREATE
SERVICE ErrorLoggerReceiverService ON QUEUE ErrorLoggerReceiverQueue (ErrorLoggerContract);
IF
EXISTS (SELECT name from sys.service_broker_endpoints where name = N'ServiceBrokerEndpoint') DROP endpoint ServiceBrokerEndpoint
CREATE
ENDPOINT ServiceBrokerEndPoint STATE=STARTED AS TCP (LISTENER_PORT = 4024) FOR SERVICE_BROKER
(
AUTHENTICATION
= WINDOWS,
ENCRYPTION
= DISABLED
);
GRANT
SEND ON SERVICE::ErrorLoggerReceiverService To [cc\agr-admin];
GRANT
CONTROL ON SERVICE ::ErrorLoggerReceiverService TO Public
use
master;
GRANT
CONNECT ON ENDPOINT::ServiceBrokerEndPoint To [cc\agr-admin];
SELECT
CONVERT(NVARCHAR(max), message_body) AS message FROM ErrorLoggerReceiverQueue;
RECEIVE
CONVERT(NVARCHAR(max), message_body) AS message FROM ErrorLoggerReceiverQueue;
____________________________________________________________
Here is the send message code:
use
sourcedb
go
declare
@ConversationHandle uniqueidentifier
begin
transaction
begin
dialog @ConversationHandle
from
service ErrorLoggerSendService
to
service 'ErrorLoggerReceiverService','42665FF5-FE45-4CC5-BE74-AC91DD043D4C'
on
contract ErrorLoggerContract with encryption = off;
send
on conversation @ConversationHandle
message type ErrorLoggerMessage ('test')
commit
_____________________________________________________
On the initiator i get: An error occurred while receiving data: '10054(error not found)'.
On the target i get: Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.
Thanks for trying to help me ....
-
Any more ideas anyone?
Thanks
-
did you apply Service Pack 2 for SQL 2005 yet?
It appears you've set everything up correctly.
You stated that it works from one machine to another machine. But that it fails when you're running two instances of SQL on the same machine.
correct?
Christopher Ford
-
Hi Christopher,
I haven't applied the service pack so will do that.
Yes, it works across two boxes but not on two instances on the same box.
Thanks.
-
Is the SQL Server Browser service running and set to automatic startup?
Christopher Ford
-
Hi,
Yes the service is running under local system.
-
- did you check the Troubleshooting Service Broker (SSB) script ?
- did you check the sqlserver errorlog ?
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Yes I have checked the script and the errorlog - which just lists the error message as i have previously posted.
Remember that the message has been sent and is hitting the target as both display error messages.
-
did you try
GRANT SEND ON SERVICE ::ErrorLoggerReceiverService TO Public
in both db's !!
When only setting up transport security , you need to grant send authority to public for the local SSB-service in the userdatabases
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply