Configuring firewall rules

Each SQL Server instance running on your server is using a different port number to listen for incoming connections, but during the installation of the SQL Server, there are no firewall rules created on your local firewall. So SQL Server is perfectly accessible locally, but not from remote hosts if the local firewall is active. You can run with a built-in firewall on the Windows Servers or have some third-party software in your environment, which requires additional configuration.

During the installation of the SQL Server, you had to make a choice between deploying SQL Server as a default instance or a named instance. SQL Server default instance is listening on port 1433, which you can verify in the SQL Server configuration manager tool. This port is set as static and will not change over time. Named instances, on the other hand, use a randomly selected port, which may not be fixed and can change after a system reboot, because named instances use dynamic ports as a default option.

It's advised to change the dynamic port to static so that the port number does not change and this does not have any impact for any security configuration like service principal name, which we'll discuss in another chapter.

The following screenshot gives a good idea of the default instances:

Once we know what port our SQL Server instance is listening to, we need to configure the firewall to allow the traffic to our SQL Server service. Windows Server comes with a built-in firewall that can be controlled via GUI, command line, and PowerShell.

We will add three different rules to the firewall with the PowerShell tool:

• The first rule is for the SQL Server service with the proper port number. We have seen the port number in the configuration manager. For a default instance, this is 1433; for a named instance, the port number would be mostly random on each system.
• The second rule is used for dedicated admin connection, which is used for troubleshooting the system. Enabling just the firewall rule does not allow you to remotely connect to DAC session; this also has to be turned on in the SQL Server configuration and we'll cover this topic later.
• The third rule is for a service called SQL Server browser, which is used for connection to the named instances:

New-NetFirewallRule -DisplayName “SQL Server Connection” –Protocol TCP -Direction Inbound –LocalPort 1433 -Action allow
New-NetFirewallRule -DisplayName “SQL Server DAC Connection” –Protocol TCP -Direction Inbound –LocalPort 1434 -Action allow
New-NetFirewallRule -DisplayName “SQL Server Browser Service” –Protocol UDP -Direction Inbound –LocalPort 1434 -Action allow

If you're running more instances on the server, or any other services such as analysis services or reporting services, or you use any solutions for HA/DR such as mirroring or always on, then you need to carefully examine what firewall rules are needed and the list may get much longer than the three basic rules we have seen.