Hello,
I have two database servers that I am interested in sending messeges between them. I am not interested in security or encryption at this point, only a simple message sending and recieving.
Can someone please provide simple scripts for object setup (certificates, queues etc) and message send and recieve ?
a link to such a script will be great as well. I am just a bit lost understanding all the new concepts (certificates, service, service binding.....)
Thank you
I.
The Service Listing Manager tool from https://blogs.msdn.com/remusrusanu/archive/2006/04/07/571066.aspx can be used to set this up. It will do all the necesary settings for you, including creating and configuring the endpoints, setting up the routes and setting up security. Give it a try and see if still things look complex after you use it :)
HTH,
~ Remus
|||
I installed the tool and followed the instructions but all it did was to create a document with the service name and endpoint. what do I do from here ?
Thanks
I.
|||
Open the created document, connect to the other machine and select the 'Import' option.
HTH,
~ Remus
|||
Thanks Remus,
That was very helpful. now - I know how to send and recieve messages localy on the same instance. how do I send and recieve messages between two different instances ?
Thank you
I.
|||
The tool does it. Repeat the same steps as for local instance, but simply connect to a different instance when running the Import step. It will do all the necesary setup steps (creat an endpoint, setup endpoint security, setup transport routes in the database etc).
HTH,
~Remus
|||Thanks but I was talking about the Begin Dialog Conversation and recieve commands. is it any different than recieving or sending a message localy ?|||
No differences there. Servicve Broker enables an application designed localy to work distributed w/o any changes.
HTH,
~ Remus
|||
then I guess I have a syntax error...
I am using this code to send message from one instance to another :
BEGIN TRANSACTION ;
GO
DECLARE @.message XML ;
SET @.message = N'<message>Hello, World!</message>' ;
DECLARE @.conversationHandle UNIQUEIDENTIFIER ;
BEGIN DIALOG CONVERSATION @.conversationHandle
FROM SERVICE InitiatorService
TO SERVICE 'tcp://TCA02SQL603:4037','3771F429-0111-488C-9D0E-080226B1DD8F'
ON CONTRACT HelloWorldContract ;
SEND ON CONVERSATION @.conversationHandle
MESSAGE TYPE HelloWorldMessage
(@.message) ;
SET @.message = N'<message>Goodbye!</message>' ;
SEND ON CONVERSATION @.conversationHandle
MESSAGE TYPE HelloWorldMessage
(@.message) ;
END CONVERSATION @.conversationHandle ;
the message doesn't arrive and in the sys.transmission_queue table I get the error :
'The target service name could not be found. Ensure that the service name is specified correctly and/or the routing information has been supplied.'
|||
The error you mention in the sys.transmission_queue indicates that there is no route for the service named 'tcp://TCA02SQL603:4037'. Perhaps you used the route address instead of the service name? BEGIN DIALOG takes in the name of the target service (e.g. 'TargetService').
HTH,
~ Remus
|||
OK, I indeed used the route address instead of the service name so I replaced it :
BEGIN DIALOG CONVERSATION @.conversationHandle
FROM SERVICE InitiatorService
TO SERVICE 'TargetService','3771F429-0111-488C-9D0E-080226B1DD8F'
ON CONTRACT HelloWorldContract ;
However, I still get the same message. I must specify that in sys.transmission_queue the
to_service_name column = 'TargetService,3771F429-0111-488C-9D0E-080226B1DD8F'
and the 'to_broker_instance' is empty.
|||
This means you had a typo in the BEGIN DIALOG statement and you actually used TO SERVICE 'TargetService, 3771F...' instead of 'TargetService','3771F...'. You should see 'TargetService' in to_service_name column and '3771F..' in to_broker_instance column.
Also, make sure the row in sys.transmission_queue is the one from your lastes actual BEGIN DIALOG, and not left there from previous attempts. To clean up failed attempts, use END CONVERSATION ... WITH CLEANUP
HTH,
~ Remus
|||
Thanks Remus, that really helped.
I am getting a different error message now :
Connection handshake failed. The login 'NT AUTHORITY\ANONYMOUS LOGON' does not have CONNECT permission on the endpoint. State 84.
|||
I think I deleted your post by mistake.
 Ilan D wrote: |
|
| ok. for some reason 'End conversation @.CoversationHandle WITH CLEANUP' doesnt clean up the sys.transmission_queue table. any suggestions ? I tried to make sure that I use ',' between service and instance. it's really hard to tell which error message I got because the table doesn't clean up... but I believe that the error message I am getting right now is 'The private key for the security certificate bound to the database principal (ID 1) is password protected. Password protected private keys are not supported for use with secure dialogs.' |
You need to cleanup the particular @.conversationHandle that shows in the conversation_handle column in sys.transmission_queue. You need to do this individually for each conversation you wish to clean up.
The error indicates that the 'dbo' certificate is encrypted by password and the database master key encryption is missing. I assume you used the Service Lsiting Manager tool to setup the certificates. I believe the problem then lieas with 'dbo' having some aditional certificates marked as 'active for begin dialog' that are not the ones deployed by the tool. Lookup in sys.certificates what other certificates are owned by 'dbo' and mark them as not active for begin dialog (ALTER CERTIFICATE [...] WITH ACTIVE FOR BEGIN_DIALOG = OFF, see http://msdn2.microsoft.com/en-us/library/ms189511.aspx)
HTH,
~ Remus
|||
I'd recommend to use certificate based authentication for the endpoints, is easier to get it right.
The error you see is because the NTLM/Kerberos authentication ends up with the anonymous login. The best document to solve these kind of issues is this: http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerberr.mspx
Remeber that the accounts being authenticated are those of that are actually running the two SQL Server services. If the SQL Server instances are running as LocalSystem or NETWORK SERVICE, then you must register the SPN 'MSSQLSvc/<machinename>:<brokerport>' for Kerberos to work (use a tool like setspn.exe, available at MS download center).
HTH,
~ Remus
