In this blog post, we shall discuss how to Configure SQL Server Instance to listen on a specific TCP Port using SQL Server Configuration Manager. The default instance of Microsoft SQL Server listens on TCP Port 1433. This is the default port for communication between Microsoft SQL Server and its applications. Here is a similar article on Setup is unable to access the SQL UDP Port 1434 on the specified SQL Server, and how to enable or disable WinRM via the command-line.
Note: The Named instances of the Database Engine and SQL Server Compact are configured for dynamic ports. Because the Database Engine might change the selected port every time the SQL Server service starts.
This could present a challenge if there is a firewall between the application server and the SQL server, as is typically the case in most deployments. This is what we need to solve in this guide.
See the Concept of Ansible on Windows using Cygwin, how to change the default RDP port in Windows, and “Active Directory Ports: Service and network port requirements for Windows“.
SQL Server Firewall Rules
To ensure secure access to computer resources, it is crucial to properly configure firewall correctly. Failure to configure the firewall correctly can result in blocked attempts to connect to SQL Server. When connecting to a named instance through a firewall, it is advisable to configure your Windows Firewall to enable the appropriate ports for the database engine to listen on a specific port.
Use the Windows Firewall with Advanced Security MMC snap-in to view and configure all firewall profiles. The Windows Firewall item in Control Panel only configures the current profile.
Also, see how to create a Windows firewall rule on Windows, Remote WMI Connection: How to enable or disable WMI Traffic Using Firewall UI, and How to enable or disable a Remote WMI Connection in Windows.
Why Hardcode a specific Port?
To emphasize on what we have discussed above. The default instance of a SQL Server Database Engine listens on TCP port 1433. Named instances on the other hand are by configured by default to use dynamic ports.
This means that they select an available port when the SQL Server service is started. Because the port selected might change every time that the Database Engine is started. This can pose a bit of a challenge if there is a firewall between the application server and the SQL server, which in most deployments there will be
- Also, for enhanced security. Most organisations and applications resort to changing the known standard port from 1433 to 1401 or something else. Keep in mind, TCP/IP permits port scanners to query open ports. Therefore the principle of changing the TCP/IP port is not a robust security measure.
Note: An alternative to configuring a named instance to listen on a fixed port is to create an exception in the firewall for a SQL Server program such as sqlservr.exe (for the Database Engine).
The port number won’t appear in the Local Port column of the Inbound Rules page when you’re using the Windows Firewall with Advanced Security MMC snap-in. It can be difficult to audit which ports are open.
Configure Microsoft SQL Server Database Engine to Listen on a particular Port
Before proceeding with these steps, please note that if the Listen All setting on the Protocol tab is set to “Yes”. Then only TCP Port and TCP Dynamic Port values under the IPAll section will be used and individual IPn sections will be ignored in their entirety.
On the other hand, if the Listen All setting is set to “No”, then the TCP Port and TCP Dynamic Port settings under the IPAll section will be ignored and the TCP Port, TCP Dynamic Port, and Enabled settings on the individual IPn sections will be used instead.Note: Each IPn section has an Enabled setting with a default value of “No” which causes SQL Server to ignore this IP address even if it has a port defined.
Start SQL Server Configuration Manager. Here is a similar issue: Setup is unable to access the SQL UDP Port 1434 on the specified SQL Server.
Expand SQL Server Network Configuration, select the Database Engine instance you want to edit (Protocols for <instance name>). Right-click or double-click TCP/IP.
Navigate to the IPAddress Tab as shown below.
Hard code the TCP/IP Port
Like i said, I will ignore TCP/IP Properties dialog box in the format IP1, IP2 (IPn). As you can see, I have set the IPn section to enabled. As such, we have to work with the IPALL section. Enter your TCP ports as shown below.
In the console pane, select SQL Server Services. In the details pane, right-click SQL Server (<instance name>) and then select Restart, to stop and restart SQL Serve
Note: The Database Engine begins listening on a new port when restarted.
However the SQL Server Browser service monitors the registry and reports the new port number as soon as the configuration is changed. Even though the Database Engine might not be using it. Restart the Database Engine to ensure consistency and avoid connection failures.
I hope you found this blog post on how to configure SQL Server Instance to listen on a specific TCP Port helpful. If you have any questions, please let me know in the comment session.
