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...
WhenI'm connecting to SQL Server (2016 and 2017 latest builds) with a dedicated administrator connectionvia the Dedicated Administrator Connection (DAC) via afrom PowerShell, I get the.
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 does runruns successfully. Numerous connections Numerous connection string management iterations have been attempted,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 logError Log, so no joy there.
Examining sys.dm_exec_sessions
reveals nothing of interest,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/noisejust noise. The DAC query runs and works as expected. Error fires
The error is recorded every time even with a fresh restart on a quiesced system. There are NO other DACsDAC connections in use - if there is itwas, Powershell would throw a very heinousan obvious error message at the command prompt.
Interestingly, when using sqlcmd for thissqlcmd
, it does not throw theno error message is recorded in the SQL Server Error Log.
IfNo connections are shown when I userun this scriptquery 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'Connection';
...it shows no connections.