SQL Server Firewall Settings: A Complete Guide

Stephen West | January 12th, 2017 | SQL |

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.

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 or fixed 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

Follow the below-mentioned procedures to configure firewall for SQL server 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

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

Transaction Log Restore