none
Re: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server

    Question

  • Hi,

    I'm encountering an issue with "Named Pipes Provider, error: 40 windows" and am having problems determining how to fix it due to the environment I'm using.  I have two SQL Servers installed on two separate Win2K3 Server boxes, one is SQL Server 2000 and the other is SQL Server 2005.  The SQL Server 2000 contains the actual application data.  The 2005 database is used only for Reporting Services.  I've set up the reports on SSRS such that their datasources hit the 2000 server.  This is using SQL Server authentication.

    When testing the reports via SSRS (in Visual Studio 2005), the connection to the data works and the reports are generated fine.  When I deploy them to the reporting server and launch IE to test locally (still on the 2005 box), I get this "Named Pipes Provider, error 40" issue.  I made sure that Named Pipes and TCP were enabled and the port set at 1433 (to match that on the 2000 box). 

    Now I changed the datasource's authentication from SQL Server to Windows authentication.  I tested this in SSRS and this works too.  When I redeployed the reports with this authentication change, testing the reports via IE locally (on the 2005 box) worked.  Great.  Now when I open IE on an external box, i.e. on the 2000 box, and try to test the reports, I get this same error 40 issue.  I've been through a few threads describing the error 40, fiddling around with the SQL Server configuration as well as SSRS, to no avail.  I have a feeling this error 40 issue has to due with permissions/authentication between the SQL Server boxes but I can't really be sure.  Anyone have any ideas on how to troubleshoot my situation.  Thanks.

    larry
    Friday, May 2, 2008 12:41 AM

Answers

  • Named Pipes Provider, error: 40 - Could not open a connection to SQL Server :

    I.   Incorrect connection string, such as using SqlExpress.

    Check out: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=558456&SiteID=17

                    https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1245564&SiteID=1

    The typical error when dealing with Express includes:

    a.  User is not aware of SqlExpress was installed as a named instance, consequently, in his/her connection string, he/she only specify ".","localhost" etc instead of ".\SqlExpress" or "<machinename>\Sqlexpress".

    b. Np was disabld by default after installing SqlExpress.

    c. If Sqlexpress was installed on the remote box, you need to enable remote connection for Express.

    Please read the following blog for best practice of connecting to SqlExpress.

    http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx

     

    II. Named Pipes(NP) was not enabled on the SQL instance.

    Check out: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=136253&SiteID=1

    Oppose to SQL 2000 which turn on all protocols, SQL 2005 SKUs turn off NP by default. So, when you see this error, please check:

    1) Go to SQL Server Configuration Manager, See Server has NP enabled.

    2) %windir%\program files\microsoft sql server\mssql.1\mssql\log, notepad ERRORLOG, see whether Server is listening on NP.  You should see "Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ] or [\\.\pipe\mssql$<InstanceName>\sql\query]"

    3) Notice that "sql\query" is the default pipe name, so you need to know server is listening on which pipe name. eg: if you specify server pipe name is "sql\query1", then you would see in the errorlog that server listening on [ \\.\pipe\sql\query1 ], and go to SQL Server Configuration Manager, click client Named Pipe properties, see whether the pipe name is same with the one server listening on.

    4) If you are using SQL Native Client ODBC/OLEDB provider({SQL Native Client} or SQLNCLI), go to SQL Configuration Manager, click client protocols, make sure NP and TCP are both enabled. Right click properties of NP, make sure client is using the same pipe name as server for connection.

    5) If you are using MDAC ODBC/OLEDB({SQL Server} or SQLOLEDB) provider, in command line, launch "cliconfg.exe" and make sure NP enabled and right pipe name specified.

     

    III. Remote connection was not enabled. 

    Check out: when you right click on the Server in SQL Server Management Studio, in Connections, the Remote server connections part, you have enabled the "Allow remote connections to this server" check box

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=322792&SiteID=1

    https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=763875&SiteID=1

    If you are making a remote connection, namely, your target SQL Server is on the different box as client application, you might need to check whether:

    a. "File and Printer Sharing" was opened in Firewall exception list.

    b. Please see the blog for enabling remote connection for express and troubleshooting tips of remote connection.

    http://blogs.msdn.com/sql_protocols/archive/2005/11/14/492616.aspx

    http://blogs.msdn.com/sql_protocols/archive/2006/09/30/SQL-Server-2005-Remote-Connectivity-Issue-TroubleShooting.aspx

     

    IV. Server not started, or point to not a real server in your connection string.

    Check out: Open SQL Server Surface Area Configuration and ensure all the required services are started, Remote Connections are configured.

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=348662&SiteID=1

    a. use "sc query mssqlserver" for default instance or "sc query mssql$<instancename>" to make sure SQL Server was started. Sometimes, reseason behind the broken of your client application w/ this error:40 might be SQL server restarted and failed, so, it'd better for you to double check.

    b. User specified wrong server in their connection string, as described in the forum discussion, "MSSQLSERVER" is an invalid instance name. Remember, when you connect to default instance, <machinename> could be best representitive for the instance, when you connect to a named instance such as sqlexpress, you should specify <machinename>\<instancename> as data source in your connection string.

     

     V. Other reasons such as incorrect security context.

    Check out: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=192622&SiteID=1

    Such error also occured during user operation such as moving database or db mirroring or cluster, any DB OP that might invovle different sql instances, namely, the destination database is located in another sql instance and user is not aware of the state of the destination. I recommend you first isolate whether this fail is during connection stage or data operation stage.

    a. During data operation, you are normally asked to type in the destination server name whether it is default to "(local)" or another server "<remotemachinename>". So, remember the exact string that represent the target instance, then when the error repros, open command line, use "sqlcmd -S<representitive> -E" ,see what happens, if the connection fail, please follow up above I - IV troubleshooting lists. otherwise continue.

    b. If you can make basic conection, but still face the error, then there must be something that server reject the connection or client close the connection for some reason.

     

    VI. Please try basic connectivity tests between the two mahcines you are working on. One simple way to verifty connectivity is to use command line tools, such as osql.exe. For example, osql -E -StcpTongue Tiedervername\instancename. If it connects cross-machine successfully, please also verify that your connection string in your scenario is correct.

     

    Here are some blogs which could be helpful: just follow the basic connectivity troubleshooting guidelines on the SQL Protocols blog, see:

    SQL Server 2005 Connectivity Issue Troubleshoot - Part I

    http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx

    and

    SQL Server 2005 Connectivity Issue Troubleshoot - Part II

    http://blogs.msdn.com/sql_protocols/archive/2005/10/29/486861.aspx

     

    Hope this helps.

     

    Tuesday, May 20, 2008 5:49 AM

