Problem
If your SQL Server instance has both static and dynamic ports configured, you may occasionally experience connection issues after a failover. Also, when using a single static port, it is easier to manage the Firewall rules. In this tip we will look at port settings for a SQL Server instance and how you can make changes.
Solution
If you have experienced connection issues with dynamic ports, to fix this issue you will need to delete the dynamic port entry, including deleting the zero in the IP Addresses settings for the instance. Books Online clearly says, at the time of writing, “delete the 0” as stated in bullet point 3 in the referenced link. To be thorough, you should then restart the SQL Server instance. If it is a cluster then failover the cluster and fail it back as well.
We will walk through how to tell if dynamic ports are being used and how to adjust the settings.
Step 1 – See if SQL Server Is Using Both Static and Dynamic Ports
Run this T-SQL code to see if you have any active connections using both static and dynamic ports.
SELECT distinct @@SERVERNAME as [Instance], local_tcp_port FROM sys.dm_exec_connections WHERE local_tcp_port is not null
An example screen shot of the result:
The local_tcp_port should be 1435 only. The 49981 shows a dynamic port. If the TSQL above returns no port information then follow a procedure from tip 2495 by Ashish Kumar Mehta on how to identify the SQL Server TCP IP port being used.
Step 2 – Discover the nodes that the instance is hosted on
You will need to configure the port information on every node that the instance is hosted on. If your instance is hosted on a Windows Cluster, then open Failover Cluster Manager. Go to Server Manager > Features > Failover Cluster Manager. See tip 2672 by Brady Upton to find out more about this.
Also, this T-SQL code will show the active node name for an instance.
Select @@servername as [Instance name], SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as 'Node'
Step 3 – Adjust the TCP Port Settings with SQL Server Configuration Manager
Remote desktop to each of the nodes hosting the instance in turn. Start SQL Server Configuration Manager. If using a GUI open a run command window using "Windows+R" then type SQLSERVERMANAGER11.msc (SQL Server 2012), SQLSERVERMANAGER12.msc (SQL Server 2014), SQLSERVERMANAGER13.msc (SQL Server 2016), SQLSERVERMANAGER14.msc (SQL Server 2017) to open it. SQL Server Configuration Manager is available in many versions. Check this Microsoft article for details.
If using a Windows Server with minimal shell configuration, then start the Microsoft Management Console (mmc) and add the SQL Server Configuration Manager snap in. Type mmc.exe and press Enter. Click on File and Add/Remove snap in.
If two SQL Server Configuration Manager snap-ins are present, try each one and choose the newest version as it will display both the current and older versions connection information. Note that older mmc snapins cannot edit newer SQL Server configuration information.
The SQL Server Configuration Manager does many things and so it is worth your time going through each area to understand the functions.
Browse to the SQL Server Network Configuration. Then the protocols for the instance.
Then open the TCP/IP protocol name and then IP Addresses.
A tricky bit
If the setting in the ‘Protocol’ tab for ‘Listen All’ is set to ‘Yes’ then it will affect how the information in the next tab is used.
The ‘Yes’ means the section under the title IPALL will be used. (See the note in purple at this Microsoft site, it explains it.)
Remove any entry from the TCP Dynamic Ports area, including the zero, if present. Click OK to apply the change.
RDP to the other nodes and remove any entry from the TCP Dynamic Ports area on that node.
Step 4 – Restart and Failover
Failover the instance and re-run the TSQL to confirm it has no dynamic port open. Fail the instance back and check the port again.
Considerations and Definitions
- What is a port? A port number is an ‘end-point’ of a service. A way of connecting a process to a server. A port number is held in both User Datagram Protocol (UDP) and Transmission Control Protocol (TCP) packets. A port makes sense when associated with a protocol and IP number or host.
- What are some common port numbers? See this list of TCP and UDP port numbers at Wikipedia.
- Why use static ports? Because that is easier to configure in a Windows Server Firewall.
- Isn’t using a static port a security risk? No, changing the port is not a robust security step.
- What if I cannot failover right away? Make the change and go back and check it when you can.
- Why does the dynamic port change? On startup SQL Server checks that the port is still free. If not, it must change the port number. That means your firewall rule will no longer work.
- Why does the dynamic port not change? The dynamic port gets cached in the Windows Registry and will be used next start-up given the port is still free.
Static Port Allocation
If you configure a SQL Server instance to use a static port and you restart the instance. Clients can only connect to that instance using that port. The SQL Server Browser service will direct clients to the port specified.
Instances can be organized in this way. Specifying a different port to each instance means you have a cross check for the clients to use. If the connection string specifies both the instance and port it means they need to get both correct to connect.
Dynamic Port Allocation
When the instance of SQL Server starts, it requests a free port number from the operating system. See this Microsoft reference on How to configure SQL Server (2000-2008) to listen on a specific port.
Firewall tips
At some point you may need to check your firewall rules. See this PowerShell tip on listing Firewall Rules.
This Microsoft site lists the PowerShell commands relating to Firewalls.
Next Steps
- See Books Online as it clearly says, at the time of writing, ‘delete the 0’ in the TCP Dynamic Ports.
- Check out tip 2495 by Ashish Kumar Mehta How to identify SQL Server TCP IP port being used
- Check out tip 2928 by Thomas LeBlanc Updating the TCP Port on a SQL Server Cluster Node
- Check out tip 3542 by K. Brian Kelley Automated way to get all port information for SQL Server instances
