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()
}
}