Welcome to Bucaro TecHelp!

Bucaro TecHelp
HTTPS Encryption not required because no account numbers or
personal information is ever requested or accepted by this site

About Bucaro TecHelp About BTH User Agreement User Agreement Privacy Policy Privacy Site Map Site Map Contact Bucaro TecHelp Contact RSS News Feeds News Feeds

How to Connect to an Unresponsive SQL Server: Direct Admin Connection

There may be times when SQL Server stops responding to client connections but it is still up. This can be a frustrating experience, and the first impulse is to bounce SQL Server and/or reboot the server itself. While this may stop the symptom, this action won't tell you anything to help you prevent the cause.

For example, let's say that you arrive on Tuesday morning to discover an unresponsive database. The previous night, your developers added new code to the application servers. Coincidence or cause? Without a way to see what SQL Server is doing, you'll never know. Enter the Direct Admin Connection...

Since SQL Server 2005, SQL Server has the Direct Admin Connection (DAC) to allow a back door for just this sort of scenario. On the server itself (no network connections unless the remote admins option was previously configured), a single login that is a member of the sysadmin group can connect to SQL Server via a DAC in order to get an idea of what is happening and have an opportunity to correct the issue. There are two methods to connect to SQL Server with a DAC.

DAC Method 1

The first method uses SQLCMD. On the server, open a command prompt and enter:

sqlcmd -S localhost -U sa -P password -d master -A

You may substitute the sa login (the -U parameter, entering the password after -P) with another login that is a member of the sysadmin group.The parameter -A establishes the DAC, while the -d master parameter tells SQLCMD to use the master database as the default for this session. The master database is guaranteed to be available so long as the instance is running, while other databases may not be available.

DAC Method 2

The second method uses SSMS. Again, on the server, open SSMS, then click 'File' -> 'New' -> 'Database Engine Query'. In the dialog box that appears (titled 'Connect to Database Engine'), type 'ADMIN:' in front of the name of the server instance. As an example, to login via a DAC to instance PROD\BOULDER, you would type 'ADMIN:PROD\BOULDER', then enter the remaining authentication information (remembering to use a login with the sysadmin role), and click 'Connect'.

Remember, neither of the above will succeed if the login you are using is not a member of the sysadmin group or if there is another DAC already connected to the instance.

Once connected with either method, you can run queries to determine the cause of the lock-up. I prefer method 2 above (using SSMS), as I can quickly access my standard set of queries saved on the server and/or copy and paste them from my local machine to the server via my RDP session.

Bob Balsman is a Systems & Database Administrator, with over 20 years of IT experience. His career spans many areas of technical support, including data center management, network security, system administration, disaster recovery, and database administration. Bob blogs at [http://www.singingdba.com Sorry, the website www.singingdba.com cannot be found]

RSS Feed RSS Feed

Follow Stephen Bucaro Follow @Stephen Bucaro

Fire HD
[Site User Agreement] [Privacy Policy] [Site map] [Search This Site] [Contact Form]
Copyright©2001-2024 Bucaro TecHelp 13771 N Fountain Hills Blvd Suite 114-248 Fountain Hills, AZ 85268