As a SQL Server user, it is important to understand how the server is configured and what port it is listening on. With each release of SQL Server, there are enhancements and changes that may require revisiting old blogs or seeking new information. In this blog post, we will explore how to find the port number on which SQL Server is listening and determine if it is a fixed or dynamic port.

In a previous blog post, I discussed how to find the port number on which SQL Server is listening by reading the ERRORLOG file. However, a reader recently asked if there is a way to determine if the port is fixed or dynamic. The port value is stored in the registry hive of SQL Server and is specific to each instance.

If SQL Server is configured to use dynamic ports, the port value can be found in the TcpDynamicPorts registry key. On the other hand, if a fixed port is used, the TcpPort registry key will contain the port value.

To retrieve this information using T-SQL, I have written a small script that can detect both dynamic and static ports. Here is an example:

DECLARE @ServerName SYSNAME,
        @InstanceID NVARCHAR(128),
        @InstanceName NVARCHAR(128),
        @tcp_port NVARCHAR(10),
        @InstanceKey NVARCHAR(255)

SELECT @ServerName = @@SERVERNAME
SELECT @InstanceName = ISNULL((CAST(SERVERPROPERTY('InstanceName') AS NVARCHAR(128))), 'MSSQLSERVER')

EXECUTE xp_regread @rootkey = 'HKEY_LOCAL_MACHINE',
                   @key = 'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL',
                   @value_name = @InstanceName,
                   @value = @InstanceID OUTPUT

SELECT @InstanceKey = 'SOFTWARE\MICROSOFT\Microsoft SQL Server\' + @InstanceID + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'

EXEC xp_regread @rootkey = 'HKEY_LOCAL_MACHINE',
                @key = @InstanceKey,
                @value_name = 'TcpDynamicPorts',
                @value = @tcp_port OUTPUT

IF @tcp_port IS NOT NULL
    SELECT 'SQL Server (' + @InstanceName + ') uses dynamic TCP port: ' + CAST(@tcp_port AS NVARCHAR(128))
ELSE
BEGIN
    EXEC xp_regread @rootkey = 'HKEY_LOCAL_MACHINE',
                    @key = @InstanceKey,
                    @value_name = 'TcpPort',
                    @value = @tcp_port OUTPUT

    SELECT 'SQL Server (' + @InstanceName + ') on ' + @ServerName + ' uses static TCP port: ' + CAST(@tcp_port AS NVARCHAR(128))
END

By executing this script, you can easily determine whether your SQL Server instance is using a fixed or dynamic port. This information can be useful for troubleshooting network connectivity or configuring firewalls.

As a community, sharing scripts and knowledge is invaluable. If you have any other scripts related to SQL Server configuration that you think would be helpful to other readers, please share them in the comments section below. Together, we can continue to learn and enhance our SQL Server skills.