5

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';
Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323
RelativitySQL
  • 217
  • 2
  • 7

2 Answers2

2

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.

Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323
RelativitySQL
  • 217
  • 2
  • 7
0

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.

Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323