Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Remove Dynamic Port Configuration from a SQL Server Instance


By:   |   Last Updated: 2018-12-19   |   Comments   |   Related Tips: More > SQL Server Configurations

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:

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


Last Updated: 2018-12-19


get scripts

next tip button



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

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools