2

Starting working with Dedicated Administrator Connections on SQL Server 2008 R2 servers. All is working fine (once port 1434 was opened), but am getting severity 20 errors when I disconnect the admin connection.

From within SSMS, I am opening a new query window then connecting via the DAC using connection string, "admin:". All is well. However, when I disconnect, my SQL Alert for Severity 20 errors fires, alerting me to it. The description of the alert reads:

"Could not connect because the maximum number of ‘1’ dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process. [CLIENT: XX.XX.XX.XX]" The IP address is mine.

I'm confused as what could be attempting the connection at the same time I am disconnecting from the DAC. Or, is it something else. No connections to the same server from my SSMS via any query window or Object Explorer. Other users may be connecting to this SQL Server (its a dev box) but not on DAC (only opened port 1434 before testing).

Any thoughts?

  • 1
    SMSS is probably opening up extra connections for something you're doing. It's easier to use DAC with the -A flag via sqlcmd to keep it simple. You should only be doing a few things with that connection in an emergency, so SMSS shouldn't be necessary. – LowlyDBA Jan 27 '15 at 16:03
  • I'm surprised the error is raised when you disconnect. Usually I see a dialog that reflects the same sort of thing when connecting (and it's because of the database dropdown and/or IntelliSense trying to establish a separate connection using all of the same details). But I agree with @John - you really shouldn't be using DAC for random, casual stuff you're doing in Management Studio - you should keep that connection open in case something important happens. – Aaron Bertrand Jan 27 '15 at 16:11
  • Thanks for the info, gents. Sorry I wasn't clear but I was just testing the DAC setup and SSMS seemed easy. I used 'sqlcmd' to test and there were no errors generated. – CJ Gregory Jan 27 '15 at 18:10
  • That's because sqlcmd doesn't have things like database dropdowns, IntelliSense, Object Explorer, etc. – Aaron Bertrand Jan 27 '15 at 21:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.