All replies

  • Update:  When looking at the SSRS logs, this is what I see:  Cannot create a connection to data source 'Barai_DB'. ---> System.Data.SqlClient.SqlException: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.  Any ideas?

    larry
    Friday, May 2, 2008 2:10 PM
  • Named Pipes Provider, error: 40 - Could not open a connection to SQL Server :

    I.   Incorrect connection string, such as using SqlExpress.

    Check out: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=558456&SiteID=17

                    https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1245564&SiteID=1

    The typical error when dealing with Express includes:

    a.  User is not aware of SqlExpress was installed as a named instance, consequently, in his/her connection string, he/she only specify ".","localhost" etc instead of ".\SqlExpress" or "<machinename>\Sqlexpress".

    b. Np was disabld by default after installing SqlExpress.

    c. If Sqlexpress was installed on the remote box, you need to enable remote connection for Express.

    Please read the following blog for best practice of connecting to SqlExpress.

    http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx

     

    II. Named Pipes(NP) was not enabled on the SQL instance.

    Check out: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=136253&SiteID=1

    Oppose to SQL 2000 which turn on all protocols, SQL 2005 SKUs turn off NP by default. So, when you see this error, please check:

    1) Go to SQL Server Configuration Manager, See Server has NP enabled.

    2) %windir%\program files\microsoft sql server\mssql.1\mssql\log, notepad ERRORLOG, see whether Server is listening on NP.  You should see "Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ] or [\\.\pipe\mssql$<InstanceName>\sql\query]"

    3) Notice that "sql\query" is the default pipe name, so you need to know server is listening on which pipe name. eg: if you specify server pipe name is "sql\query1", then you would see in the errorlog that server listening on [ \\.\pipe\sql\query1 ], and go to SQL Server Configuration Manager, click client Named Pipe properties, see whether the pipe name is same with the one server listening on.

    4) If you are using SQL Native Client ODBC/OLEDB provider({SQL Native Client} or SQLNCLI), go to SQL Configuration Manager, click client protocols, make sure NP and TCP are both enabled. Right click properties of NP, make sure client is using the same pipe name as server for connection.

    5) If you are using MDAC ODBC/OLEDB({SQL Server} or SQLOLEDB) provider, in command line, launch "cliconfg.exe" and make sure NP enabled and right pipe name specified.

     

    III. Remote connection was not enabled. 

    Check out: when you right click on the Server in SQL Server Management Studio, in Connections, the Remote server connections part, you have enabled the "Allow remote connections to this server" check box

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=322792&SiteID=1

    https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=763875&SiteID=1

    If you are making a remote connection, namely, your target SQL Server is on the different box as client application, you might need to check whether:

    a. "File and Printer Sharing" was opened in Firewall exception list.

    b. Please see the blog for enabling remote connection for express and troubleshooting tips of remote connection.

    http://blogs.msdn.com/sql_protocols/archive/2005/11/14/492616.aspx

    http://blogs.msdn.com/sql_protocols/archive/2006/09/30/SQL-Server-2005-Remote-Connectivity-Issue-TroubleShooting.aspx

     

    IV. Server not started, or point to not a real server in your connection string.

    Check out: Open SQL Server Surface Area Configuration and ensure all the required services are started, Remote Connections are configured.

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=348662&SiteID=1

    a. use "sc query mssqlserver" for default instance or "sc query mssql$<instancename>" to make sure SQL Server was started. Sometimes, reseason behind the broken of your client application w/ this error:40 might be SQL server restarted and failed, so, it'd better for you to double check.

    b. User specified wrong server in their connection string, as described in the forum discussion, "MSSQLSERVER" is an invalid instance name. Remember, when you connect to default instance, <machinename> could be best representitive for the instance, when you connect to a named instance such as sqlexpress, you should specify <machinename>\<instancename> as data source in your connection string.

     

     V. Other reasons such as incorrect security context.

    Check out: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=192622&SiteID=1

    Such error also occured during user operation such as moving database or db mirroring or cluster, any DB OP that might invovle different sql instances, namely, the destination database is located in another sql instance and user is not aware of the state of the destination. I recommend you first isolate whether this fail is during connection stage or data operation stage.

    a. During data operation, you are normally asked to type in the destination server name whether it is default to "(local)" or another server "<remotemachinename>". So, remember the exact string that represent the target instance, then when the error repros, open command line, use "sqlcmd -S<representitive> -E" ,see what happens, if the connection fail, please follow up above I - IV troubleshooting lists. otherwise continue.

    b. If you can make basic conection, but still face the error, then there must be something that server reject the connection or client close the connection for some reason.

     

    VI. Please try basic connectivity tests between the two mahcines you are working on. One simple way to verifty connectivity is to use command line tools, such as osql.exe. For example, osql -E -StcpTongue Tiedervername\instancename. If it connects cross-machine successfully, please also verify that your connection string in your scenario is correct.

     

    Here are some blogs which could be helpful: just follow the basic connectivity troubleshooting guidelines on the SQL Protocols blog, see:

    SQL Server 2005 Connectivity Issue Troubleshoot - Part I

    http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx

    and

    SQL Server 2005 Connectivity Issue Troubleshoot - Part II

    http://blogs.msdn.com/sql_protocols/archive/2005/10/29/486861.aspx

     

    Hope this helps.

     

    Tuesday, May 20, 2008 5:49 AM
  • in my case, i had a standalone server, i changed the sql server port default port 1433 in configuration manager to some number and restarted the sql serve service to take effect,i was able to connect to the sql server through management studio if i login to the server. but i was not able to connect from my local machine through sql server, i was getting the error:

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and

    that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 5)

    I checked and verified all the below

    -Named pipes/TCP is enabled.
    -Remote connections are allowed.
    -Windows Firewall is off
    -Created an exception for portin Windows Firewall( this was not necessary in my case as the server is in same subnet network).
    -Enabled everything in SQL Server Configuration Manager.


    then i chnaged back the port number to default 1433 and restarted the sql server service, and the issue got resolved and i am able to connect the sql server from my local management studio.

    Friday, December 19, 2014 6:49 PM
  • old problem, old bug.

    The SSMS 2014 SP2 has a fatal bug - even if you specifiy the protocol "TCP/IP" it attempts a named pipe connection and then fails.

    After seeing this I was **** *** ******* ** *** (censored), because I wasted 14 hours of work on a CEO priority problem. 

    The bug lead me into many useless attempts to connect. I went through various troubleshooting but didn't find the troublemaker,  I made useless supportcalls with our local admin to unblock the firewalls, priority 1 calls for  the WAN team for checking if Port 1433 is open, UDP on port 1433,  I made useless attempts with user rights, and I downloaded the 890 MB sized SSMS 16 (and that takes a looooong while on a 3G connection) for just finding out that it connects and the SSMS 2014 SP2 doesn't. 

    At the end the "named pipes" protocoll was disabled at the target system. ALL trobleshooting guides ask "is TCP-IP activated in the configuration manager". Yes it was.

    The SSMS 2016 did connect (but it has some weird bugs so I am not gonna use da thing productonal) and it turned out the server was  OK, functional, the both SQL 2014 instances could "talk" to each other (wanted to set up a replication) but for setting this up a working SSMS was required... 




    IT architect - Terminal servers, virtualizations, SQL servers, file servers, WAN networks and closely related to software devleopment (8 years + experience in VB, C++ and script langugaes), MCP for SQL server and CCAA for Xenapp 6.5

    • Proposed as answer by zhgart Tuesday, October 24, 2017 3:00 AM
    Friday, March 17, 2017 9:45 AM