Monday, August 22, 2011

FIXED - Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Problem :
After installing and configuring a SQL Server instance when I try to connect to it using SSMS from another machine, I got the below error;

TITLE: Connect to Server
------------------------------
Cannot connect to mmsdevelopment.
------------------------------
ADDITIONAL INFORMATION:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-2&LinkId=20476


Solution :
After digging in to the issue I found that windows firewall on the server is enabled and it refuses any connections to SQL Server used port (1433). The solution is adding this TCP port (1433) to the Windows firewall exceptions list.

This is a technet article which details the steps of adding a port to Exception list
http://technet.microsoft.com/en-us/library/cc784523(WS.10).aspx

Apart from this specific scenario there are other situations which can cause the same error to appear. Below are the steps you should take to rectify this as detailed in the MSDN;

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.

No comments:

Post a Comment