Static DAC ports for named instances

Question

Using SQL 2016 Enterprise Edition

The default instance will listen on 1433 and the DAC port will listen on TCP 1434. SQL Browser will listen on UDP 1434. Any subsequent named instances will get a dynamically allocated port from windows in the range 49152 to 65535 as will their DAC ports. Connection requests (including DAC) to those named instances will make a call to SQL Browser and told the port number.

You can use the SQL Server Configuration Tool to change the port for the named instance to a static port number, but there is no option to set the DAC port to a static port number.

When I suggested to the networks team that I'd like the entire port range opened up for remote DAC connectivity, they were less than enthusiastic.

Is there any way to set the DAC port to a known, static port number for named instances?

Answer 1

Taken from the Microsoft Support site:

To configure a static port for the specialized Dedicated Administrator Connection (DAC), you must update the registry key that corresponds to your instance. For example, the registry key may be the following:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp

Note The "X" in "MSSQL.X" is a number that indicates the directory where the instance is installed for SQL Server 2005 or the instance name for SQL Server 2008.

Update the TcpDynamicPorts entry to the port number you wish to use.

Although the article talks about SQL Server 2008, this works for all Windows-based versions from 2005 and above. SQL Server on Linux uses a different mechanism, which I am not yet aware of.

You'll need to stop-and-start the SQL Server instance for the change to take effect. Once you've restarted the instance, check the error log for the following message, indicating which port the SQL Server Dedicated Admin Connection is using:

Dedicated admin connection support was established for listening remotely on port xxxx.

I use this script to configure the Remote DAC port:

/*
    Reads the instance's registry settings (via xp_instance_regread
    as opposed to xp_regread), to determine if the Dedicated 
    Aministrator Connection is configured to use the desired TCP 
    port number.

    2022-11-29  Hannah Vernon
*/
SET NOCOUNT ON;
SET XACT_ABORT ON;

DECLARE @desired_dac_port   varchar(11)     = '1432';

IF NOT EXISTS
(
    SELECT 
        1 
    FROM 
        [sys].[configurations] sc
    WHERE  
        sc.[name] = N'show advanced options' 
        AND sc.[value] = 1
)
BEGIN
    PRINT N'Enabling "Show Advanced Options" configuration.';
    EXEC sys.sp_configure 
          @configname = 'show advanced options'
        , @configvalue = 1;
    RECONFIGURE;
END;

IF NOT EXISTS
(
    SELECT 
        1 
    FROM 
        [sys].[configurations] sc
    WHERE  
        sc.[name] = N'remote admin connections' 
        AND sc.[value] = 1
)
BEGIN
    PRINT N'Enabling Remote Dedicated Admin Connections.';
    EXEC sys.sp_configure 
          @configname = 'remote admin connections'
        , @configvalue = 1;
    RECONFIGURE;
END
ELSE
BEGIN
    PRINT N'Remote Dedicated Admin Connections are already enabled.';
END;

DROP TABLE IF EXISTS #dac_port;
CREATE TABLE #dac_port
(
      [value]   nvarchar(128)       NOT NULL
    , [data]    varchar(255)        NULL
);

INSERT INTO #dac_port
(
      [value]
    , [data]
)
EXEC sys.xp_instance_regread 
      @rootkey = N'HKEY_LOCAL_MACHINE'
    , @key = N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp'
    , @value = N'TcpDynamicPorts';

IF NOT EXISTS
(
    SELECT *
    FROM #dac_port dp
    WHERE dp.[value] = N'TcpDynamicPorts'
        AND dp.[data] = @desired_dac_port
)
BEGIN
    PRINT N'Setting Dedicated Admin Connection to use port ' + @desired_dac_port;

    EXEC sys.xp_instance_regwrite 
          @rootkey = N'HKEY_LOCAL_MACHINE'
        , @key = N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp'
        , @value_name = N'TcpDynamicPorts'
        , @type = N'REG_SZ'
        , @value = @desired_dac_port;
END
ELSE
BEGIN
    PRINT N'The dedicated admin connection is already configured on port ' + @desired_dac_port;
END;