[SQL Server] SQL Server remote admin connections -- DAC (Dedicated Admin Connection)

SQL Server remote admin connections -- DAC (Dedicated Admin Connection)

If the users can't connect to the server, or there's problem with SQL Server, then you might need to connect to SQL Server explicitly for diagnostics purpose or something.

Mostly this situation can happen when there are too many connections or there are too high CPU usage queries are running on the server so that no one can connect to the server at that moment.

DAC(dedicated administrator connection) is designed for these kinds of situations.

Enable DAC

First of all you need to check the current configuration.

sp_configure 'remote admin connections'
GO

sp_configure 'remote admin connections', 1;  
GO  
RECONFIGURE;  
GO
sp_configure 'remote admin connections'
GO

Verify ports for DAC 

Make sure the default DAC port (1434) is opened.

netstat -an | findstr ":1434"

Connect to the server via DAC

You can connect via SSMS, but it's better to connect via SQLCMD which is a command line.

Because when you connect to the server via SSMS, it opens at least 2~3 new sessions and which means it's not possible to connect with DAC by default.

sqlcmd -S {Servername} -U {username} -P {password} -d {databasename} -A

Note! Make sure SQL Server Browser service is Running.

** Error -- when SQL Browser is not running.

Check the current DAC session

Check Query

SELECT dess.session_id AS DACSessionID, @@SPID AS CurrentSessionID, dess.original_login_name AS DACLogin
FROM sys.endpoints AS ep JOIN sys.dm_exec_sessions AS dess ON ep.endpoint_id = dess.endpoint_id
WHERE ep.name='Dedicated Admin Connection';
GO

References

• remote admin connections Server Configuration Option
• Diagnostic Connection for Database Administrators
• sqlcmd Utility