Friday, May 21, 2010

Dedicated Administrator Connection - SQL 2005

When you try to connect to a SQL server instance using the Dedicated Administrator Connection(DAC), from SSMS - the following error will occur : "Dedicated administrator connections are not supported(Object explorer).

The thing is, dedicated administrator connection only works when there are no concurrent active connections. So, you gotta connect by clicking on "New Query" button - and then connect with

ADMIN:SERVERNAME


From SQLCMD:

sqlcmd -E -S servername -A


When a server level trigger prevents new connections to SQL server, then you might want to connect using the DAC and troubleshoot.

By default, only local dedicated admin connections are enabled - if you want to enable remote DAC, use Surface Area Configuration too.

exec sp_configure 'remote admin connections', 1
reconfigure