none
Linked server error: Named Pipes Provider: Could not open a connection to SQL Server [5]. RRS feed

  • Question

  • I have a stored procedure that calls a function, the function uses a linked server in it's query.

    If I execute the stored procedure from the app or from SSMS on my laptop (connected to the host server)  I get a timeout (20 seconds) and the stored procedure logs the following error in my error handler table;

    Named Pipes Provider: Could not open a connection to SQL Server [5].

    If I execute the same stored procedure on the host server (SSMS local connection)  it is runs successfully in less than 1 second.

    SQL Browser is not running (due to security) but I have enabled it to test but nothing changed
    The linked server is using SQL Authentication
    Linked server is the default OLE DB Provider for SQL Server

    Server 1  (host): Win2K8, SQL2K8 R2
    Server 2 (linked): Win2K8, SQL2K8 R2

    The host server is running a non standard port but the linked server is running the default 1433.

    Any idea's as to why this works locally but not remotely? 



    • Edited by DanAzz Wednesday, November 7, 2012 3:36 PM
    Wednesday, November 7, 2012 2:46 PM

Answers

  • FIXED:

    I have added the port (default, 1433) to the linked server conneciton (ServerName, 1433). 

    It appears that if the host server runs on a non-default SQL port then the default port needs to be specified in the Linked server connection, even though the destination server is running port 1433.

    • Marked as answer by DanAzz Friday, November 9, 2012 11:01 AM
    Friday, November 9, 2012 11:01 AM

All replies

  • Named Pipes Provider: Could not open a connection to SQL Server [5].

      Is this the exact error you faced, The solutions is being provided for (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 5) by Pinal dave at  http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/

    Thanks
    Manish

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, November 7, 2012 3:31 PM
  • Thanks Manish, I saw that post prior to posting on this forum but it isn't the same scenrio, both server accept incoming connections (one is a SharePoint server) and the query works from the host server to the linked server the issue is executing the query from another server (it executes the stored procedure, validated by viewing the execution plan and profiler) it just fails when it calls the linked server.

    It might be a timeout error but there isn't anything in the SQL Error logs.

    Wednesday, November 7, 2012 3:43 PM
  • Since setting up your linked server have you been able to run any queries against the host from Server2? What happens when you run a basic query on Server2 against the host? Still getting a timeout?

    I think the time I encountered an issue like this I had to setup an alias on the linked server to the host server. This was due to security being rather tight on the host server though.  

    Just shooting some possibilities out there.

    Wednesday, November 7, 2012 7:27 PM
  • I have tried a simple select top 10 from a table on the linked server and get the following error;

    OLE DB provider "SQLNCLI10" for linked server "servername" returned message "Login timeout expired".

    OLE DB provider "SQLNCLI10" for linked server "servername" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".

    Msg 5, Level 16, State 1, Line 0

    Named Pipes Provider: Could not open a connection to SQL Server [5].

    This works between the host and the linked server but not if run from another server (all via SSMS).

    I have tried an alias but it was a lot slower (between the two servers that work) but it still didn't work from any other server.

    Wednesday, November 7, 2012 8:32 PM
  • Hi DanAzz,

    In Server 1, could you just select the table from the linked server 2?
    And when you connect to Server 1 remotely through SSMS. You could not select the table from the linked server 2. Right?
    Have you configued the SQL Server linked server to allow remote connections?

    TechNet Subscriber Support
    If you are
    TechNet Subscriptionuser and have any feedback on our support quality, please send your feedback here.

    Thanks.


    Maggie Luo

    TechNet Community Support





    Thursday, November 8, 2012 6:41 AM
    Moderator
  • Hi Maggie,

    Yes the linked server does allow remote connections, it has been Live for over two years.

    The linked query function works when logged onto server 1 (and linking to server 2) but if you are logged onto server 3 and run the function on server 1 (via SSMS) it times out.

    Thursday, November 8, 2012 7:34 AM
  • Further testing:

    Remote desktop to server 1: Profiler on server 2 (the linked server)
    Execute stored procedure (SSMS server 1) and Profiler picks up a login on server 2 for the linked server account and query runs successfully

    Remote desktop to server 3: Profiler on server 2 (the linked server)
    Execute stored procedure (SSMS server 3 connected to server 1) but Profiler shows no login attempts and the query times out.

    I logged on using the same domain account on all servers which is a local admin on servers and sa on all SQL Severs.  All servers are in the same datacentre.

    Thursday, November 8, 2012 8:27 AM
  • FIXED:

    I have added the port (default, 1433) to the linked server conneciton (ServerName, 1433). 

    It appears that if the host server runs on a non-default SQL port then the default port needs to be specified in the Linked server connection, even though the destination server is running port 1433.

    • Marked as answer by DanAzz Friday, November 9, 2012 11:01 AM
    Friday, November 9, 2012 11:01 AM