Allow sql server port in firewall

By:   |   Updated: 2010-01-27   |   Comments (8)   |   Related: > Security

Problem

Windows Firewall is an OS level software firewall. It can be a crucial part of OS level security if you are not using other firewall hardware or software. Since I do not have another firewall I have a need to enable Windows Firewall for my SQL Server database server. In this article I go through the process of configuring Windows Firewall for different scenarios along with the associated considerations.

Solution

Windows Firewall was previously known as ICF (Internet Connection Firewall). It falls into the category of software firewalls and is used to block/filter specific traffic towards your machine. Windows Firewall may be accessed from the Control Panel of your Windows machine or by executing "firewall.cpl" inside the run command box (as shown below). It requires theWindows service "Windows Firewall/Internet Connection Sharing (ICS)" to be in a running state.

By executing the firewall.cpl command, you get the Windows Firewall configuration frame. Here you can turn on/off the firewall along with adding exceptions and other settings. Check the status of the firewall on the General tab and if the firewall is off turn it on to enable it.

If you just turned this on, at this stage your firewall would block any SQL Server connection request to your machine. To allow access to a SQL Server database engine behind the firewall, you would have to configure the firewall by providing certain exceptions. When working with firewall configurations for SQL Server we need to know about the ports used for SQL Server and the SQL Server Browser service. Both are involved when configuring the firewall for SQL Server. So it would be appropriate to go through both concepts separately.

SQL Server ports

A SQL Server port can be related to an instance level and is specific for that instance. Ports are used by SQL Server when an instance is configured to use the TCP/IP protocol. When network data/connection request arrives at your server machine through the IP address and instance name, at this point SQL Server receives data on its specific port.

Port allotment to SQL Server may be dynamic or static. While working with firewalls and adding a port exception, you would have to configure a static port for SQL Server. It is important to note that SQL Server Express, SQL Server Compact 3.5 SP1, and named instances always use a dynamic port by default. You may allot static ports to these instances as mentioned in this tip.

SQL Server default instances use port 1433 by default. After that, any instance on that machine would have to use another port. Default instance can also be configured to listen on a custom port rather than the default 1433. You may view/edit SQL Server port configurations by going into properties of TCP/IP protocol in SQL Server Configuration Manager for a specific instance of SQL Server as shown below.

By clicking on Properties, you will get the TCP/IP properties. Go to the IP Addresses tab and here you may edit/view port configurations for a specific instance of SQL Server.

SQL Server Browser Service

SQL Server Browser service is responsible to serve incoming requests for SQL Server connection by providing them information about installed instances of SQL Server. If this service is stopped and the port assigned to any instance is other than the default port (1433), then you will need socket information (IP + Instance + Port) to access the instance . If the SQL Server Browser service is on then you do not need to provide a port number with the IP and instance. To make use of the SQL Server Browser service while the firewall is on, you have to include UDP port 1434 in the exception list of the firewall, else SQL Server Browser service would not be able to serve the incoming connection requests You can check the status or change the status of the SQL Server Browser service by using Windows MMC or by using SQL Server Configuration Manager/Surface Area Configuration. To access Windows services execute 'services.msc' in the Run command box as we did above for the firewall.

One thing to note is that I mention it uses the IP Address.  The reason for this is that when you connect to machines your DNS will direct you to the correct IP based on the machine name you give it, so you are still accessing your SQL Servers by using the machine name + Instance + Port.

Protocols for SQL Server network connections

SQL Server may be configured to accept network data through TCP/IP or named pipes protocol. In coming sections we will discuss each while configuring the firewall connections.

Different scenarios with TCP/IP

TCP/IP protocol has different scenarios when working with firewalls. Ports are involved for accessing the SQL Server engine through TCP/IP. You may have one instance running on a default/custom/dynamic port. On the other hand there may be more than one instance on the same machine with a combination of different ports and the SQL Server Browser service may be stopped or running.

For working with TCP/IP protocol access configurations, I have selected some scenarios to start with. Based on these you would be able to configure firewall access for any number of instances on your machine.

Scenario 1

