SQL Sever - Dedicated Administrator Connection - DAC

16 August,2012 by Tom Collins

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) Sysadmin group - The logon account must be part of the sysadmin group. Read Find current user and sysadmin to identify sysadmin

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

Dac

 

Read More

DAC and Server is in single user mode – Error 18461

Monitoring a Rollback and sys.dm_exec_requests

SQL Server – default trace FAQ

 


Author: Tom Collins (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Sever - Dedicated Administrator Connection - DAC


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer