There are a lot of very good webpages explaining what a Dedicated Admin Connection is and how to enable it, but few sources show you how to figure out on what TCP-Port number your DAC-Connection is currently listening (if on any) or how to set up your SQL Instance so that the DAC Connection would always come up listening on the same Static TCP-Port of your choice.

Why would you want to do that, if all you have to do is to prefix your SSMS connection with ADMIN: ? Well, the ADMIN: part works assuming that your SQL Server Browser Service is running and able to respond to client requests over the network. ADMIN: prefix simply asks the SQL Server Browser Service: please tell me which TCP Port is DAC of that Instance listening on? Here is a client at 10.0.0.6 asking the SQL Server at 10.0.0.5 that very question using the clnt_ucast_dac UDP packet:

DAC-clnt_ucast_dac packet

To which SQL Browser happily replies:

DAC-svr_resp_dac packet

As you can see, the Browser Service answers without asking for any authentication - one more reason why many DBA’s recommend turning off that service. If the service is either turned off or unreachable (due to for example firewall restrictions) placing the ADMIN: prefix in front of the SQL Instance name/IP will result in: error: 43 - An error occurred while obtaining the dedicated administrator connection (DAC) port. Make sure that SQL Browser is running, or check the error log for the port number). To connect using TCP Port number, without relying on the Browser, instead of the ADMIN: prefix we would have to use the same method as with regular connections: append a numerical port-number-suffix to our SQL Instance Name (for example to connect to SQL Instance running on host Host1 with DAC enabled and listening on port 51434 we would have to use: Host1,51434). But the problem is: we have to know the TCP-Port number on which DAC is listening (if enabled) first, in order to use it that way. So, here are a few ways to figure out that number if we do not know it:

1 . Assuming SQL machine is still responsive enough, search through the SQL ERRORLOG for string "Dedicated admin connection" - an entry specifying the port number should appear right after the local connection providers and before SPN Registration:

DAC Error Log entry

If you prefer to do the same search by running sys.sp_readerrorlog you can use the following query:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
DECLARE @SQL_Start_Date DATETIME
      , @now            DATETIME

SELECT @SQL_Start_Date = DATEADD(MINUTE, -1, [sqlserver_start_time])
	 , @now = GETDATE()
FROM [sys].[dm_os_sys_info]
--SELECT @SQL_Start_Date AS [SQL Start Date]

DECLARE @maxLog    INT
      , @searchStr VARCHAR(256)
      , @startDate DATETIME;

SELECT @searchStr = 'dedicated'
     , @startDate = DATEADD(DAY, -1, @now)

DECLARE @errorLogs TABLE ([LogID] INT, [LogDate] DATETIME, [LogSize] BIGINT);

DECLARE @logData TABLE ([LogId] INT NOT NULL, [LogDate] DATETIME NOT NULL, [ProcInfo] VARCHAR(64), [LogText] VARCHAR(2048));
DECLARE @logDataTmp TABLE ([LogDate] DATETIME, [ProcInfo] VARCHAR(64), [LogText] VARCHAR(2048));

INSERT INTO @errorLogs EXEC sys.sp_enumerrorlogs;

SELECT TOP 1
       @maxLog = [LogID]
FROM @errorLogs
--WHERE [LogDate] <= @startDate
ORDER BY [LogDate];

WHILE @maxLog >= 0
BEGIN
    DELETE FROM @logDataTmp
	INSERT INTO @logDataTmp EXEC sys.sp_readerrorlog @maxLog, 1, @searchStr;
    PRINT(CONCAT('@maxLog: ', @maxLog))
	IF EXISTS (SELECT 1 FROM @logDataTmp)
	BEGIN
		INSERT INTO @logData
		SELECT @maxLog, [tmp].[LogDate], [tmp].[ProcInfo], [tmp].[LogText]        
		FROM @logDataTmp AS tmp
	END
    SET @maxLog = @maxLog - 1;
