Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding - Microsoft SQL Server 2005

Symptom:

SQL Server throws an exception 'Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2) when try to connect from a remote computer. Most probable symptom for the error is TCP Port 1433 being blocked by the windows firewall in a computer running windows XP(SP2).

Solution

Scenario 1

Make sure you have enabled remote connections from the SQL Server surface area configuration and allow TCP/IP, Named Pipe protocol or combination as necessary.

• To Enable Remote connections: [1] SQL Server 2005 – [2] Configuration Tools – [3] SQL Server Surface area configuration – [4] Click on 'Surface area configuration for services and connections'.
  
• Expand [1] Database Engine – [2] Remote Connections – [3] Select Local and remote connections – [4] Select the desired protocol (In this scenario I have used TCP/IP and Named Pipes)
  
• Restart the SQL Server.
  

Scenario 2

Open TCP Port 1433 – This is the default port that SQL server use for remote connections. By default windows XP firewall deny access of this port:

• Open [1] Control Panel – [2] Windows Firewall – [4] Click on 'Add Port'
  
• Add: Name = SQL Server (Any meaningful name), Port Number = 1433 (by default, if you have changed the default port you have to make sure you enter the corresponding port number)