DAC is probably the most useful tool for troubleshooting SQL Server in few scenarios. Basically, It can be used as a back door entry in case the server goes unresponsive and doesn’t accept any new connections. So, how to make a DAC to SQL Server Instance? Preferred way is to use SQLCMD by issuing -A switch. But few folks might prefer SSMS over SQLCMD. In this blog post let’s see how to make a dedicated admin connection from SSMS.
Well, all you need to do is prefexing “admin:”to your instance name. I’ve a default SQL Instance(SQLTREK2), so in order to make a dedicated admin connection, all I’ve to do is use “admin:SQLTREK2” as my connection string. Okay, Let’s see what my SSMS does when I do that.
Dedicated administrator connections are not supported via SSMS as it establishes multiple connections by design. (Microsoft.SqlServer.Management.SqlStudio.Explorer)
If you wonder what multiple connections its talking about, It is referring to Object Explorer(Yes, OE establishes its own connection). But there can be only one DAC session at any given time. So, how to get around this issue in SSMS?
Method 1:
Creating DAC session by initiating connection from query pane. In the below screenshot, you can see I have a query pane which is currently disconnected.
Right click in the query pane and go to connection and click connect(providing admin:Instance name)
Well, SSMS created a pop-up which says it failed to connect, But I would like you folks to focus on the status bar where it did established a connection using admin:SQLTREK2. So basically this window is your DAC session.
Method 2:
By using ‘Database Engine Query’ connection. By using this method, you can avoid the error message which SSMS returned(shown in the above screenshot). All you have to do is click on Database Engine Query button right beside ‘New Query’ and make a connection. See below screen shots.
Voila! That was easy right….
Bonus points to you if you have noticed difference between ‘connect to server’ and ‘connect to database engine’. See below if you haven’t.
From Object Explorer: (DAC fails)
DB Engine query: (DAC Succeeds)
Hope this helps! Happy holidays…!
Leave a comment