The SQL Server Dedicated Administration Connection (DAC) once again saved the day! A bad logon trigger on a development SQL Server caused all Logons to go into ROLLBACK. In effect , the Logons were authenticating but at the point of creating a user session the Logon would ROLLBACK.
1) The DAC is only allowed on the local server. Remote connections are not allowed except if configured with the sp_configure. For local server access , you’ll either need to connect locally or use RDP. If you can't RDP logon because other users logged on read How to View Remote Desktop Sessions on how to view logons and disconnect them.
2) The logon account must be part of the sysadmin group.
3) Restrictions. The main restriction is only one DAC session can be open at any time. There are other restrictions , check SQL Server Book Online for details
4) How to use . Once connected use either SQLCMD or SQL Server Management Studio.
SQLCMD option
Note: using the –A switch
sqlcmd -A –S <SQL_Server_Instance> –E
Note: using the –A switch
SSMS Option
Note: Using the ADMIN: prefix on the Server Name
