Named Pipes Provider, error: 40 - Could not open a connection to S

[Alex]

We have a web service server that connects to a SQL Server 2000 database via
a firewall. It works fine, but once in a while, looking at the event logs, I
notice this entry:

Exception Type: System.Data.SqlClient.SqlException
Errors: System.Data.SqlClient.SqlErrorCollection
Class: 20
LineNumber: 0
Number: 53
Procedure: NULL
Server:
State: 0
Source: .Net SqlClient Data Provider
ErrorCode: -2146232060
Message: 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)
Data: System.Collections.ListDictionaryInternal
TargetSite: Void OnError(System.Data.SqlClient.SqlException, Boolean)
HelpLink: NULL

What confuses me is that the connection is available 99.9% of the time, and
once in a while we get this error. It's the same web service, using the same
connection string. Researching this, a possible issue is that it's using
tcp\ip and then tries to use named pipes. Not sure how this is possible since
I don't know much about it.

Can anyone shed some light?

Thanks

[Erland Sommarskog]

Alex (alex...@adp.com.(nospam)) writes:
> We have a web service server that connects to a SQL Server 2000 database
> via a firewall. It works fine, but once in a while, looking at the event
> logs, I notice this entry:
>
> Exception Type: System.Data.SqlClient.SqlException
> Errors: System.Data.SqlClient.SqlErrorCollection
> Class: 20
> LineNumber: 0
> Number: 53
> Procedure: NULL
> Server:
> State: 0
> Source: .Net SqlClient Data Provider
> ErrorCode: -2146232060
> Message: 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)
> Data: System.Collections.ListDictionaryInternal
> TargetSite: Void OnError(System.Data.SqlClient.SqlException, Boolean)
> HelpLink: NULL

I have seen KB articles mentioning situations where SQL Server fails
to respond to login requests when under load.

However, my experience is that this may very well be something flaky
in your network. I have sometimes have problem at work to connect to
our development server. Next time I try it works. It can be the firewall
that takes a nap. Or the DNS server. Or...

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Alex]

Looking at the times when this happens, the server is not under any
particular heavy load at all. According to our Network team, the firewall is
either on or off, so they say it's not a firewall issue. Also, the two
servers are on the same physical network, so I'm not sure if there are any
issues in this case.

Thanks

[Erland Sommarskog]

For my part I stopped believe network teams long ago. :-)

Unfortunately, these issues are very difficult to track down. You can
check the SQL Server error log if there are any messages that be
correlated with the connection problems. But most likely, the problem
is outside SQL Server.

One thing you could consider is to set up a job from Task Manager that
pings the SQL Server machine with 200 pings about every 10 minutes, and
let that run for some days. If you see any dropped packages, this is
an indication that the network has some issue.