Named Pipes Provider, error: 40 - Could not open a connection to SQL Server (Microsoft SQL Server, Error: XXX)

  • Article

This error message is the most frequent error message when connecting to SQL Server. You see this error message when you use SqlClient. In SNAC, the error message is slightly differently as follows:

C:>osql -E -Syourserver
[SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [xxx].
[SQL Native Client]Login timeout expired

Basically, this error message just tell you that the client cannot make a connection to the server. It's equvalent to "SQL Server does not exist or access denied" in MDAC. Although the error message say about Named Pipe Provider, the issue does not have to be NP related. The reason is that, by default, the client stack try TCP and NP in order. If the connection attempt could not success with any of them, then NP is the last protocol tried and this is the error message to present to users.

When users see this error message, sometimes xxx is omitted. But actually, xxx is the most important part of this error message. xxx is Windows error code and it gives customer hints about why the connection fails. Here are some error code users often see. I also explain the root cause and possible solutions here.

1) xxx=53
winerr 53 means "The network path was not found". If you got this message, it means the client stack cannot find the target machine. Here are possible reasons for this failure,
 a) typo in the server name, or using "/" rather than "" between server name and instance name, e.g. "myserver/myinst" is not correct.
 b) name resolution to the server name is not correct, "ping -a yourserver" would tell if that's the case
 c) The server machine is firewall'ed and file sharing is not in the exception list. You need put "File and Printer Sharing" in exception.

2) xxx = 1326
winerr 1326 means "Logon failure: unknown user name or bad password". When you get this error code, it means a) the client stack is able to reach the target machine, and b) the service account of the client application does not have enough credential to make a NP session to the server. NP is based on SMB (file sharing). The logon failure message represented by winerr 1326 is from SMB layer, not SQL Server. You need to make sure you can make a file sharing to the server machine with the same service account. If you cannot make a file sharing between your server and your client, NP in SQL Server would not success. Try "net use" or "Map Network Drive" to check this. This often happens when server and/or client is not on domain.

3) xxx = 2
winerr 2 means "The system cannot find the file specified". This error message means the client can reach the server but there is no Named Pipe listener with specific pipe name on the target machine. It also means the account can have a file sharing session without a problem.Possible reasons are:
 a) typo in instance name or wrong instance name. The instance name is not the one you are targeting. Note that for default instance, you shouldn't use MSSQLSERVER as instance name.
 b) Target SQL Server is not running
 c) Named Pipe is not enabled on the server. In this case, the SQL server is not listenning on the specific pipe name.

4) xxx = 233
winerr 233 means "No process is on the other end of the pipe". If you see this error, it usually means you don't have sufficient credential to connect to the server, e.g, wrong user name and/or password when you are using SQL authentication. The connection is closed by server before an error message is sent to the client.

There are other error code come with this error message, but they are not as often as the ones I just mentioned. When you see a new error code, just use "net helpmsg xxx" to get some information and see if you can figure out any suspecious issue.

As I have mentioned, this error message does not mean you have an issue with NP. You can do something unrelated to NP to eliminate this error message, e.g.
a) make sure target machine is accessible
b) target server is running
c) TCP protocol on the target instance is enabled
d) sqlservr.exe and/or the TCP port that the server listens is on firewall exception if firewall is ON.
By doing this, you may success with TCP or have a failure with an error message related to TCP and/or logon credential. This is because we success or fail with TCP protocol and does not even come to the point of using NP.

Xinwei Hong, SQL Server Protocols
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Comments

  • Anonymous
    December 18, 2007
    What would an Error: 40 value indicate? We have a massively multithreaded c# application that handles 22.5 million sql server connections per hour all day long, but occasionally it will start throwing these errors (about 40,000 per minute). The odd thing is that we have two instances of this app running (from servers on the same subnet talking to the same load balanced sql2000 servers), but one will continue  to work fine while the other fails (there is no consistency about which one might fail; they both do at random intervals.) Sometimes it will go a week or more without problems, sometimes half a hour. The error reported is always this connection error.

  • Anonymous
    April 06, 2008
    "Error; 40" just means that Could not open a connection to SQL Server. Windows error code gives some indication about why it fails.

  • Anonymous
    November 05, 2008
    i'm download game. thats i can not install- there was messege error=-40. how to fix this error pls inform me.

  • Anonymous
    November 05, 2008
    i'm download game. thats i can not install- there was messege error=-40. how to fix this error pls inform me. kaleelmh@hotmail.com

  • Anonymous
    November 17, 2008
    The comment has been removed

  • Anonymous
    March 06, 2009
    pl. help with this error. i am fadeup with error.

  • Anonymous
    April 25, 2009
    I can't connect sql server 2005 professional. you can help me? thanks you very much