Anonymous558364


I am trying to send a message between to SQL Server 2005 instances on two different machines. I have checked all my routes and all my objects appear to be setup correctly. However, when running Profiler on the target machine, I receive the "This message has been dropped because the TO service could not be found. Service name: "[tcp://mydomain.com/TARGET/MyService]". Message origin: "Transport". This is my activated stored procedure that is sending the message to the target service. I am using certificate security. Any help appreciated....

CREATE PROCEDURE [usp_ProcessMessage]

AS

BEGIN

SET NOCOUNT ON;

DECLARE @conversation_handle uniqueidentifier

DECLARE @message_body AS VARBINARY(MAX)

WHILE (1=1)

BEGIN

BEGIN TRANSACTION;

WAITFOR(RECEIVE TOP (1)

@conversation_handle = conversation_handle,

@message_body = message_body

FROM [tcp://mydomain.com/INITIATE/MyQueue]

), TIMEOUT 1000;

IF (@@ROWCOUNT = 0)

BEGIN

COMMIT;

BREAK;

END

END CONVERSATION @conversation_handle

IF @message_body IS NOT NULL

BEGIN

BEGIN DIALOG CONVERSATION @conversation_handle

FROM SERVICE [tcp://mydomain.com/INITIATE/MyService]

TO SERVICE '[tcp://mydomain.com/TARGET/MyService]'

ON CONTRACT [tcp://mydomain.com/INITIATE/MyMessage/v1.0]

WITH ENCRYPTION = ON, LIFETIME = 600;

SEND ON CONVERSATION @conversation_handle

MESSAGE TYPE [tcp://mydomain.com/TARGET/VisitMessage]

(@message_body);

END

COMMIT;

END

END

GO

My endpoints are created like so:

CREATE ENDPOINT MyEndpoint

STATE = STARTED

AS TCP

(

LISTENER_PORT = 4022

)

FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE MasterCertificate)

GO

GRANT CONNECT TO CertOwner

GRANT CONNECT ON ENDPOINT::MyEndpoint TO CertOwner

GO

And my routes like so:

GRANT SEND ON SERVICE::[tcp://mydomain.com/INITIATE/MyService] TO CertOwner

GO

CREATE REMOTE SERVICE BINDING [MyCertificateBinding]

TO SERVICE '[tcp://mydomain.com/TARGET/MyService]'

WITH USER = CertOwner,

ANONYMOUS=OFF

CREATE ROUTE [tcp://mydomain.com/INITIATE/MyRoute]

WITH SERVICE_NAME = '[tcp://mydomain.com/TARGET/MyService]',

BROKER_INSTANCE = N'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx',

ADDRESS = N'TCP://xxx.xx.xx.xx:4022'

GO




Re: Service Broker "TO service could not be found" Message origin: "Transport"

Remus Rusanu


Just a guess, but probably your service is named 'tcp://mydomain.com/TARGET/MyService', not '[tcp://mydomain.com/TARGET/MyService]'





Re: Service Broker "TO service could not be found" Message origin: "Transport"

Anonymous

OK, I thought you might be onto something that I missed. While that may have been a problem - it didn't fix the issue. I still get the same message in Profiler on the target machine and no reasons in the transmission_status (empty). Any other ideas...anyone




Re: Service Broker "TO service could not be found" Message origin: "Transport"

Remus Rusanu

Make sure you get rid of all the old messages pending from your previous attempts, since they have the wrong service name. Use either END CONVERSATION ... WITH CLEANUP to delete the wrong conversations one by one or use ALTER DATABASE ... SET NEW_BROKER to nuke all of them at once (in the sender's database).






Re: Service Broker "TO service could not be found" Message origin: "Transport"

Anonymous

OK, well I'm not sure if this is promising or not, but now my trace shows:

This message could not be delivered because the Service Contract could not be found. Service Contract: 'tcp://mydomain.com/INITATE/MyMessage/v1.0.

I see this contract defined on both the initiator and target machines.





Re: Service Broker "TO service could not be found" Message origin: "Transport"

Remus Rusanu

Make sure the event occurs in the database you expect (check dbid column in profiler), and make sure the case is correct. Services, contracs and message type names are always compared using a case sensitive collation, no matter the server or database collation.




Re: Service Broker "TO service could not be found" Message origin: "Transport"

Anonymous

OK, so I seem to have solved the problem by applying the following to the Target:

GRANT CONTROL ON SERVICE :: [tcp://mydomain.com/TARGET/MyService] TO PUBLIC

GO

ALTER DATABASE [Target] SET TRUSTWORTHY ON;

and this to the Initiator:

ALTER DATABASE [Initiator] SET TRUSTWORTHY ON;

However, I'm wondering if all this is necessary. Opening up security always makes me nervous, so what is the minimum necessary to allow communication between two instances across machine/network boundaries





Re: Service Broker "TO service could not be found" Message origin: "Transport"

Remus Rusanu

None of these is actually necessary. And none of these can actually explain the profiler traced message undeliverable event you described before.

My suspition is that you are doing now unsecure dialogs, for which SEND permission is required on the target service by the [Public] role. By granting the much more powerfull CONTROL permission, you've implicitly granted SEND (as SEND is implied by CONTROL). The reason why you are doing unsecure dialogs is probably related to the service name typo (the [ and ] brackets inside the already quoted string) which you probably still have in the originally create REMOTE SERVICE BINDING (RSB) object on the sender. This makes the RSB in fact associated with the wrong service name and no RSB actually associated with your desired name. I'd recommend you start from scratch and make sure you only add the needed object. The Service Listing Manager from www.codeplex.com/slm can help you get the security part right.






Re: Service Broker "TO service could not be found" Message origin: "Transport"

Anonymous

OK, you're probably right; I am going to use the SLM to iron this out and see what the problem is. Everything looks correct in terms of the remote service binding that you mention - there are no quotes around the service. I'll let you know my findings.