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.
This took entirely too long and then I got this:
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.
With that, I added it to my connection dialog, sure that this would work, but of course, it didn’t.
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.
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.
Then I could connect. Here’s the SQLCMD version.
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
- Enabling the DAC – http://www.mssqltips.com/sqlservertip/1801/enable-sql-server-2008-dedicated-administrator-connection/
- The DAC: Why, When, How – http://www.brentozar.com/archive/2011/08/dedicated-admin-connection-why-want-when-need-how-tell-whos-using/
- Errors when Trying to Use DAC Connection in SQL Server – http://www.sqldbpros.com/2013/10/error-when-trying-to-use-dac-connection-in-sql-server/
Voice of the DBA 
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.
LikeLike
Yes, the Browser comes into play with named instances. If this were a default, it should work without the Browser.
LikeLike
Pingback: Using the DAC | Voice of the DBA