SQL Server instance is running on default port 1433 TCP/IP. So you need to add TCP/IP 1433 in exception list of Windows Firewall.

Access Windows Firewall exception frame, go to exceptions tab and click Add Port... button. A frame will appear as shown below, provide parameters with any descriptive name as shown in below figure and click OK.

Now you can access your SQL server instance on the default port without any customization in connection parameters. Although it would have no effect on your connection parameters since you are using the default port, you could also add SQL Server Browser in the exception list. For this, go to exceptions tab and click Add Port... button and add 1434 port number for UDP and click OK.

Scenario 2

In this scenario you are using a port other than the default 1433 and SQL Server Browser Service is running.

Suppose you have configured SQL Server to run on port 55120. So you would add the custom port in the exception list as shown below.

To make use of the SQL Server Browser service, you would also add port 1434 UDP in exception list as shown below.

If you do not include port 1434 UDP in the exception list then there would be no use for the SQL Server Browser service even if it was started. Now to access the instance just enter IP of machine with instance name without any port number. SQL Server Browser service would do the rest for you and it will connect you to the instance even if a custom port is being used.

Scenario 3

In cases where the instance to be accessed is using the default port TCP 1433 and SQL Server Browser Service is not running, you can connect to an instance without specifying the port. You may note that in the case where instance to be accessed is on default TCP/IP port, SQL Server Browser Service status would not effect the way you are connecting to the instance.

Scenario 4

If instance is on any custom defined port and SQL Server Browser service is not running, then just add SQL Server instance custom port in exception list.

Now you have to provide IP + InstanceName + Port# for connection. If you provide just IP along with instance name, there is no mechanism to get the port number. As a result the connection would fail.

Rules for working with firewall and TCP/IP

Above mentioned scenarios may be used to build more complex configurations where more than one instances is installed on the same machine with different port configurations. Following are rules that may be deducted from above four scenarios

• Make sure that your instance is configured to use a static port either default or custom while configuring firewall access through adding ports in exception list.
• If you need to use SQL Server Browser service, include UDP 1434 in exception list
• Include 1433 in exception list, if your instance is running on default port
• Include custom port in exception list, if instance is running on custom port
• If you are not using SQL Server Browser service then provide IP + InstanceName + Port for connection to custom port other than default (1433).

Configuring access for Named Pipes

If you have configured named pipes as a network protocol, then to allow access through Windows Firewall, go to configuration frame and enable File and Printer Sharing through the firewall as shown below.

This will allow you to access the SQL Server engine on named pipes behind the firewall.

Using firewall with SQL Server on dynamic ports

If you have configured SQL Server to listen on dynamic ports, even then you can use the firewall for your SQL Server instance. To do this you include the "sqlservr.exe" program in your firewall exception list. Just go to exceptions tab and click on 'Add Program' button.

Browse to "sqlservr.exe", it is in the bin directory of your SQL Server instance installation. Click OK and now you can access this instance of SQL Server without adding specific ports in the exception list.

This configuration would work for only one instance for which you have browsed to sqlservr.exe.

To configure multiple instances along with dynamic ports, add "sqlservr.exe" from each instance in the exception list.

Adding sqlsrvr.exe works for static ports also.

Configuring more complex scenarios

Once you have worked with the above mentioned simple scenarios, you may join these pieces to build more complex configurations of instances, ports and SQL Server Browser services.

Follow these rules to build complex configurations

• Pick each instance one by one for configuration
• After configuration of each instance, perform cumulative testing for instances that have been configured for access behind the firewall
• Do not exclude any previously added exception from exception list

Next Steps

• It is among best practices to turn on Windows Firewall on server machine. But make sure to test properly in simulated environment.
• For enhanced security change your server port to a  port other than the default port and also turn off SQL Server Browser service. With this configuration, you would have to provide IP+InstanceName+ port for connection.
• If you have a firewall other than Windows Firewall, then thoroughly consult its documentation according to your specific environment.
• Click here to read more about Windows Firewall
• Click here to read more about classification and assignment of port numbers

Related Articles

Popular Articles

About the author

View all my tips

Article Last Updated: 2010-01-27