3 DAC errors reporting in SQL logs / Application Event Log.

[S0l4r15]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

Source Logon Message Could not connect because the maximum number of '1' dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process. [CLIENT: 127.0.0.1]

Followed by

Source Logon Message Error: 17810, Severity: 20, State: 2.

This is the error and repeated 2 more times.

Steps to Reproduce

We have a PS script that runs on the hour via Windows Task Scheduler.

The DBAtools execution is as follows:

$primaryServer = $env:COMPUTERNAME Sync-DbaAvailabilityGroup -AvailabilityGroup "AAGlistener" -Primary $primaryServer -Force -Verbose

The other parts of the PS script are checks for primary/secondary node and logging actions to text file.

Please confirm that you are running the most recent version of dbatools

Initially we had v 2.1.1 Updating to v 2.1.6 still contains the issue.

Other details or mentions

From research, I believe this is due to decryption of credentials requiring a DAC connection - however, given the errors, it suggests that either the 3 processes are trying to open a DAC before the session is free, or they're trying to run at the same time (pure guess work on my part). The requirement is to keep both primary and secondary AAG nodes in sync (e.g. users, linked servers, dbmail, credentials, etc). We use -Force to ensure the primary node is the "source of truth" and overwrites changes made to the secondary (we know it won't handle deletes of objects).

What PowerShell host was used when producing this error

Windows PowerShell (powershell.exe)

PowerShell Host Version

Name Value

---

PSVersion 5.1.17763.5202
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.17763.5202
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

SQL Server Edition and Build number

Microsoft SQL Server 2017 (RTM-CU31-GDR) (KB5029376) - 14.0.3465.1 (X64) Jul 30 2023 15:31:58 Copyright (C) 2017 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Datacenter 10.0 (Build 17763: ) (Hypervisor)

.NET Framework Version

PSChildName Version

---

Client 4.7.03190 Full 4.7.03190 Client 4.0.0.0

[S0l4r15]

I'd also like to add that the functionality of the script does as we need it to - our monitoring tools pick up on SQL Severity 20 (as it should) and thus this is "muddying" the waters for actual problems currently.

[S0l4r15]

Good day dear developers and community!

I appreciate that this issue is more of a "fog" rather than a "break" - however, has anyone had any ideas for potential fixes ? I'm more than happy to assist in troubleshooting, testing, digging around, etc.

Thanks in advance. Sol

[niphlod]

are you sure there's only one process open each time and "the older one" isn't there, lurking around with a DAC connection opened ?

[00-schaudhary]

I have the same issue.

Connect-DbaInstance -SqlInstance localhost\instanceName -DedicatedAdminConnection -verbose

This generates the error in the SQL error logs if I use the name (localhost or serverName). The issue does not happen if an IP is used (127.0.0.1 or serverIP).

I have a Windows Cluster and using Availability Groups and AG listeners.

Hope this helps is recreating the issue internally.