Remove Dynamic Port Configuration from a SQL Server Instance

By:   |   Comments   |   Related: > SQL Server Configurations


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.


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:

sql server port connections

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. 

failover cluster manager

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.

sql server configuration manager snapin

Browse to the SQL Server Network Configuration. Then the protocols for the instance.

sql server configuration manager

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.

sql server configuration manager tcp ip properties

The 'Yes' means the section under the title IPALL will be used. (See the note in purple at this Microsoft site, it explains it.)

sql server configuration manager tcp ip properties

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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Graham Okely Graham Okely is a SQL Server DBA and has been working with database systems since 1984 and has been specializing in SQL Server since 2007.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

get free sql tips
agree to terms