END

SELECT [LogId], [LogDate], [LogText]
FROM @logData
--WHERE [LogDate] >= @startDate
ORDER BY [LogDate] DESC;

This should narrow the results to just the entries matching your keyword search (may work quicker than GUI, depending on the size of your log):

DAC Error Log query search results

2 . Alternatively: if you do not have access to SQL Error Logs on the host where your SQL Instance is running, issue a PowerShell command (a modified-for-SQL version of this solution):

1
Get-NetTcpConnection | select local*, remote*, state, @{Name = "Process"; Expression = {(Get-Process -Id $_.OwningProcess).ProcessName }} | Where {$_.Process -Like "sqlservr"} | Format-Table

and looking at the ports with state of Listen/Established you can get a rough idea of which ports are being used by the regular client-connections (i.e. Established, in this example 51433) and which one of the remaing ports is most likely used for DAC, if any (in this example 51434). 0.0.0.0 shown in LocalAddress column means the port listens on all IP addresses available. This is assuming that you have just one SQL Instance running on that machine.

Get-NetTcpConnection

If the Instance is running AlwaysOn Availability Group(s) you would most likely also see 5022 Hadr_endpoint under Local and Remote ports.

3 . Finally the most reliable way: At start-up SQL Engine reads the registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\NameOfYourSqlInstance\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp

Based on the value of TcpDynamicPorts entry it makes the DAC available on the port specified there. If needed, for example if there is a port conflict, SQL will quietly override that registry setting and assign itself (to DAC) a different available random high-port, without telling you anywhere that there was a need for that override (the only way you can tell is if you monitor your registry changes).

Here is an example of a different manually-assigned TCP DAC Port (54321 in this case):

DAC Registry Random TCP Port

To get that registry key value(s) using PowerShell run the script below on your SQL host (important: as $SQLService parameter provide the correct SQL DB Engine Service Display Name, which you can get from the DisplayName column of the: Get-Service -name *SQL* PowerShell command):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
$SQLService = "SQL Server (Inst1)"; # <== IMPORTANT: CHANGE THIS TO MATCH YOUR INSTANCE NAME!
$SQLInstancePath = "";

$SQLServiceName = ((Get-Service | WHERE { $_.DisplayName -eq $SQLService }).Name).Trim();

