Intermittent SQL Server ODBC Timeout expired

Question

We have a bunch of VB6 applications that access two different database servers (both 32-bit windows 2003, one SQL Server 2000, one SQL Server 2005). About every ten minutes or so, we are getting a few errors:

[Microsoft][ODBC SQL Server Driver]Timeout expired [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. [Microsoft][ODBC SQL Server Driver]ConnectionRead()

This is happening on more than a dozen different computers at random times. We also have IP phones that all run through the same network and those are not having any problems. We can also VNC into a users computer and reproduce the error they were getting, but VNC still continues to work. Email also works. It just seems to be an ODBC connection to SQL Server that causes the issue. The errors happen for both of our SQL Servers. We have scoured google, but haven't been able to come up with a solution. Is there anything we can try to diagnose the problem? Is there any fix out there?

Update:

We have no internal firewalls on either the servers or clients. We connect through TCP/IP via the dns name of the server (also have tried IP address).

One server has Windows 2003 SP2 and the other doesn't.

The strange thing is that it has worked fine for years and now all of a sudden these errors are happening. Nothing has changed on those servers for quite some time. That leads us to beleive it is a bad switch, but that seems unlikely since nothing else (email, phones, vnc, file server, etc) all work without problems.

Answer 1

I recently came across a very similar problem and posted it here.

ScottE found the solution.

Answer 2

We never did figure out why this was happening. We upgraded our server to SQL Server 2008 and the error messages stopped.

Answer 3

Are you using non-default instances? Or do you have internal firewalls?

We changed to "server.fqdn.tld\instance,port" to avoid this exact (IIRC) intermittent connection issue from clients behind internal firewalls.

Only SQL connections were affected.

Answer 4

Take a look at this Microsoft Knowledge Base article.

Some problems occur after installing Windows Server 2003 SP2

I would also suggest that you switch to an OLE DB provider. In my experience, it's faster than ODBC and more reliable too.

Connection Strings

Scroll down to: SQL Native Client OLE DB Provider