locked
Intermittent "Unable to complete login process due to delay in opening server connection." RRS feed

  • Question

  • I have some queries that get data from a remote linked server and combine that data with data in my own database.  Sometimes the queries run VERY slow (1-4 minutes) when they typically run a few seconds.  But sometimes I get this error message in SSMS:  

    OLE DB provider "SQLNCLI11" for linked server "SQL2014" returned message "Unable to complete login process due to delay in opening server connection".
    Msg 7303, Level 16, State 1, Line 1
    Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "SQL2014".

    Is there a way to determine what is causing the issue?  If so, how do I monitor this issue so I can fix it before I get several emails from coworkers complaining of the issue?

    I do not want to just increase the timeout settings.  I want to understand why this delay is happening.


    Ryan

    Tuesday, October 24, 2017 5:59 PM

All replies

  • By means not simple to troubleshoot.

    When this happens, is it difficult in general to log in on the remote server? In some situations, when SQL Server is under load, it may not be very responsive to login requests. It can also run out of workerthread, in which case your login attempt shows up as a THREADPOOL wait on the target server.

    If the problem is only when connecting to the remote server from your local server, I would be more inclined to think that there network issues of some sort. There could also be problem in connecting to the AD, or things like that.

    I assume that self-mapping is in force, so that you connect to the remote server with the same credentials that you use when you login locally? (You can run this query to find out if you don't know):

    SELECT * FROM OPENQUERY(SQL2014, 'SELECT SYSTEM_USER AS usrname)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, October 24, 2017 9:52 PM
  • Hi Ryan0827,

    According to your description, it seems that the connection is unstable, and there are many factors that cause this issue. So you could try some possible options below.

    Option1 Check the network on the local server

    1.On the Start menu, click Run. In the Run window type cmd, and then click OK.
    2.In the command prompt window, type ping and then the IP Address of the remote computer that is running SQL Server, or type ping and the name of the computer. For example,

    ping <IP address or computer name> -t

    3. Monitor the network by using the command above, then check the reply time if it meets your requirements, especially when running the query.

    Option2 The server decides the order in which it tries a protocol(TCP/IP, Named Pipes, Shared Memory)

    You could use SQL Server Configuration Manager on the server to set the order.

    1. Open SSCM.

    2. In the left pane, expand SQL Server Network Configuration, and then click Server Protocols.

    3. In the right pane, right-click specific Pipes, and then click Order.

    4. Use the up and down arrows to change the priority.

    Option3 The client decides the order in which it tries a protocol(TCP/IP, Named Pipes, Shared Memory)

    You could use SQL Server Configuration Manager on the client to set the order.

    1. Open SSCM.

    2. In the left pane, expand SQL Native Client Configuration, and then click Client Protocols.

    3. In the right pane, right-click specific Pipes, and then click Order.

    4. Use the up and down arrows to change the priority.

    Besides, if you changed the enabled setting for any protocol you must restart the Database Engine. In the left pane select SQL Server Services. In the right-pane, right-click the instance of the Database Engine, and then click Restart.(Option2,Option3)

    Option4 Try other ways

    Please see the following article and search some ways that you could try.

    Troubleshoot Connecting to the SQL Server Database Engine

    Hope these are useful to you.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 25, 2017 5:05 AM
  • I'm getting the same "Unable to complete login process due to delay in opening server connection" error message.  My setup is as follows: on my Windows 10 Pro (v1709) laptop I'm running SQL Server 2017 in a docker container.  I have installed SSMS v17.7.  

    I can successfully connect to the database from SSMS, SSDT, and SQLCMD.  It is only SQL Profiler, v17.7, that is unable to connect.  With any of these tools, I'm simply connecting to "localhost."

    What additional information, if any, should I provide?

    The full text of the error is as follows:

    ===================================
    
    Cannot connect to localhost.
    
    ===================================
    
    Unable to complete login process due to delay in opening server connection
     (pfutil)
    
    ------------------------------
    Program Location:
    
       at CProfilerSqlDbConnection.Open()
       at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectorThread()
    
    



    -Brent Arias

    Wednesday, June 6, 2018 2:05 AM
  • Try this , 

    Navigate to linked server in server objects , right click on linked server , Script AS --> drop and recreate . 

    search for connection timeout and query timeout values , by default they are 0, increase to your desired value , 

    execute the script and go to properties of linked server and reenter the passwords if any embedded in linked server , 

    Test the connection now , 

    Thank you.

    Sunday, February 24, 2019 4:55 PM