Problems with DAC

Recently I wasn’t able to connect to one of my servers due to high CPU usage on the machine. So I tried to connect using DAC (Dedicated Admin Connection) and here is lesson I knew but forgotten (hence this post): Do not open DAC connection in object explorer or you’ll see (in SQL Server 2008 R2):

 
  ===================================

Cannot connect to ADMIN: mySqlServer

===================================

Dedicated administrator connections are not supported. (Microsoft.SqlServer.Management.SqlStudio.Explorer)

------------------------------
 Program Location:

at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.CheckDacAndSmo(SqlConnection sqlConnection)
 at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
 at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
 

Which is not helpful when you know very well that you had it working

Reason for that is that Object Explorer is opening more than one connection and therefore DAC won’t work as it is meant to be lightweight single connection tool with minimal resources use connection for troubleshooting issues when you can’t connect other way.

SQL Server 2016 is much more friendly and  will give you more information:

===================================

Cannot connect to admin: mySqlServer2016.

===================================

Dedicated administrator connections are not supported via SSMS as it establishes multiple connections by design. (Microsoft.SqlServer.Management.SqlStudio.Explorer)

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.CheckDacAndSmo(SqlConnection sqlConnection)
   at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
   at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

So solution for this issue is really simple and I am going to remember it now. Just connect using File-> New ->Database Engine Query and connect with:

ADMIN: mySQLServer

Another problem that may occur is that connection is already taken. This may happen if you (of course not) or your colleague (of course) connected to DAC and forgot to close the session afterwards.

For such situation I am have scheduled job that runs every 12hrs and kills any session using DAC endpoint

Code snippet:

If exists (
select session_id from sys.dm_exec_sessions
where endpoint_id = (select endpoint_id from sys.endpoints
where name = 'Dedicated Admin Connection'))

begin

declare @Session2 int

select @Session2 = session_id from sys.dm_exec_sessions
where endpoint_id = (select endpoint_id from sys.endpoints
where name = 'Dedicated Admin Connection')
declare @sqlkill nvarchar (24)

set @sqlkill = N'kill ' + CAST(@Session2 as nvarchar)

execute sp_executesql @sqlkill

end

 

 

 

 

Author: G L

Greg's a Senior Database Administrator in Austria with over 15 years of experience in IT (servicedesk/helpdesk, software engineer, database administrator) Likes SQL Server administration, powershell and testing new features

Leave a Reply

Your email address will not be published. Required fields are marked *