Configure Windows Firewall for SQL Server Remote Connections

The Firewall is an important component of Microsoft Windows which prevents unauthorized access to computer resources. If the Windows firewall is turned on and if it is not configured correctly, then attempts made to connect to SQL Server by users will be blocked. To access an instance of SQL Server through a firewall, a Database Administrator needs to configure the firewall on the computer that is running SQL Server to allow access. This article will show the steps that need to be followed by a Database Administrator to configure the Windows firewall for SQL Server database engine access and the steps which needs to be performed to allow SQL Server to Accept Remote Connections.

Adding Windows Firewall Exception for an Instance of SQL Server Database Engine Running on Default Port
When SQL Server is installed as a named instance, by default SQL Server will use port 1433 to accept user connections. The below are the steps which need to be followed by a DBA to add a Windows firewall exception for a SQL Server Instance which is running on the default port 1433.

1. Click Start | Run and type FIREWALL.CPL this will open up Windows Firewall:



By default, Microsoft Windows XP Service Pack 2 and later, Windows Server 2003 Service Pack 1, Windows Vista and Windows Server 2008 Operating Systems enables Windows Firewall, which closes port 1433 to prevent internet computers from connecting to a default instance of SQL Server on your computer.

2. In the Windows Firewall dialog box, click the Exceptions Tab, and then click Add Port…:



3. In the Add a Port… dialog box, specify the SQL Server in the Name textbox and also specify the Port Number as 1433 which will be the port number used by the Database Engine for the default instance of SQL Server:



4. Verify that TCP is selected and the click OK.

5. To open the port to expose the SQL Server Browser Service, click Add Port… In the Add a Port Dialog box, Type SQL Server Browser in the Name text box, type 1434 in the Port Number text box and select UDP and finally click OK to save:



The SQL Server Browser service lets SQL Server users connect to an instance of the Database Engine that is not listening on port 1433. If the SQL Server Browser Service is running then the SQL Server users can connect without knowing the port number. To use the SQL Server Browser Service, a DBA must open UDP (User Datagram Port) port 1434. To promote the most secure environment, leave the SQL Server Browser service stopped, and configure clients to connect using the port number.

6. To allow the named pipes access through the firewall, a DBA needs to enable File and Printer Sharing through the firewall.

7. To close the Windows Firewall dialog box, click OK.
Adding Windows Firewall Exception for Accessing SQL Server Database Engine Running on Dynamic Port
1. In the Windows Firewall dialog box, click Exceptions Tab, and then click Add Program….

2. In the Add a Program dialog box, click Browse, and nagivate to the instance of SQL Server that you would like to access through the firewall, and then click Open. By default the SQLServr.exe will be loacted in C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn location.



3. To close the Windows Firewall dialog box, click OK twice

Configure SQL Server to Accept Remote Connections
When users try to connect to an instance of SQL Server 2005 or SQL Server 2008 from a Remote computer, they may receive an error message as mentioned below. This happens when you try to connect to a SQL Server Instance which is not configured to accept Remote Connections. By default SQL Server Express and SQL Server Developer Editions don't allow remote connections.

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

Enabling Remote Connections in SQL Server 2005
Database Administrators can enable remote connections by configuring SQL Server using the SQL Server Surface Area Configuration (SqlSAC.exe) tool.

1. Open SQL Server Surface Area Configuration Tool by click Start | Programs | Microsoft SQL Server 2005 | Configuration Tools | SQL Server Surface Area Configuration:



2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.

3. On the SQL Server 2005 Surface Area Configuration for Servers and Connections page, expand MSSQLSERVER, expand Database Engine Node and select Remote Connections. On the right side panel you need to select Local and remote connections and select the appropriate protocol option among the three choices available depending upon your requirement. In the below snippet you could see that Using both TCP/IP and named pipes is selected:



4. Click OK to save the changes. This will open up Connection Settings Change Alert, informing you to restart the Database Engine Service for the change to take effect, Click OK:



5. On the Surface Area Configuration for Services and page, expand MSSQLSERVER, expand Database Engine Node and click services. On the right panel the details related to MSSQLSERVER services will be shown. click Stop and wait until the MSSQLSERVER service stops, and then click Start to restart the MSSQLSERVER service to bring change into effect:


Enabling Remote Connections in SQL Server 2008
In SQL Server 2008, SQL Server Surface Area Configuration Tool is not available. DBA needs to use SQL Server Configuration Manager (SQLServerManager10.msc) tool to enable remote connections in SQL Server 2008.

1. Open SQL Server Configuration Manager Tool by click Start | Programs | Microsoft SQL Server 2005 | Configuration Tools | SQL Server Configuration Manager.

2. In SQL Server Configuration Manager, expand SQL Server Network Configuration and the click Protocols for MSSQLSERVER:



3. In the details panel on the right side, right click TCP/IP and click on Enable. This will issue a warning that the changes will only come into effect once the database engine service is restarted. Click OK:



4. Follow the same steps to enable Named Pipes Protocol This will issue a warning that the changes will only come into effect once the database engine service is restarted:



5. On the Surface Area Configuration for Services and page, expand MSSQLSERVER, expand Database Engine Node and click services. On the right panel the details related to MSSQLSERVER services will be shown. click Stop and wait until the MSSQLSERVER service stops, and then click Start to restart the MSSQLSERVER service to bring change into effect.

Conclusion
Configuring the Windows Firewall Exception for the SQL Server Database Engine will allow users to connect to SQL Server Instances when the Windows Firewall is enabled to secure the SQL Server. Once the exception is created then the Database Administrators can avoid database connectivity issues on SQL Server Developer and SQL Express Editions by proactively enabling the SQL Server Remote Connections, so that SQL Server Users who wanted to connect remotely can connect without any issues.