Why would I get this error intermittently? "The server was not found or was not accessible"

Question

We keep getting this error randomly in our web application.

System.Data.SqlClient.SqlException: 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)

It would make sense if we got the error ever time but to get it intermittently suggest something else is going on.

Has anyone experienced this? Any suggestions or theories?

Thanks!

Answer 1

It's possible that the server is too busy to respond - are you using SQL Express or Workgroup Edition? Also, how many connections at a time does this server have? Is this error happening on all connections at a certain time, or do some connections get rejected while others succeed at the same time?

Also, if you do a "PING -t Servername" and watch it, does every ping come back, or are some lost? This can be an indicator of network interruptions that might also cause this error.

Answer 2

I've seen this before when I was accessing a remote sever at another location. I suspected it was due to the router configuration between the two sites as it was, shall we say, cludgy. It is entirely possible you're getting it because of a network configuration or bad hardware, but I wouldn't jump to that conclusion until you exhausted a lot of other possibilities.

Answer 3

We had this because of too many firewalls: the further away the client (say HK or NY, we're in Switzerland) the more often it happened. London: rarely. Switzerland: never. It was explained to us (via our DB engineering and MS help) that the delays because of routers and firewalls sometimes upset Kerberos and/or related timings.

Fix: use the port in the connection string. This avoids the roundtrip to port 1434 to enumerate the instance. We already used FQDN.

Example: server.domain.tld\instance,port

YNNV of course, but it worked for us

Answer 4

I had the exact same problem; we also got this one:

System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

and this one:

System.Data.SqlClient.SqlException: A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

The first one was especially odd, as we weren't using Named Pipes.

Anyway, it turned out the problem was a faulty switch.

I messed around with app- and db-level troubleshooting before setting up a scheduled task to PING back and forth between the appservers and the databases (using both the IP address and NETBIOS name of the servers, in case the issue was netbios name lookup).

The ping errors happened reliably at the same time as the DB errors, so we knew it was the network hardware and not anything at a higher level.

Replacing the switch fixed everything.