Pages

Friday, February 24, 2012

Troubleshooting: Timeout Expired

The "Timeout expired" error commonly occurs when an instance of the SQL Server Database Engine is not running, when the server name was typed incorrectly, or when there are network problems or firewalls.

In SQL Server Management Studio this error appears as:
"Cannot connect to <servername>."
"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2)"
From sqlcmd, possible timeout errors include:
"SQL Network Interfaces: Error Locating Server/Instance Specified"
"Sqlcmd: Error: Microsoft SQL Server Native Client : Client unable to establish connection."
"Sqlcmd: Error: Microsoft SQL Server Native Client : Login timeout expired."
"Could not open a connection to SQL Server"

"An error has occurred while establishing a connection to the server. When connecting to SQL Server, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections."
Cause
Resolution
Server name was typed incorrectly.
Try again with the correct server name.
The SQL Server service on the server is not running.
Start the instance of SQL Server Database Engine.
The TCP/IP port for the Database Engine instance is blocked by a firewall.
Configure the firewall to permit access to the Database Engine.
Database Engine is not listening on port 1433 because it has been changed, or because it is not the default instance, and the SQL Server Browser service is not running.
Either start the SQL Server Browser service, or connect specifying the TCP/IP port number.
The SQL Server Browser service is running but UDP port 1434 is blocked by a firewall.
Either configure the firewall to permit access to the UPD port 1434 on the server, or connect specifying the TCP/IP port number.
The client and server are not configured to use the same network protocol.
Using SQL Server Configuration Manager, confirm that both the server and the client computers have at least one enabled protocol in common.
The network cannot resolve the server name to an IP address. This can be tested using the PING program.
Fix the computer name resolution problem on your network or connect using the IP address of the server. This is not a SQL Server problem. For assistance, see your Windows documentation or your network administrator.
The network cannot connect using the IP address. This can be tested using the PING program.
Fix the TCP/IP problem on your network. This is not a SQL Server problem. For assistance, see your Windows documentation or your network administrator.

Multiple Server IP Addresses

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.
Cause
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.
Client operating system
Operating system that is running SQL Server
SQL Server 2008
result
SQL Server 2005
result
Windows XP or Windows Server 2003
Windows XP or Windows Server 2003
UDP packets are not dropped.
UDP packets are not dropped.
Windows XP or Windows Server 2003
Windows Vista or Windows Server 2003
UDP packets are not dropped.
UDP packets are not dropped.
Windows Vista or Windows Server 2008
Windows XP or Windows Server 2003
UDP packets are dropped. Fails to connect.
UDP packets are dropped. Fails to connect.
Windows Vista or Windows Server 2008
Windows Vista or Windows Server 2008 (x86, IA64)
UDP packets are not dropped.
UDP packets are dropped. Fails to connect.
Windows Vista or Windows Server 2008
Windows Vista or Windows Server 2008 (x64)
UDP packets are dropped. Fails to connect.
UDP packets are dropped. Fails to connect.
Resolution
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.
    Caution note 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.
    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.
      SOURCE:http://msdn.microsoft.com

0 comments:

Post a Comment

Related Posts Plugin for WordPress, Blogger...

Blogroll