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.

Answer 1

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 Snippet

telnet.exe target-machine target-broker-port

 

 

Once telnet connection is successfull, broker should work as well.

Answer 2

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?

Answer 3

Looks like transport authentication problem. Please provide the commands you used to create broker endpoints, commands used to grant connect permissions on the endpoints, and service accounts that both your Sql Server instances run as.

 

Answer 4

On initiator side:

Code Snippet

USE master;
GO

CREATE ENDPOINT InstInitiatorEndpoint
 STATE = STARTED
 AS TCP ( LISTENER_PORT = 4022 )
 FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS [KERBEROS] );
GO

EXEC sp_grantlogin [MyDomain\MyUser]
GO

CREATE DATABASE InstInitiatorDB;
GO

USE InstInitiatorDB;
GO

-- enable SSB on database
ALTER DATABASE InstInitiatorDB
 SET ENABLE_BROKER
 WITH ROLLBACK IMMEDIATE
GO

ALTER DATABASE InstInitiatorDB SET TRUSTWORTHY ON
GO

CREATE MASTER KEY
       ENCRYPTION BY PASSWORD = N'MyPassword1234';
GO
GO
CREATE USER InitiatorUser WITHOUT LOGIN;
GO

CREATE 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';
GO

CREATE 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;
GO

CREATE CONTRACT [SimpleContract]
      ([RequestMessage]
         SENT BY INITIATOR,
       [ReplyMessage]
         SENT BY TARGET
      );
GO

CREATE 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;
GO

DECLARE @Cmd NVARCHAR(4000);
SET @Cmd = N'USE InstInitiatorDB;
CREATE ROUTE InstTargetRoute
WITH SERVICE_NAME =
       N''TargetService'',
     ADDRESS = N''TCP://project2:4022'';';
EXEC (@Cmd);
GO

DECLARE @Cmd NVARCHAR(4000);
SET @Cmd = N'USE msdb
CREATE ROUTE InstInitiatorRoute
WITH SERVICE_NAME =
       N''InitiatorService'',
     ADDRESS = N''LOCAL''';
EXEC (@Cmd);
GO

CREATE REMOTE SERVICE BINDING TargetBinding
    TO SERVICE 'TargetService'
    WITH USER = TargetUser,
         ANONYMOUS = ON ;
GO

 

 

 

on the target machine:

Code Snippet

USE master;
GO

CREATE ENDPOINT InstTargetEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4022 )
FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS [KERBEROS]);
GO

EXEC sp_grantlogin [MyDomainL\MyUser]
GO

CREATE DATABASE InstTargetDB;
GO

ALTER DATABASE InstTargetDB SET TRUSTWORTHY ON
GO

USE InstTargetDB;
GO

CREATE MASTER KEY
       ENCRYPTION BY PASSWORD = N'MyPassword1234';
GO

CREATE USER TargetUser WITHOUT LOGIN;
GO

CREATE 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';
GO

CREATE MESSAGE TYPE [RequestMessage]
       VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [ReplyMessage]
       VALIDATION = WELL_FORMED_XML;
GO

CREATE CONTRACT SimpleContract
      (RequestMessage
         SENT BY INITIATOR,
       ReplyMessage
         SENT BY TARGET
      );
GO

CREATE 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;
GO

CREATE USER InitiatorUser WITHOUT LOGIN;
GO

CREATE CERTIFICATE InstInitiatorCertificate
   AUTHORIZATION InitiatorUser
   FROM FILE =
N'C:\Users\MyUser\Documents\certs\InstInitiatorCertificate.cer';
GO

DECLARE @Cmd NVARCHAR(4000);
SET @Cmd = N'USE InstTargetDB;
CREATE ROUTE InstInitiatorRoute
WITH SERVICE_NAME =
       N''InitiatorService'',
     ADDRESS = N''TCP://project1:4022'';';
EXEC (@Cmd);
GO

DECLARE @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 ;
GO

CREATE 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

 

Answer 5

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 Snippet

GRANT 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 Snippet

GRANT CONNECT ON ENDPOINT::InstTargetEndpoint TO [MyDomain\InitiatorHost$]

 

 

...and vice-versa.

 

I hope you get the idea.

 

Answer 6

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 Snippet GRANT 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 Snippet

GRANT CONNECT ON ENDPOINT::InstTargetEndpoint TO [MyDomain\MyUser$]

 

 

(added the $) and this completed successfully, but only after I also did

Code Snippet

CREATE LOGIN  [MyDomain\MyUser$]  FROM WINDOWS

 

 

Anyhow - 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

Answer 7

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!

Answer 8

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.

 

Answer 9

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 Snippet

GRANT CONNECT ON ENDPOINT::InstInitiatorEndpoint TO [MyDomain\TargetMachine$]

 

and on the target machine:

Code Snippet

GRANT 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.

Answer 10

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.

 

 

Answer 11

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.

Answer 12

Once I re-started SQL Server Management Studio and the Profiler - all these messages ceased to appear, and even after exchanging messages between the sides - there are no more error messages anywhere.

 

Thanks

Answer 13

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 .