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

    Monday, October 20, 2008 7:12 AM

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 Snippet
    telnet.exe target-machine target-broker-port

     

     

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

    Monday, October 20, 2008 4:01 PM
  •  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.

    Tuesday, October 21, 2008 7:06 PM

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 Snippet
    telnet.exe target-machine target-broker-port

     

     

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

    Monday, October 20, 2008 4:01 PM
  • 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?

    Monday, October 20, 2008 6:19 PM
  • 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.

     

    Monday, October 20, 2008 6:45 PM
  • 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

     

    Monday, October 20, 2008 7:36 PM
  • 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.

     

    Monday, October 20, 2008 9:13 PM
  • 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

    Tuesday, October 21, 2008 12:02 AM
  • OK, I think you've gone too far. Smile 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!

    Tuesday, October 21, 2008 5:38 PM
  • 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.

     

    Tuesday, October 21, 2008 6:43 PM
  •  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.

    Tuesday, October 21, 2008 7:06 PM
  • 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.

     

     

    Tuesday, October 21, 2008 8:59 PM
  •  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.

    Tuesday, October 21, 2008 9:30 PM
  • 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

    Tuesday, October 21, 2008 10:04 PM