Post reply

Linked Server usually does not work, sometimes it will connect

  • December 10, 2021 at 7:35 am

    #3962283

    I've a problems with Linked Servers I created for particular farm. I've setup many linked servers this way before and never seen such problem.

    The two servers are on farms on the opposites sites of the continent, but connecting directly from A to B in SSMS works without practically any delays at all.

    Linked server configuration is following:

    • Provider: SQLNLI 11.0
    • Data source: B's server SPN
    • Provider string: Integrated Security=SSPI;

    Security is using login's current security context

    Sometimes they work fine, but usually connection will fail with any combinations of following error messages:

    Login failed for user 'NT AUTHORITY\ANNYMOUS LOGON'OLE DB provider "SQLNCLI11" for linked server "TEST" returned message "Unable to complete login process due to delay in opening server connection".

    OLE DB provider "SQLNCLI11" for linked server "TEST" returned message "Invalid connection string attribute". (.Net SqlClient Data Provider)

    Here's things I checked -

    For all A and B servers SPNs are created

    All A Servers, service accounts have delegation rights to B SPNs

    Remote sessions are using Kerberos when tested with SYS.DM_EXEC_CONNECTIONS.

     

    Like I mentioned above to most unusual thing about it is that it will occasionally work, and I'll be able to query tables of B servers, even from a remote (not A) machine. So it does not look like to me like a problem with configuration, but I am not sure where to take it from here.

    Source is 11 and destination is 15 if that matters

    EDIT: For fun I created same linked servers on server in farm C. Here both work flawlessly. So issue seems to be somewhere with farm A server. I'll have to involve local team there.

    • This topic was modified 2 years, 11 months ago by  jankovic.

  • December 11, 2021 at 8:10 am

    #3962631

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • December 15, 2021 at 10:41 am

    #3963829

    Is there a difference between how SSMS connects to remote server and how Linked Server does it?

    It works first way, but not the other

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply