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/
Filed under: Blog Tagged: administration, sql server, SQLNewBlogger, syndicated 
