"the maximum number of '1' dedicated administrator connections already exists" message in error log when connecting to DAC from Powershell

Question

I'm connecting to SQL Server (2016 and 2017 latest builds) via the Dedicated Administrator Connection (DAC) from PowerShell.

The following error message is recorded in the SQL Server Error Log:

Date 4/2/2019 1:59:13 PM Log SQL Server (Current - 4/2/2019 1:59:00 PM)

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]

The query runs successfully. Numerous connection string management iterations have been attempted; this one is the most robust so far.

A solution exists on Stack Exchange that involves killing the spid before closing the connection, but that also throws a nuisance message into the SQL Server Error Log, so no joy there.

Examining sys.dm_exec_sessions reveals nothing of interest; no connections remain open using this technique. The below PowerShell has a dummy query in it, I can't talk about why we are connecting this way because it is proprietary, but it is 100% necessary, it is a very quick connection, and I need to do it once every 10 minutes.

This error is just noise. The DAC query runs and works as expected.

The error is recorded every time even with a fresh restart on a quiesced system. There are NO other DAC connections in use - if there was, Powershell would throw an obvious error message at the command prompt.

Interestingly, when using sqlcmd, no error message is recorded in the SQL Server Error Log.

#begin powershell script
$SqlServerName = "server\instance"

$DbQuery = "
INSERT INTO  master.dbo.sometable(value1,value2) values ('test14','testtest14');"

function Get-SqlConnection
{
    param (
        [String] $SqlServerName
    )
    $sqlConnection = $null

    try
    {
        $sqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $sqlConnection.ConnectionString = "data source=admin:$SqlServerName;Integrated Security=True; pooling=false"
        $sqlConnection.Open()
    }
    catch
    {
        if ($sqlConnection)
        {
            [void] $sqlConnection.Dispose()
        }
        throw
    }
    $sqlConnection
}

try
{
    $sqlCommand = New-Object System.Data.SqlClient.SqlCommand
    $sqlConnection = Get-SqlConnection -SqlServerName $SqlServerName
    $sqlCommand.Connection = $SqlConnection
    $sqlCommand.CommandText = $dbQuery
    [void] $sqlCommand.ExecuteNonQuery()
}
finally
{
    if ($sqlCommand)
    {
        [void] $sqlCommand.Dispose()
    }

    if ($sqlConnection)
    {
        [void] $sqlConnection.Dispose()
    }
}

No connections are shown when I run this query on the server:

SELECT
    CASE
        WHEN ses.session_id= @@SPID THEN 'It''s me! '
        ELSE '' 
    END 
    + coalesce(ses.login_name,'???') as WhosGotTheDAC,
    ses.session_id,
    ses.login_time,
    ses.status,
    ses.original_login_name
from sys.endpoints as en
join sys.dm_exec_sessions ses on
    en.endpoint_id=ses.endpoint_id
where en.name='Dedicated Admin Connection';

Answer 1

After working up through several layers of Microsoft support, it was discovered that the solution to this issue was to change pooling=false to Pooling=False - apparently keywords and values are case sensitive.

Answer 2

Adding a System.Data.SqlClient.SqlConnectionStringBuilder object to your script, and using it to generate the connection string, would avoid this issue from the outset.

PS C:\> $SqlConnectionStringBuilder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder('data source=admin:my-server\instance;pooling=false');
PS C:\> $SqlConnectionStringBuilder.ConnectionString;
Data Source=admin:my-server\instance;Pooling=False

Notice I have all the keywords in lower-case; the SqlConnectionStringBuilder object automatically translates them to the correct case, and does a syntax check as well.