SQL Server 2008 R2 TCP timeout expired problem

Question

I have an issue with a SQL Server 2008 R2 installation where at random intervals I get a "timeout expired" error.   This can appear in SSMS and applications querying any databases.

I inadvertently changed an application connection method from TCP to named pipes and this application now is impervious to the timeout problems.   I have monitored it closely for over a week and switching it back to TCP connections provides the same intermittent timeout errors.    Named pipes won't be an adequate connection method going forward though.

I have tried connections via IP address as well as named instance and there appears to be no difference.

If an application already has a connection or an SSMS query window is already open then any queries run against any of the database perform well so there doesn't appear to be a performance problem or blocking occuring.

I have a question mark regarding the virus scanner (McAfee VirusScan Enterprise 8.7.0i) as this seems to be a bit of a black art configuring it correctly.

The other salient piece of information I can give is that this SQL installation is configured with 2 identical servers in a failover cluster.

Does anyone know how I can trap TCP connections being granted or denied?   I'm hoping there's something available in perfmon to do this. 

Other than that if anyone has any other useful suggestions then I'm more than happy to follow them up. 

Thanks.

Answer 1

Just some further information ..

I can run telnet <ip address> 1433 from a remote PC at the time of a connection problems - this gives me a blank screen but from reading other resources on the .net providing that I get a blank screen then the port isn't being blocked.

If I run sp_who2 via query analyser then there is very little actually going on and no processes being blocked.

There are no failed "Audit login" events.

It does appear that SQL Server is denying TCP connections at random intervals.  A modified version of the application that uses named pipes is still impervious to the connection problems.

Thanks.

Answer 2

Hi Godofthunder,

Clients on Windows Vista or Windows Server 2008 can receive this error when connecting to a named instance of SQL Server that is installed on a cluster or on a non-clustered computer with multiple IP addresses. This problem can occur for all versions of SQL Server.

When connecting to a named instance on a remote computer, the client uses User Datagram Protocol (UDP) to connect to the SQL Server Browser Service on the SQL Server computer or cluster to obtain the connection endpoint (the TCP port number or named pipe).

Firewalls on the Windows Vista or Windows Server 2008 client do not allow loose source mapping for UDP. That is, the response must return from the same IP address that was queried. If the response does not come back from the IP address that was originally targeted, the client firewall will drop the packet. This problem can occur when you try to connect to a clustered server or a non-clustered server computer that has multiple IP address.

The following table describes the operating system combinations that can cause UDP packets to be dropped. This prevents connecting to a named instance of SQL Server or a default instance of SQL Server that is not listening on TCP port 1433.

To work around this problem, perform one of the following actions:
•Specify the TCP port number or named pipe name as part of the server name in the connection string.

•Create an exception in the Windows Firewall with Advanced Security on the client computer.

The exception can be either of the following:
◦Add an exception rule for the application connecting to SQL Server.

◦Add an inbound rule that allows traffic from all the possible IP addresses of the SQL Server computer or cluster.

Caution:

Exceptions in the Firewall may make a computer or a network more vulnerable to attack by malicious users or by malicious software such as viruses. We do not recommend this workaround but are providing this information so that you can implement it at your own discretion in cases where the alternative is impractical.

 

---

Iric Wen
TechNet Community Support