Configure SQL 2005 for Remote Access

Configure SQL 2005 for remote access in a Windows Firewall enabled Box

While connecting to a SQL Server instance installed in a Windows Firewall enabled box using web browser we may get an error saying “Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.” The cause of this error is that the web browser is not able to interact with the server because the port is blocked by the Firewall. It is also to be noted that the behavior differ from Named Instance to Default Instance. Generally, when we debug this error there are few steps to be followed :-

For Default Instance :-

(a) Make sure that the Database Engine is enabled for Local & Remote Connection and Using Both TCP/IP and Named Pipes (using Surface Area Configuration (SAC) Tool)
(b) Create an exception for SQL Server 2005 in Windows Firewall
loadTOCNode(3, 'moreinformation');
To create an exception for SQL Server 2005 in Windows Firewall, follow these steps:
(i) Right click on Local Area Network in Network Connection and Select Properties
(ii) Select ‘Advanced’ Tab
(iii) Select ‘Setting’
(iv) In Windows Firewall, click the Exceptions tab, and then click Add Port
(v) Give your Instance Name
(vi) Give the SQL Server Default Instance Port No (for eg. 1433 )
(vii) Select TCP
(viii) Click OK
(You can do this for the SQL Service instead of mentioning the port number, by using Add Program in the Exception Tab)
For Named Instance
When we use named instance generally the port number will be different from 1433(as 1433 is the default port for SQL Server). For named instance, you have to mention the port number in the connection string. If you are running SQL Server 2005 by using an instance name and you are not using a specific TCP/IP port number in your connection string, you must enable the SQL Server Browser service to allow for remote connections. Then you have to follow the above mentioned step for the corresponding ports. Apart from that you should make sure that the SQL Browser Service is running and the UDP Port 1434 is added in the exception. Steps are as follows
(a) Make sure that the Database Engine is enabled for Local & Remote Connection and Using Both TCP/IP and Named Pipes (using Surface Area Configuration (SAC) Tool)
(b) Create an exception for SQL Server 2005 in Windows Firewall
loadTOCNode(3, 'moreinformation');
To create an exception for SQL Server 2005 in Windows Firewall, follow these steps:
(i) Right click on Local Area Network in Network Connection and Select Properties
(ii) Select ‘Advanced’ Tab
(iii) Select ‘Setting’
(iv) In Windows Firewall, click the Exceptions tab, and then click Add Port
(v) Give your Instance Name
(vii) Give the SQL Server Named Instance Port No (for eg. 1433 )
(vii) Select TCP
(viii) Click OK

(c) Now, you should add an exemption for UDP port 1434
(i) In Windows Firewall, click the Exceptions tab, and then click Add Port
(ii) Give your Instance Name
(ii) Give the Port No as 1434
(iii) Select UDP
(iv) Click OK
(d) Go to the SQL Server Configuration Manager and Make sure that the SQL Browser is running