"the maximum number of '1' dedicated administrator connections already exists" message in error log when connecting to DAC from Powershell
5
votes
2
answers
4466
views
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';
Asked by RelativitySQL
(217 rep)
Apr 2, 2019, 06:56 PM
Last activity: Oct 18, 2023, 05:56 PM
Last activity: Oct 18, 2023, 05:56 PM