Home » SQL » Configure Windows Firewall Ports for SQL Server Settings
Stephen West ~
Modified: January 5th, 2024 ~ SQL ~ 4 Minutes Reading
SQL server is a database management system developed by Microsoft and it is used to manage and store information on the SQL database. A database is a collection of data that is organised in such a way so that it can easily be managed, accessed, and updated.
To prevent unauthorised access of a database, there is a need of firewall settings on the SQL server. SQL server firewall settings can be done by using SQL server configuration manager. Therefore, in this post, we are going to discussed how to configure firewall for SQL Server by using SQL Server configuration manager.
Table of Content
- Ports in Use by SQL Server
- Configuration Manager
- Conclusion
Ports Used By SQL Server
Static Port: At the time of SQL server configuration, default instance of SQL Server listens on port 1433. This is called static, fixed or SQL Server default port. The port can be changed for security purpose or client application requirement.
Dynamic Port: The named instances (including SQL Server Express) are configured to listen on dynamic ports, by default. It means that, when the Database Engine starts, it finds an available port every time and uses that port number.
Configure Firewall For SQL Server Using Configuration Manager
As we know SQL Server default port, follow the below-mentioned procedures to configure the Windows firewall to allow SQL Server access using SQL server configuration manager:
For Static Port:
- Go to Start>Run and type WF.msc and then click on OK button
- Under the Windows Firewall with Advanced Security, right-click on Inbound Rules, and then click on New Rule
- In the Rule Type box, select the option Port, and then click on Next button
- In the dialog box of Port, select the option TCP. Then, select the option Specific local ports, after that type the port number 1433 for the static instance. After that click on Next button
- Select Allow the action under the Action dialog box and then click on Next button
- Now, Under the Profile dialog box, select any profiles which you want to connect to the SQL server, and then click on Next button
- Type a name and description of the rule, in the Name dialog box and then click on Finish button
For Dynamic Port:
- Go to Start>Run and type WF.msc and then click on OK button
- Under the Windows Firewall with Advanced Security, right-click on Inbound Rules, and then click on New Rule
- In the Rule Type box, select Program, and then click on Next button
- Now, under the Program dialog box, select the option This program path. After that, click on Browse, and go to the SQL Server instance that you want to access by using the firewall, and then click on Open. The location of SQL Server by default is C:\ProgramFiles\MicrosoftSQLServer\MSSQL11.MSSQLSERVER\MSSQL\Binn\Sqlservr.exe. After that, click on Next
- Select Allow the action under the Action dialog box and then click on Next
- Now, Under the Profile dialog box, select any profiles which you want to connect to the SQL server, and then click on Next button
- Type a name and description of the rule, in the Name dialog box and then click on Finish button
Advanced SQL Database Recovery Software
Oftentimes, we have observed that users suffer with several SQL Server database issues. Most of them are data corruption issues. However, sometimes, user also faces data deletion errors. Well to safeguard the entire database from such critical errors, users must have an advanced software to recover their SQL Server database.
Therefore, the below mentioned software can help users fix damaged or deleted SQL Server data files. It works on both MDF & NDF files along with several advanced features that no other tool possess.
Download Now Purchase Now
Conclusion
In this post, we have discussed the procedures of SQL server firewall settings using SQL server configuration manager for static or dynamic port. By default, the value of static port is 1433. This port number can be changed for security purpose or client application requirement. Now, users know how to configure the Windows firewall to allow SQL Server access with ease.
If it is changed than, it becomes dynamic port. By following the above-mentioned steps, users can configure firewall for SQL server to prevent unauthorised access to a network.
Введение
В статье описано, как разрешить подключаться к базе данный MS SQL Server через брандмауэр
Настройка
В Брандмауэре необходимо разрешить входящие соединения.
Откройте Брандмауэр Windows — Пуск > Панель управления > Администрирование > Монитор брандмауэра Защитника Windows в режиме повышенной безопасности
|
|
На следующем шаге Укажите протокол TCP и порт — 1433 |
|
Выберите Разрешить подключение |
В настройках профилей поставьте флажки на всех 3 пунктах |
|
Укажите имя для созданного правила |
На клиентском компьютере в SQL Management Studio не обязательно вводить адрес нужного экземпляра БД. |
Теперь можно подключаться с этого компьютера к базе данных.
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 | 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.
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
May 2, 2016 updated by
Leave a reply »
By default, Windows Firewall doesn’t allow inbound / outbound requests for SQL Server. If you try to connect to a SQL Server instance from network, you might get the error saying “The server was not found or was not accessible”. In this tutorial we’ll show you 2 ways to configure Windows Firewall to allow SQL Server access.
Method 1: Allow SQL Server Access Through Control Panel
- Open Control Panel in Large icons or Small icons view, click on Windows Firewall.
- Click the link “Allow a program or feature through Windows Firewall” on the left of window.
- You will now see a list with all the apps which are allowed to communicate through the Windows Firewall. To change the rules, you need to click the Change Settings button. The list of rules will no longer be gray and you will be able to edit it.
- Click the “Allow another program…” button.
- From the “Add a Program” window, click the Browse button.
- Navigate to the installation path for your SQL Server instance and select sqlservr.exe, and click Open. In my example, the location is
C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn\sqlservr.exe.
- You’ll back to the “Add a Program” window and see SQL Server is added to the list. Click the Add button.
- SQL Server now appears in the list of Allowed programs and features. You can check any of the location types: private or public. When done, press OK.
Method 2: Allow SQL Server Access Through Command Prompt
- Open an elevated Command Prompt.
-
You can run the Netsh advfirewall command to open all ports for SQL Server connections. Assuming the path of your SQL Server service is C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn\sqlservr.exe, copy / and paste the following commands in the Command Prompt, press Enter after each.
netsh advfirewall firewall add rule name="SQL Server TCP" protocol=tcp dir=in action=allow program="C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn\sqlservr.exe" enable=yes profile=any localip=any localport=any remoteip=any remoteport=anynetsh advfirewall firewall add rule name="SQL Server UDP" protocol=udp dir=in action=allow program="C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn\sqlservr.exe" enable=yes profile=any localip=any localport=any remoteip=any remoteport=any
- When it’s done, you’ve successfully opened up all ports to allow SQL Server access through Windows Firewall.
- Previous Post: How to Reset Start Menu Layout to Default in Windows 10
- Next Post: How to Block An Application from Running in Windows 10
(Updated 2016-10-24)
Intro
If you want to allow remote computers on your network to access an instance of Sql Server running on one machine, you’ll have to add some new Incoming rules to the Windows Firewall, or no connections can come through. So, let’s show you what has to be considered and how to achieve this.
Sql Server Instance Types
Before you can configure the Windows Firewall to allow remote access to your Sql Server instance, it’s important to understand the “instance type” of the Sql Server that you are running. For Sql Server, its “instance type” can either be the default instance, an unnamed instance, or a named instance. Which one you have determines which port you have to open in Windows Firewall.
- When SQL Server is installed as the default instance, it does not require a client to specify the name of the instance to make a connection. The client only has to know the server name. This typically uses Port 1433 in the TCP protocol.
- A named instance is identified by the network name of the computer plus the instance name that you specify during installation (i.e. \\ComputerName\SqlExpress or something similar). The client must specify both the server name and the instance name when connecting. This typically uses the Sql Server Browser service to find the Sql Server instance, and that requires access to Port 1434 UDP protocol.
Windows Firewall Setup to Allow Remote Access
So, in order to allow remote access to the Sql Server instance, you will need to add these Incoming rules to the Windows Firewall:
- Add an Incoming rule to all access the application SqlServr.exe (C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\Binn\sqlservr.exe)
- If you want to access the Sql Server Instance from other computers by using the computer name (i.e. {ComputerName}\SQLEXPRESS, you will need to add an Incoming rule to allow access to the application SqlBrowser.exe (C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe). Note: If you are only going to use the IP address {xxx.xxx.xxx.xxx}\SQLEXPRESS, then you will not need this rule for SqlBrowser.exe.
- Add an Incoming rule for the correct port (1433 TCP or 1434 UDP depending on Sql Server instance type (from above)).
You can use these handy Poweshell scripts to add the above Firewall rules:
|
# http://mattslay.com/opening-up-windows-firewall-to-allow-sql-server-to-allow-remote-connections/ # https://blog.brankovucinec.com/2015/12/04/scripts-to-open-windows-firewall-ports-for-sql-server/ Write-host Enabling SQLServer Default instance port 1433 TCP Inbound New-NetFirewallRule -DisplayName «SQL Server TCP Port 1433» –Direction inbound -Protocol TCP –LocalPort 1433 -Action Allow Write-host Enabling SQLServer Named Instance port 143 UDP Inbound New-NetFirewallRule -DisplayName “SQL Server UDP Port 1434” -Direction Inbound –Protocol UDP –LocalPort 1434 -Action allow Write-host Enabling SQLServer EXE Application rule New-NetFirewallRule -DisplayName “SQL Server EXE” -Direction Inbound -Program «C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\Binn\sqlservr.exe» -Action allow Write-host Enabling SQLServer Browser Application rule New-NetFirewallRule -DisplayName “SQL Server Browser” -Direction Inbound -Program «C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe» -Action allow |
You should see the new rules here:
Sql Server Full version
For the full version of Sql Server (not Express), by default it installs as the default instance unless you specify an instance name. So, the next step to allow remote connections to the default instance is to add a Port Rule to open Port 1433 for the TCP protocol.
Sql Server Express
SQL Server Express always installs by default as a named instance unless you force a default instance during setup. So, if you are running a named instance of Sql Server (or Sql Server Express), and you want to allow remote connections to it, add an Incoming port rule for Port 1434 for the UDP protocol.
Since I was running a named instance, I found that I did not need the 1433 TCP port rule at all, I only needed the 1434 UDP port rule (along with the two application rules) to get it working on my network.
Other steps to check
This post only covers the Windows Firewall. There are other steps required in configuring Sql Server itself:
- Enabling the correct protocols, and make sure Sql Server has the option “Allow remote connections to this server” checked on the Connections tab of the Server properties. You can access this setting in Sql Server Management Studio tool.
- Enable the correct protocols in the Sql Server Configuration Management Tool, under the Sql Server Network Configuration node. Honest, I wasn’t sure exactly which I needed, so I enabled all 3. I’m pretty sure TCP/IP was disable by default.
Some details pulled from these links:
https://technet.microsoft.com/en-us/library/ms165614%28v=sql.90%29.aspx
and a comment by user J_m on this Technet article: https://technet.microsoft.com/en-us/library/ms175043%28v=sql.110%29.aspx