Skip to main content
improved title, grammar, and removed wordiness.
Source Link
Hannah Vernon
  • 70.5k
  • 22
  • 177
  • 321

When using DAC, receiving the "[…] the "the maximum number of '1' dedicated administrator connections already exists" message in error log when connecting to DAC from Powershell

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.

When using DAC, receiving the "[…] the maximum number of '1' dedicated administrator connections already exists" message in error log

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.

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.

"the maximum number of '1' dedicated administrator connections already exists" message in error log when connecting to DAC from Powershell

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.

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';
added details from comments
Source Link
Hannah Vernon
  • 70.5k
  • 22
  • 177
  • 321

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.

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.

Commonmark migration
Source Link

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]

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]

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]

Tweeted twitter.com/StackDBAs/status/1113501661803286528
added 55 characters in body; added 78 characters in body
Source Link
Loading
Generic DBMS tag for visibility; formatting changes and content reorganization for readability; etc.
Source Link
MDCCL
  • 8.5k
  • 3
  • 31
  • 62
Loading
Source Link
Loading