none
Linked server error - OLE DB provider "SQLNCLI" for linked server "<Linked Server Name>" returned message "Login timeout expired".

    Question

  • Hi -
    I have a linked server that set configured to connect to remote SQL Server using a remote SQL Server login. When I run the T-SQL statement to get the data from the remote server. The query fails with the following error message.

    Named Pipes Provider: Could not open a connection to SQL Server [5].
    OLE DB provider "SQLNCLI" for linked server "<Linked Server Name>" returned message "Login timeout expired".
    OLE DB provider "SQLNCLI" for linked server "<Linked Server Name>" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".(Microsoft SQL Server,Error:5)

    I've checked that the remote SQL Server has the remote login(Used by the linked server) defined on the source server with appropriate access. Can anyone help me to resolve this issue?

    Thanks in Advance.
    Monday, June 29, 2009 9:30 AM

Answers

  • Hi,

    Based on your description, you cannot connect to one of your linked servers. However, you could successfully connect to another one. Thus, I think the problem is focus on the Network configuration of the linked server that you failed to connect to.  Try to use the following troubleshooting methods on the linked server.
    1. Select "Using both TCP/IP and named pipes" in SQL Server 2005 Surface Area Configuration.
    2. In SQL Server Configuration Manager, make sure that TCP/IP is enabled for the Network protocol.
    3. If you turn on the Firewall, make sure the TCP port 1433 is in the Firewall exception list.

    If you have any more questions, please let me know.

    Thanks.
    ***Xiao Min Tan***Microsoft Online Community***
    Wednesday, July 01, 2009 6:50 AM
    Moderator

All replies

  • This Error happend when the remote server cannot response the login request, the default setting for this behavior is 20 seconds. Server under high utilization can caused this error or server down.
    Monday, June 29, 2009 9:48 AM
  • Thanks. But my remote server is not down and the utilization is normal. I have another remote SQL Server with the default remote login timeout set to 20 seconds and I'm able to run remote queries against that server using linked server without any issues. So I believe it is not the reason.
    Monday, June 29, 2009 10:06 AM
  • Hi,

    Based on your description, you cannot connect to one of your linked servers. However, you could successfully connect to another one. Thus, I think the problem is focus on the Network configuration of the linked server that you failed to connect to.  Try to use the following troubleshooting methods on the linked server.
    1. Select "Using both TCP/IP and named pipes" in SQL Server 2005 Surface Area Configuration.
    2. In SQL Server Configuration Manager, make sure that TCP/IP is enabled for the Network protocol.
    3. If you turn on the Firewall, make sure the TCP port 1433 is in the Firewall exception list.

    If you have any more questions, please let me know.

    Thanks.
    ***Xiao Min Tan***Microsoft Online Community***
    Wednesday, July 01, 2009 6:50 AM
    Moderator
  • Hi,

    We are marking this issue as ‘Answered’ since the previous thread  provides a solution to the problem you encountered.

    If you have time to review it and have additional input please feel free to uncheck the ‘Answer’ mark. We would also appreciate it if you can share any extra or alternative solutions you may know. This can really help other community members search and find answers for similar questions.

    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Friday, July 10, 2009 9:22 AM
    Moderator
  • In case of my linked server a sql 2000 , how do I try the above? I'm trying to set up a linked server connection from SS2005 to SS2000. I see TP/IP , named pipes enabled in network ultility. I set up a common login on ss2000 server. However while testing connection i get the below error.

    Named Pipes Provider: Could not open a connection to SQL Server [2].
    OLE DB provider "SQLNCLI" for linked server "TEST1" returned message "Login timeout expired".
    OLE DB provider "SQLNCLI" for linked server "TEST1" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.". (Microsoft SQL Server, Error: 2)

    Appreciate your response.

    Thanks

    Sri


    • Edited by sri490 Friday, March 23, 2012 9:42 PM
    Friday, March 23, 2012 8:58 PM