If ($SQLServiceName.contains("`$")) { $SQLServiceName = $SQLServiceName.SubString($SQLServiceName.IndexOf("`$")+1,$SQLServiceName.Length-$SQLServiceName.IndexOf("`$")-1) }
foreach ($i in (get-itemproperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server").InstalledInstances)
{
  If ( ((Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL").$i).contains($SQLServiceName) )
  { $SQLInstancePath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\"+`
  (Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL").$i}
}

$DACPath = "$SQLInstancePath\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp"
Get-ItemProperty -Path $DACPath

DAC Registry Random TCP Port PowerShell

What if you leave that string entry blank? In the SQL ErrorLog you will see 2 messages, first of which is somewhat misleading:

Dedicated admin connection support was not started because of error 0xd, status code: 0x1. This error typically indicates a socket-based error, such as a port already in use.

TDSSNIClient initialization failed with error 0xd, status code 0x23. Reason: No or more than one dynamic TCP/IP port is configured for Dedicated Administrator Connection in registry settings. The data is invalid.

What if you delete the TcpDynamicPorts completely from the key? Same thing, except the second message is more accurate: TDSSNIClient initialization failed with error 0x2, status code 0x22. Reason: Unable to retrieve dynamic TCP/IP ports registry settings for Dedicated Administrator Connection. The system cannot find the file specified.

What if you add a TcpPort entry below the TcpDynamicPorts just like it exists in the \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15.INST1\MSSQLServer\SuperSocketNetLib\Tcp\IPAll key, hoping that the manual static-port entry will work alongside the dynamic one, just like it does under SuperSocketNetLib\Tcp\IPAll?

DAC Manual Static TCP Port

SQL will completely ignore the static DAC TcpPort and will read/overwrite (if needed) just the TcpDynamicPorts string value. The logic being: unlike with regular client connections you can establish only one DAC connection, and it’s better to override DAC’s port-value at startup if necessary (hence Dynamic having priority) than to leave SQL without DAC whatsoever.

In fact, the only thing that SQL browser service does, in order to translate for us the ADMIN: prefix into the DAC’s TCP Port number, is to monitor/read the string value under the TcpDynamicPorts registry key. Here is part of sqlbrowser.exe activity as shown by Process Monitor the moment I manually changed the TcpDynamicPorts string value:

sqlbrowser.exe reading a change in registry

Notice how the Browser reads the TcpDynamicPorts key only and ignores the TcpPort key (even though I entered/changed its value as well):

sqlbrowser.exe reading a change in registry details

Interestingly, this also means that after such a manual registry-change (by somebody else, without us knowing about it and without the SQL Engine restart) the Browser’s translation of the ADMIN: prefix will be wrong. It will match the new registry value rather than the actual TCP Port on which the DAC was originally started. In this case the ADMIN: prefix instead of helping us will result in network-related or instance-specific error and unless we notice the discrepancy between the registry entry and results of the first 2 methods above (SQL Error Logs and Get-NetTcpConnection PowerShell command) we may have a hard time figuring out why we can’t connect to DAC using the ADMIN: prefix - one more reason not to rely on the SQL Browser but rather on static port assignments and the numerical suffix in the connection.

So, how do you set that “Static” TCP Port for the DAC Connection, so that next time you are called to rescue the hanging instance you don’t have to guess its DAC port number? Well, you simply set it by assigning TcpDynamicPorts a port-value that:

  1. is very unlikely to be overtaken/overwritten due to port conflict
  2. matches your organization-wide port numbering scheme/design (i.e: is secure and easy to remember for emergency situations)

To ensure that the DAC port has not changed at SQL Startup you can setup a startup procedure that will alert you of any discrepancies between the expected and the actual registry entry values, for example something like this:

Reg-Entry-Monitor-Ok

or notify you that everything is ok (how to setup such notifications is a subject for a separate blog-post):

Reg-Entry-Monitor-Ok

And here is the PowerShell script that will set both the DAC static-port and the really-static port for the regular client-connections (incidentally, it also turns off IPv6 for SQL Engine only, without affecting any other services that might need IPv6 addresses):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
# Set Static Variable Values:
$SQLService = "SQL Server (Inst1)"; # Replace <SQL Server (Inst1)> with your SQL Service Name as shown in DisplayName by ps command: Get-Service -name *SQL*
$IpAll_StaticTcpPort = "51433"      # Replace with Static TCP Port Number of your choice for regular TCP Client Connections
$DAC_StaticTcpPort = "51434"        # Replace with Static TCP Port Number of your choice for Dedicated Admin Connection


function Test-RegistryValue {
    param (

            [parameter(Mandatory=$true)]
            [ValidateNotNullOrEmpty()]$Path,

            [parameter(Mandatory=$true)]
            [ValidateNotNullOrEmpty()]$Value
    )

    try {
            Get-ItemProperty -Path $Path | Select-Object -ExpandProperty $Value -ErrorAction Stop | Out-Null
            return $true
        }
    catch {return $false}
}


# Get SQL Server Instance Path:
$SQLInstancePath = "";

$SQLServiceName = ((Get-Service | WHERE { $_.DisplayName -eq $SQLService }).Name).Trim();
If ($SQLServiceName.contains("`$")) { $SQLServiceName = $SQLServiceName.SubString($SQLServiceName.IndexOf("`$")+1,$SQLServiceName.Length-$SQLServiceName.IndexOf("`$")-1) }
foreach ($i in (get-itemproperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server").InstalledInstances)
{
  If ( ((Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL").$i).contains($SQLServiceName) )
  { $SQLInstancePath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\"+`
  (Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL").$i}
}
# $SQLInstancePath


##################################################################################################################################################
# IPAll Section:
##################################################################################################################################################

$SQLTcpPath = "$SQLInstancePath\MSSQLServer\SuperSocketNetLib\Tcp"
Write-Host 'Entries in Path: '$SQLTcpPath " (before applying changes):"

Get-ChildItem $SQLTcpPath | ForEach-Object {Get-ItemProperty $_.pspath} `
| Format-Table -Autosize -Property @{N='IPProtocol';E={$_.PSChildName}}, Enabled, Active, TcpPort, TcpDynamicPorts, IpAddress


Set-ItemProperty -Path "$SQLTcpPath" -Name "Enabled" -Value "1"
Set-ItemProperty -Path "$SQLTcpPath" -Name "ListenOnAllIPs" -Value "1"

# TcpDynamicPorts has to be set to empty string if you want IPAll to listen remotely on static $IpAll_StaticTcpPort:
Set-ItemProperty -Path "$SQLTcpPath\IPALL" -Name "TcpDynamicPorts" -Value ""
Set-ItemProperty -Path "$SQLTcpPath\IPALL" -Name "TcpPort" -Value $IpAll_StaticTcpPort

If (Test-RegistryValue -Path "$SQLTcpPath\IPALL" -Value "IPV6Supported") {
    Write-Host "Setting the IPV6Supported to 0: "
    Set-ItemProperty -Path "$SQLTcpPath\IPALL" -Name "IPV6Supported" -Value 0
}
Else {
    Write-Host "Creating and Setting the IPV6Supported to 0: "
    New-ItemProperty -Path "$SQLTcpPath\IPALL" -Name "IPV6Supported" -Value 0 -PropertyType DWord
}

Write-Host 'Entries in Path: '$SQLTcpPath " (after applying changes, if any):"

Get-ChildItem $SQLTcpPath | ForEach-Object {Get-ItemProperty $_.pspath} `
| Format-Table -Autosize -Property @{N='IPProtocol';E={$_.PSChildName}}, Enabled, Active, TcpPort, TcpDynamicPorts, IpAddress


##################################################################################################################################################
# DAC Section:
##################################################################################################################################################

$DACPath = "$SQLInstancePath\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp"
Write-Host 'Entries in DAC Path: '$DACPath " (before applying changes):"
Get-ItemProperty -Path $DACPath

Set-ItemProperty -Path "$DACPath" -Name "Enabled" -Value "1"
Set-ItemProperty -Path "$DACPath" -Name "ListenOnAllIPs" -Value "1"

# You can set the static DAC TcpPort but SQL will completely ignore it and will read/overwrite (if needed) just the `TcpDynamicPorts` string value.
# The logic being: unlike with regular client connections you can establish only one DAC connection,
# and it's better to override DAC's port value at startup if necessary (hence Dynamic having priority) than to leave SQL without DAC whatsoever.

Set-ItemProperty -Path "$DACPath" -Name "TcpDynamicPorts" -Value $DAC_StaticTcpPort
Set-ItemProperty -Path "$DACPath" -Name "TcpPort" -Value $DAC_StaticTcpPort # SQL Ignores that setting but you can set it just in case the dynamic port gets overwritten for comparison


If (Test-RegistryValue -Path "$DACPath" -Value "IPV6Supported") {
    Write-Host "Setting the DAC IPV6Supported to 0: "
    Set-ItemProperty -Path "$DACPath" -Name "IPV6Supported" -Value 0
}
Else {
    Write-Host "Creating and Setting the DAC IPV6Supported to 0: "
    New-ItemProperty -Path "$DACPath" -Name "IPV6Supported" -Value 0 -PropertyType DWord
}

Write-Host 'Entries in DAC Path: '$DACPath " (after applying changes, if any):"
Get-ItemProperty -Path $DACPath