3

I am about to escalate this to MS, but I thought I would try here first and see if anyone has any ideas as to how to prevent this faux error message...

When connecting to SQL Server (2016 and 2017 latest builds) with a dedicated administrator connection (DAC) via a PowerShell, I get the following error message:

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 does run successfully. Numerous connections 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 a total nuisance/noise. DAC query runs and works as expected. Error fires every time even with a fresh restart on a quiesced system. There are NO other DACs - if there is it would throw a very heinous error message at the command prompt.

Interestingly, when using sqlcmd for this, it does not throw the error message.

#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()
    }
}
  • In the error, what is the state? – Sean Gallardy Apr 2 at 22:23
  • 1
    I tested the code (SQL 2017) repeatedly and couldn't reproduce. Before running the PowerShell script, if you query sys.dm_exec_connections, do you see any connections with endpoint_id = 1? – HandyD Apr 3 at 0:22
  • This repros for me on 2016 and 2017. – Josh Darnell Apr 3 at 12:37
  • @SeanGallardy for me, sometimes it's 1 and sometimes it's 2. – Josh Darnell Apr 3 at 12:38
  • If I use this script: 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' ...it shows no connections. – RelativitySQL Apr 3 at 14:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.