Foreign Catalog SQL Server Dynamic Port

[jim12321]

When creating a Foreign Catalog SQL Server Connection, a port number is required. However, many sql servers have dynamic ports and the port number keeps changing. Is there a solution for this?

In most common cases, it should allow instance name instead of port number so sql servers can scan and use the correct port. But there is no option for that.

@foreign catalog 

[Kaniz · Accepted solution]

Hi @jim12321, Dealing with dynamic ports in SQL Server connections can be tricky, but there are ways to address this challenge.

Let’s explore a couple of options:

1. Static Port Configuration:

   • By default, SQL Server named instances are configured to listen on dynamic ports. However, you can configure them to start at a specific static port.
   • To check if your SQL Server instance is using a dynamic or static port, follow these steps:
     • Open SQL Server Configuration Manager.
     • Expand SQL Server Network Configuration, then expand Protocols for [instance name].
     • Double-click TCP/IP.
     • In the TCP/IP Properties, check the value in the Listen All setting:
       • If it’s set to Yes, the instance is using dynamic ports.
       • If it’s set to No, proceed to the next step.
     • Scroll down to the IP Addresses section and check the values in IP All:

       • Refer to the following table to determine whether the named instance is listening on a dynamic or static port:

         TCP dynamic ports	TCP port	SQL Server instance using dynamic or static ports?
         Blank	Blank	Dynamic ports
         <Number>	Blank	Dynamic ports (where <Number> is the dynamic port that SQL Server is currently listening on)
         <Number1>	<Number2>	Concurrently listening on a dynamic port <Number1> and a static port <Number2>

       • Additionally, check the IP addresses (e.g., IP1, IP2) to see if any are configured for dynamic or static ports.

2. PowerShell Script:

   • You can use PowerShell to retrieve information about your SQL Server instances and their port configurations.

   • Run the following script in the PowerShell ISE to display relevant TCP/IP details for all installed SQL Server instances:

     clear
     Write-Host "SQL Server 2019"
     Write-Host "====================="
     Get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15.*\MSSQLServer\SuperSocketNetLib\Tcp" |
         Select-Object -Property Enabled, KeepAlive, ListenOnAllIps, @{
             label = 'ServerInstance'
             expression = {$_.PSPath.Substring(74)}
         } | Format-Table -AutoSize
     
     Get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15.*\MSSQLServer\SuperSocketNetLib\Tcp\IP*\"
         | Select-Object -Property TcpDynamicPorts, TcpPort, DisplayName, @{
             label = 'ServerInstance_and_IP'
             expression = {$_.PSPath.Substring(74)}
         }, IpAddress | Format-Table -AutoSize
     # Repeat similar sections for SQL Server 2017, 2016, and 2014
     

   • Adjust the script for other SQL Server versions as needed.

Remember that using static ports ensures consistency, but dynamic ports can be more flexible. Choose the approach that best suits your requirements! 🚀🔍

For more detailed information, you can refer to the official Microsoft documentation¹.