Configure Windows Firewall to Work with SQL Server

By:   |   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.

access firewall through Run command

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.

Turn on Windows Firewall

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.

view or edit sql server port configuration

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.

view or edit TCP-IP ports for 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 SQL Server Browser Port
Scenario 1 Running Default (1433)
Scenario 2 Running Custom port
Scenario 3 Not Running Default (1433)
Scenario 4 Not Running Custom port

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.

Add SQL Server default port in Windows firewall exception list

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.

Add SQL Server custom port in Windows firewall exception list

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

Add SQL Server browser service in Windows firewall exception list

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.

Allow firewall access for named pipes

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.

Add sqlservr exe file in exception list

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Atif Shehzad Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Saturday, July 27, 2013 - 4:08:11 AM - Naveed Back To Top (26031)

Great Yaar

Thanks

Monday, January 21, 2013 - 12:35:31 AM - Atif Shehzad Back To Top (21588)

Hi Steve, You may Also check following configuration.

In SQL Server configuration manager, Go to SQL Server network configurations.

Right Click on protocols for your instance and go to properties

Under "Flags" tab check "hide instance" to No.

If yes then it may cause to hide your instance on the list.

Thanks

Wednesday, January 16, 2013 - 7:14:15 AM - Steve Back To Top (21478)

Hello - I am not a DBA and have limited knowledge of SQL Server. I am not at the customer's site, so Q&A has been via email. The products are SQL Server 2008 R2, and Windows 7 on the customer's PCs. All users login under Domain accounts, and Integrated Security is always used.

When the customer opens the Data Link dialog to open their DB, they select "Microsoft OLE DB Provider for SQL Server", and then proceed to the Connections tab. The customer drops the list of servers/instances, but the needed server is not listed. Nor does the Refresh button populate the list with the needed server. Other servers/instances are listed. However, the customer can manually enter the server name and proceed successfully. The connection test is good and all application features work as expected. All of the customer's PCs behave this way. The customer tells me that the Browser service is running, and TCP ports 1433 & 1434 are open. Likely a simple setting, but I'm missing something.

What would cause one (1) specific server not to be listed on the Connection tab, while all other servers appear and everything else is normal?

Thank you in advance for whatever info you can provide!

Steve

Thursday, November 1, 2012 - 12:16:11 AM - Atif Shehzad Back To Top (20167)

Durham, actually i am asking to access the instance through SQL Server Management Studio (SSMS). And you have to enter the IP of machine and instance name in SSMS login frame. I would suggest to view the SSMS login frame snaps in my following tip.

http://www.mssqltips.com/sqlservertip/1946/overview-of-the-sql-server-browser-service/

Snap no 2 and 4 of mentioned tip shows the connection through IP\Instance along with port no.

Hope that clarifies.

Thanks

Wednesday, October 31, 2012 - 10:10:19 PM - Durham Back To Top (20166)

Can you please clarify what you mean by: "Now to access the instance just enter IP of machine with instance name without any port number." in senario 2.

Access the instance from where?

Enter the IP of the machine with instance name where?

This would be appreciated a lot!

Thanks,

Durham

Thursday, August 9, 2012 - 11:37:42 AM - Senthil Back To Top (18981)

 

Wonderfule explanation. Thank you very much!!!

Friday, February 12, 2010 - 4:22:54 AM - @tif Back To Top (4889)

 @Mike, Thanks for appreciation.

Thursday, February 11, 2010 - 5:38:26 PM - mdv3441 Back To Top (4885)

Very nice article,thanks so much for the different examples.  If I could only find different scenarios for configuring a WEB site on Windows 2003 Server I would be so happy!  It is just too bad Microsoft makes everyone hunt forever to figure many of "Their" difficult configuration situations.

 

Mike :)














get free sql tips
agree to terms