Answered by:
Connection attempt failed with error: 10060

Question
-
I used the example "Completing a Conversation Between Instances" from SQL Server 2008 Books Online, but when I try to send a message, it does not go through, and in sys.transmission_queue.transmission_status I see the above error.
I tried this between two different computers, and also between a desktop and a VPC running on it, I tried various ports, but nothing helps.
On the profiler I see no activity on the target side.
Thanks for any help.
Answers
-
This is a socket timeout error. Make sure that the target machine listens on the specified TCP port (e.g. Sql Server TCP Provider is enabled) and there are no firewalls on the way blocking broker connections. Since this is not a broker-specific issue, so it may be easier to just use telnet for troubleshooting. Open a shell on the initiator machine and try to execute the following command.
Code Snippettelnet.exe target-machine target-broker-portOnce telnet connection is successfull, broker should work as well.
-
rdoronr wrote: On the initiator machine: all the services log on as 'NT AUTHORITY\NETWORK SERVICE'
On the target machine: all the services log on as 'NT AUTHORITY\NETWORK SERVICE' except the SQL Server browser which logs on as 'NT AUTHORITY\LOCAL SERVICE'.It means that when going over the network, Broker will use machine credentials. Both machines need to be in the same domain (or in domains with trusted relationship). Let's say the domain is MyDomain, and the machine names are InitiatorMachine and TargetMachine. You need to execute the following statements to make connectivity between server instances possible (again, right now we're talking about the transport/network level connectivity only).
On the InitiatorMachine:
Code SnippetGRANT CONNECT ON ENDPOINT::InstInitiatorEndpoint TO [MyDomain\TargetMachine$]and on the target machine:
Code SnippetGRANT CONNECT ON ENDPOINT::InstTargetEndpoint TO [MyDomain\InitiatorMachine$]
After these steps it should work fine. If it still does not, please provide the error in transmission status from sys.transmission_queue, as well as errors in profiler traces from both servers with all events from Broker group enabled plus additionally Audit Broker Login and Audit Broker Conversation from the Security Audit group.
All replies
-
This is a socket timeout error. Make sure that the target machine listens on the specified TCP port (e.g. Sql Server TCP Provider is enabled) and there are no firewalls on the way blocking broker connections. Since this is not a broker-specific issue, so it may be easier to just use telnet for troubleshooting. Open a shell on the initiator machine and try to execute the following command.
Code Snippettelnet.exe target-machine target-broker-portOnce telnet connection is successfull, broker should work as well.
-
Thanks for the advice.
There was indeed a port mismatch.
Now I advanced to a new set of errors:
On initiator side the errors are:
Connection handshake failed. An OS call failed: (80090303) 0x80090303(failed to retrieve text for this error. Reason: 15105). State 66.
This message could not be delivered because the security context could not be retrieved.
On target side:
An error occurred while receiving data: 10054
Any ideas?
-
-
On initiator side:
Code SnippetUSE master;
GOCREATE ENDPOINT InstInitiatorEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4022 )
FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS [KERBEROS] );
GOEXEC sp_grantlogin [MyDomain\MyUser]
GOCREATE DATABASE InstInitiatorDB;
GOUSE InstInitiatorDB;
GO-- enable SSB on database
ALTER DATABASE InstInitiatorDB
SET ENABLE_BROKER
WITH ROLLBACK IMMEDIATE
GOALTER DATABASE InstInitiatorDB SET TRUSTWORTHY ON
GOCREATE MASTER KEY
ENCRYPTION BY PASSWORD = N'MyPassword1234';
GO
GO
CREATE USER InitiatorUser WITHOUT LOGIN;
GOCREATE CERTIFICATE InstInitiatorCertificate
AUTHORIZATION InitiatorUser
WITH SUBJECT = N'Initiator Certificate',
EXPIRY_DATE = N'12/31/2010';BACKUP CERTIFICATE InstInitiatorCertificate
TO FILE =
N'C:\public\InstInitiatorCertificate.cer';
GOCREATE USER TargetUser WITHOUT LOGIN;
CREATE CERTIFICATE InstTargetCertificate
AUTHORIZATION TargetUser
FROM FILE =
N'C:\storedcerts\InstTargetCertificate.cer'
GO
CREATE MESSAGE TYPE [RequestMessage]
VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [ReplyMessage]
VALIDATION = WELL_FORMED_XML;
GOCREATE CONTRACT [SimpleContract]
([RequestMessage]
SENT BY INITIATOR,
[ReplyMessage]
SENT BY TARGET
);
GOCREATE QUEUE InstInitiatorQueue;
CREATE SERVICE InitiatorService
AUTHORIZATION InitiatorUser
ON QUEUE InstInitiatorQueue;
GO-- Make this user the owner of the initiator service.
ALTER AUTHORIZATION ON SERVICE::InitiatorService TO InitiatorUser
GO-- Grant send on the service to public.
GRANT SEND ON SERVICE::InitiatorService TO public ;
GO-- Grant RECEIVE permissions on the queue for the service.
-- This allows the local user to begin conversations from
-- services that use the queue.
GRANT RECEIVE ON InstInitiatorQueue TO public;
GODECLARE @Cmd NVARCHAR(4000);
SET @Cmd = N'USE InstInitiatorDB;
CREATE ROUTE InstTargetRoute
WITH SERVICE_NAME =
N''TargetService'',
ADDRESS = N''TCP://project2:4022'';';
EXEC (@Cmd);
GODECLARE @Cmd NVARCHAR(4000);
SET @Cmd = N'USE msdb
CREATE ROUTE InstInitiatorRoute
WITH SERVICE_NAME =
N''InitiatorService'',
ADDRESS = N''LOCAL''';
EXEC (@Cmd);
GOCREATE REMOTE SERVICE BINDING TargetBinding
TO SERVICE 'TargetService'
WITH USER = TargetUser,
ANONYMOUS = ON ;
GOon the target machine:
Code SnippetUSE master;
GOCREATE ENDPOINT InstTargetEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4022 )
FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS [KERBEROS]);
GOEXEC sp_grantlogin [MyDomainL\MyUser]
GOCREATE DATABASE InstTargetDB;
GOALTER DATABASE InstTargetDB SET TRUSTWORTHY ON
GOUSE InstTargetDB;
GOCREATE MASTER KEY
ENCRYPTION BY PASSWORD = N'MyPassword1234';
GOCREATE USER TargetUser WITHOUT LOGIN;
GOCREATE CERTIFICATE InstTargetCertificate
AUTHORIZATION TargetUser
WITH SUBJECT = 'Target Certificate',
EXPIRY_DATE = N'12/31/2010';BACKUP CERTIFICATE InstTargetCertificate
TO FILE =
N'C:\windows\temp\InstTargetCertificate.cer';
GOCREATE MESSAGE TYPE [RequestMessage]
VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [ReplyMessage]
VALIDATION = WELL_FORMED_XML;
GOCREATE CONTRACT SimpleContract
(RequestMessage
SENT BY INITIATOR,
ReplyMessage
SENT BY TARGET
);
GOCREATE QUEUE InstTargetQueue;
CREATE SERVICE TargetService
AUTHORIZATION TargetUser
ON QUEUE InstTargetQueue
(SimpleContract);
GO-- Grant receive on the target queue to the local user.
GRANT RECEIVE ON InstTargetQueue TO public;
GOCREATE USER InitiatorUser WITHOUT LOGIN;
GOCREATE CERTIFICATE InstInitiatorCertificate
AUTHORIZATION InitiatorUser
FROM FILE =
N'C:\Users\MyUser\Documents\certs\InstInitiatorCertificate.cer';
GODECLARE @Cmd NVARCHAR(4000);
SET @Cmd = N'USE InstTargetDB;
CREATE ROUTE InstInitiatorRoute
WITH SERVICE_NAME =
N''InitiatorService'',
ADDRESS = N''TCP://project1:4022'';';
EXEC (@Cmd);
GODECLARE @Cmd NVARCHAR(4000);
SET @Cmd = N'USE msdb
CREATE ROUTE InstTargetRoute
WITH SERVICE_NAME =
N''TargetService'',
ADDRESS = N''LOCAL''';
EXEC (@Cmd);
GO-- Grant send on the service to public.
GRANT SEND ON SERVICE::TargetService TO public ;
GOCREATE REMOTE SERVICE BINDING InitiatorBinding
TO SERVICE N'InitiatorService'
WITH USER = InitiatorUser;
GO
On both machines the databases are owned by the same user (displayed above as MyDomain\MyUser).Thanks
-
You are forcing Sql Server to use Kerberos to authenticate each other. For that to work, you would need to manually set SPNs for broker ports. I doubt you're doing it, so instead I would suggest to use (AUTHENTICATION = WINDOWS) endpoint option, so that it may fall back to NTLM in case Kerberos authentication is impossible.
The second thing is that I don't see granting connect permission on the endpoints you created. Connect permission is necessary for one Sql Server instance to connect to the other. Don't confuse this with dialog security (for which you're using certificates). This is something completelly different, happening on the server level rather than database level.
For example, when your initiator Sql Server instance runs as MyDomain\MyUser, you need to execute the following statement on your target instance:
Code SnippetGRANT CONNECT ON ENDPOINT::InstTargetEndpoint TO [MyDomain\MyUser]...and vice-versa.
If your initiator Sql Server instance runs as machine account (e.g. NT Authority\Network Service), you need to execute the following statement on your target instance:
Code SnippetGRANT CONNECT ON ENDPOINT::InstTargetEndpoint TO [MyDomain\InitiatorHost$]...and vice-versa.
I hope you get the idea.
-
Thank you, Pawel, for your willingness to help. I really appreciate it.
This is the current status:
Pawel Marciniak wrote: You are forcing Sql Server to use Kerberos to authenticate each other. For that to work, you would need to manually set SPNs for broker ports. I doubt you're doing it, so instead I would suggest to use (AUTHENTICATION = WINDOWS) endpoint option, so that it may fall back to NTLM in case Kerberos authentication is impossible.
OK. I did that.
Pawel Marciniak wrote: The second thing is that I don't see granting connect permission on the endpoints you created. Connect permission is necessary for one Sql Server instance to connect to the other. Don't confuse this with dialog security (for which you're using certificates). This is something completelly different, happening on the server level rather than database level.
For example, when your initiator Sql Server instance runs as MyDomain\MyUser, you need to execute the following statement on your target instance:
Code SnippetGRANT CONNECT ON ENDPOINT::InstTargetEndpoint TO [MyDomain\MyUser]I tried that too, but I got error message saying:
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
So I tried (based on information I saw somewhere else):
Code SnippetGRANT CONNECT ON ENDPOINT::InstTargetEndpoint TO [MyDomain\MyUser$](added the $) and this completed successfully, but only after I also did
Code SnippetCREATE LOGIN [MyDomain\MyUser$] FROM WINDOWSAnyhow - I still get error messages.
I get the same errors on the target like before, and on the initiator I get now in the profiler:
Connection handshake failed. The login MyDomain\PROJECT2$' does not have CONNECT permission on the endpoint. State 84.
This message could not be delivered because the security context could not be retrieved.
where project2 is the name of the target machine
and in the sys.transmission_queue table I get:
Dialog security is unavailable for this conversation because there is no security certificate bound to the database principal (Id: 5). Either create a certificate for the principal, or specify ENCRYPTION = OFF when beginning the conversation.
but as you can see above - I do have a certificate -
OK, I think you've gone too far.
Let's back up a little. Please let me know the service accounts of both your instances. You can find them by running Sql Server Configuration Manager on both machines and checking the "Log On As" column. Once you know the service accounts, that will determine what permissions need to be granted on your endpoint.
Please note that I didn't review the steps you used for certificate setup, so I'm not saying it's correct. However I've seen that you deviated from the published tutorial (e.g. by adding [KERBEROS] to the endpoint creation options). What I would recommend is to follow the tutorial closely, and only after it starts working adjust the setup to your needs. That way you will now which change to the default setup caused problems (i.e. adding [KERBEROS]).
Btw. the dollar sign after an account name means that this is a machine account rather than user account, so there is really no point in trying to add '$' after MyDomain\MyUser. It won't make it work.
Good luck!
-
Thank you, Pawel.
On the initiator machine: all the services log on as 'NT AUTHORITY\NETWORK SERVICE'
On the target machine: all the services log on as 'NT AUTHORITY\NETWORK SERVICE' except the SQL Server browser which logs on as 'NT AUTHORITY\LOCAL SERVICE'.The deviations that I introduced were the results of problems to operate the original example, which prompted me to look all over the web for solutions (that I tried, in vain), including the KERBEROS thing.
-
rdoronr wrote: On the initiator machine: all the services log on as 'NT AUTHORITY\NETWORK SERVICE'
On the target machine: all the services log on as 'NT AUTHORITY\NETWORK SERVICE' except the SQL Server browser which logs on as 'NT AUTHORITY\LOCAL SERVICE'.It means that when going over the network, Broker will use machine credentials. Both machines need to be in the same domain (or in domains with trusted relationship). Let's say the domain is MyDomain, and the machine names are InitiatorMachine and TargetMachine. You need to execute the following statements to make connectivity between server instances possible (again, right now we're talking about the transport/network level connectivity only).
On the InitiatorMachine:
Code SnippetGRANT CONNECT ON ENDPOINT::InstInitiatorEndpoint TO [MyDomain\TargetMachine$]and on the target machine:
Code SnippetGRANT CONNECT ON ENDPOINT::InstTargetEndpoint TO [MyDomain\InitiatorMachine$]
After these steps it should work fine. If it still does not, please provide the error in transmission status from sys.transmission_queue, as well as errors in profiler traces from both servers with all events from Broker group enabled plus additionally Audit Broker Login and Audit Broker Conversation from the Security Audit group.
-
Pawel,
Thank you very much!!
At last the communications works!
These two commands in your last post made the difference.
They are missing from the tutorial example, and initially, when the tutorial had not worked for me - I tried to add all sorts of "solutions" proposed for similar situations on the web, some of which probably only made things worse.
What I did now was to clean up everything, then run the tutorial again from scratch, but adding the above commands to the respective sides.
There is still one oddity, though: while the profiler does not show anything on the target side, the profiler on the initiator side still displays messages:
Broker:Connection An error occurred while receiving data: '64(failed to retrieve text for this error. Reason: 15105)'.
Broker:Message Undeliverable This message could not be delivered because the security context could not be retrieved.
even though in initiator sys.transmission_queue there are no rows at all.
Are these some remnants from before that require some sort of additional cleanup?
Thanks again.
-
rdoronr wrote: There is still one oddity, though: while the profiler does not show anything on the target side, the profiler on the initiator side still displays messages:
Broker:Connection An error occurred while receiving data: '64(failed to retrieve text for this error. Reason: 15105)'.
Broker:Message Undeliverable This message could not be delivered because the security context could not be retrieved.
even though in initiator sys.transmission_queue there are no rows at all.
The first event doesn't necessarily mean there's something wrong. It will appear every time the other side of the connection closes it due to 90 seconds of inactivity.
The second one, however, is a sign of something bad going on. But this is dialog security issue (as opposed to transport security, which we have just successfully sorted out), so for the sake of clarity I would suggest you to open another topic on the forum for the new issue, because they are completely unrelated. But first make sure that it isn't something obvious by looking at Audit Broker Conversation profiler event from the Security Audit group. It should provide more information on why the security context cannot be retrieved.
-
-
I am creating service broker to communicate between 2 different instance on same domain.I ma getting the same error.On the initiator machine: and target machine Ms Sql Server log on as 'NT Service\MSSQLServer.please let me know on whch user the grant connection on endpoint permission to be given.Do we need to have a same user with windows authentication on both instances .