Using the DAC with SSMS

I’m writing this post as a way to help motivate the #SQLNewBloggers out there. Read the bottom for a few notes on structuring a post.

While troubleshooting another issue, I needed to connect to SQL Server with the DAC. I couldn’t remember the syntax, so I looked it up quickly and ran into this link: Diagnostic Connection for Database Administrators. I added the ADMIN: to my connection in SSMS for a query window and clicked Connect.

dac_g

This took entirely too long and then I got this:

dac_a

That error resulted in a rabbit trail for me to debug this, but it worked out and I’ve learned a few things. The main one is to be sure that I’ve read the documentation and errors correctly.

As I tried a few things, including SQLCMD, I realized I had issues. I searched and found that the DAC gets set to a specific port. I checked the error log to get the number.

dac_b

With that, I added it to my connection dialog, sure that this would work, but of course, it didn’t.

dac_c

Eventually I stumbled on the post about errors and realized I should have checked the SQL Browser earlier. I thought about it, but discarded that thought because I could connect in other ways. Mistake. Check networking first, and networking is where the browser comes in. Sure enough, it was stopped.

dac_d

The properties were set to DISABLED, so I had to change that before I could start it. I assume you can do that, if not, poke around the properties until you find that setting. With that changed, I started the service.

dac_e

Then I could connect. Here’s the SQLCMD version.

dac_f

With this working, the first command dialog at the top of this piece worked from an SSMS query window.

SQLNewBlogger

This was longer. What I thought was a quick lookup turned into a troubleshooting exercise that lasted about 20 minutes as I searched, read, experimented, etc. At least I made a bunch of screenshots as I was experimenting, so I had lots of data and didn’t duplicate anything.

The actual writing was only about 10 minutes, most of that looking through screenshots and trying to organize my thoughts.

References

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.

3 Responses to Using the DAC with SSMS

  1. Sandra says:

    This has to do with your instance being a named instance though, doesn’t it? I am able to get a connection via the DAC in SSMS 2008 R2 and SSMS 2012 without enabling SQL Browser if I use the Database Engine Query button to open up the Connect to Database Engine dialog and specify the DAC connection there. But, in neither version can I register the server and then double click on the name to open from the Registered Servers tab. The errors in the two versions are different with the 2012 error being the more accurate, I think: “Dedicated administrator connections are not supported via SSMS as it establishes multiple connections by design”

    Didn’t try using SQLCMD. But, I definitely don’t have SQL Browser enabled, as this was a default instance and I specifically disabled it.

  2. Pingback: Using the DAC | Voice of the DBA

Comments are closed.