Blog

There’s a DAC in SQL Server that you should know and love. Sadly, you probably either haven’t met or you’ve forgotten to turn it on.

DAC? What’s That?

First, a little disambiguation. The acronym ‘DAC’ is too popular.

For the context of this article, our DAC is the ‘Dedicated Admin Connection’.

SQL Server implements a totally unrelated DAC as well: a data tier application or DAC package. We’re not talking about that here.

What Can DAC Do For You?

Have a SQL Server that’s in crisis? The DAC can help you stage an intervention.

The Dedicated Admin Connection was built to help you connect and run basic troubleshooting queries in cases of serious performance problems. This is your opening to grab a ‘Get Out of Jail’ card, but since you don’t use this on a regular basis it’s easy to forget how to use it. It’s also easy to forget to enable access to the DAC remotely.

How the Dedicated Admin Connection Works

The DAC uses a special reserved scheduler which has one thread for processing requests. This essentially means that SQL Server is keeping a backdoor open to processor resources available just for you.

Don’t be too tempted to abuse this privilege. That one thread is just one thread— there’s no parallelism for queries running on the DAC. Two percent of you will be tempted to use this for your maintenance jobs on busy systems. Seriously, just don’t go there. The DAC was not designed for high performance.

How to Enable the DAC for Remote Connections and Clusters

By default the DAC is only enabled for accounts logged onto the local machine. For production servers, that means it only works for remote desktop sessions to non-clustered SQL Server instances. If your instance is clustered or if you’re connecting over TCP/IP, you’re out of luck unless you change one setting. That setting is ‘Remote Admin Connections’.

Should you enable remote connections? I agree with Books Online, which points out:

If SQL Server is unresponsive and the DAC listener is not enabled, you might have to restart SQL Server to connect with the DAC.

For clusters, sign me up! I’m in favor of enabling it for other instances as well. In times of trouble, you want to minimize the amount of time you spend using remote desktop on a server having problems. You want to use that only to gather information you can’t get another way.

Enabling the DAC for remote connections is easy as pie. It is controlled by the ‘Remote Admin Connections’ setting. To enable it, you simply run this bit of code:

EXEC sp_configure 'remote admin connections', 1
GO
RECONFIGURE
GO

There’s always a catch. You many need to get firewall ports opened as well, depending on your environment and where you intend to connect from. This will probably be port 1434, but that will vary depending on your configuration. (Books Online has got your back: read more in the “DAC Port” section here.)

Only One Sysadmin Can Ride This Horse At a Time

This isn’t a party line, only one sysadmin at a time can use the DAC. Also, you should only run simple, quick queries using the DAC.

In other words, only connect to the DAC when you really need to. When you’re done wipe the seat make sure to disconnect.

How to Connect to the DAC

You can connect to the DAC using the command line. Use the “-A” option with SQLCMD.exe.

I find it more convenient to connect in Management Studio itself. You do this by prefixing the instance name you are connecting to with “Admin:”.

One FYI: Object Explorer can’t connect to the DAC. If you open SSMS and have Object Explorer connecting by default, the first connection prompt you see with be for that. If you try to tell that to connect to the DAC, it’ll fail. That’s a good thing, we wouldn’t want the power to go to Object Explorer’s head.

Who’s Been Sleeping in My DAC? How to Tell Who’s using the Dedicated Admin Connection.

If you try to connect to the DAC when someone is already connected, you’ll see a connection error. It probably won’t tell you straight out that someone’s connected to the DAC already, but if you check the SQL Server Error log you should see the message:

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.

So if you can’t get the DAC, how can you tell who’s using it?

Here’s a hint— the DAC uses a Dedicated TCP Endpoint. It’s even endpoint #1. The DAC is ranked #1 by endpoints everywhere!

This makes finding someone using the DAC nice and simple, because endpoint_id is listed in sys.dm_exec_sessions. So you’ll just need a little something like this:

select
	case
		when ses.session_id= @@SPID
		then 'It''s me! '
		else '' end
		+ coalesce(ses.login_name,'???') as WhosGotTheDAC,
	ses.session_id,
	ses.login_time,
	ses.status,
	ses.original_login_name
from sys.endpoints as en
join sys.dm_exec_sessions ses on
	en.endpoint_id=ses.endpoint_id
where en.name='Dedicated Admin Connection'

Your Mission: Get Back with your DAC Today

Take a few minutes today to connect with the DAC in a test environment. Check whether you have remote admin connections enabled in your environments, and talk about what the impact of that might be.

The DAC can save your bacon— make sure you have the ability, and use the power wisely.

↑ Back to top
  1. This is really neat. I love learning about these little hidden devices in SQL Server that are easy to miss. I’ll definitely be considering which scenarios to enable the remote DAC in from now on.

  2. Cool. Wondering if there’s some sort of hack you could do to enable parallelism. That two percent of me is more like 15 percent.

  3. We have clustered instances of SQL2008 SP2.

    I’ve tried enabling remote access to the DAC, and I see MSSQL has started it and picked a port:

    -Server is listening on [ 1.2.3.4 12345].
    -Dedicated admin connection support was established for listening remotely on port 49552.

    But all I get is timeouts:

    C:\Users\brian.spolarich>sqlcmd -S1.2.3.4,12345 -E -A
    HResult 0×57, Level 16, State 1
    SQL Server Network Interfaces: Connection string is not valid [87].
    Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or in
    stance-specific error has occurred while establishing a connection to SQL Server
    . Server is not found or not accessible. Check if instance name is correct and i
    f SQL Server is configured to allow remote connections. For more information see
    SQL Server Books Online..
    Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

    So my instance names are in format FOO\FOO and I listen on a nonstandard port, so I verified I can connect normally. This works:

    sqlcmd -S FOO\FOO -E
    sqlcmd -S 1.2.3.4\FOO,12345 -E

    But I can’t connect to the DAC, either using 127.0.0.1 or 1.2.3.4.

    What’s the deal? I’ve even restarted the database engine in my test environment but no goodness. It does pick the same DAC port.

    • Hi Brian,

      You’re very close! And Books Online is cryptic on this, so I’m glad you asked. Try this:

      From your command prompt, run this. *Do* use the port for the DAC, but *don’t* use the -A:
      sqlcmd -S 1.2.3.4,12345 -E

      It should connect up as long as you’re not connected from elsewhere. Then run this so it’ll show you your spid:
      select @@spid
      go

      Then connect normally from management studio. To prove your command prompt has the DAC, run the query I have in the blog post that shows you who’s using the DAC. You should see the spid you got from your command line.

      Basically, when you specify the DAC port explicitly on connection, you don’t need to use -A.

      You can use -A when you specify the instance name and *not* the DAC port, like this:
      sqlcmd -S 1.2.3.4\FOO -E -A

      Similarly, inside SSMS, you could use either of these to connect to the DAC for instance FOO if the DAC is listening on port 12345:
      Admin: 1.2.3.4\FOO
      1.2.3.4,12345

      Hope this helps,
      Kendra

      • I am trying to establish a remote DAC connection via SSMS to a default instance where DAC is using a non-standard, static port (i.e., something other than 1434). Also, the SQL Browser service is not enabled. When connecting to the database engine, I am keying in the following:

        ADMIN:myserver 14345

        I’ve also tried using the IP address like the following:

        ADMIN:12.123.234.12, 14345

        But I’m not able to make the connection. I have validated that DAC is listening on port 14345. Suggestions?

        • Hi Brenda,

          This is a great question.

          When you use the IP address and port for the DAC to connect, you don’t use the ADMIN: prefix. Think of it this way– the ADMIN: prefix is what’s helping the SQL Browser service know what port to connect you to. If the browser service isn’t running, then you’re on the right track with specifying the port. It’s just that with the ADMIN: prefix it’s trying to use the browser and failing.

          In my test VM, i was just able to connect to the DAC successfully from sql server management studio by using simply this:

          10.211.55.9,49172

          Where that’s my VM’s IP address and the port the DAC is listening on.

          One note– my test VM is running SQL Server 2012, so I got a pop-up message that it wasn’t able to connect to the DAC. This is a bug that’s specific to the pre-release SQL 2012′s, and I believe the error was for intellisense trying to connect up, not my session itself. I can verify that I did make the connection by looking at the toolbar and verifying I’m connected to that port, and also using the query in this post to verify my spid is connected to the DAC. So if you’re testing this on 2012, that may be related.

          Hope this helps!
          Kendra

          • Kendra,
            Thank you! You’re answer and explanation were perfect. I am using SQL Server 2008 R2. I was able to make the remote DAC connection once I removed the ADMIN: prefix and just used the servername/IP followed by a comma and the port as you described above. Simple:-) and yet I could not find this documented anywhere. Thank you again.

  4. Hmm…I’m getting somewhere, but its not working yet for me.

    C:\Users\brian.spolarich>sqlcmd -S 10.5.3.1\SQL0301,49552 -E
    HResult 0×102, Level 16, State 1
    TCP Provider: Timeout error [258].
    Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Unable to complete login process due to delay in prelogin response.
    Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

    C:\Users\brian.spolarich>sqlcmd -S 10.5.3.1\SQL0301 -E -A
    HResult 0×102, Level 16, State 1
    TCP Provider: Timeout error [258].
    Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Unable to complete login process due to delay in prelogin response.
    Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

    However I see this *sometimes* in the ERROR.LOG during my testing:

    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: 10.5.3.21]

    I ran your query and I did see myself connected to the DAC, and once during my testing I got an actual prompt:

    C:\Users\brian.spolarich>sqlcmd -S 10.5.3.1\SQL0301,49552 -E
    1> select @@SPID
    2> GO
    Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : The connection is no longer usable because the server failed to respond to a command cancellation for a previously executed statement in a timely manner. Possible causes include application deadlocks or the server being overloaded. Open a new connection and re-try the operation..

    I tried again immediately and I got the ‘prelogin failure’ error, and the ERROR.LOG shows that there’s an existing DAC connection that’s opened by me, but I never got to use it.

    What’s the deal? :-)

  5. Actually I figured it out. On these dev servers I had my domain account as a security principal on the SQL server, but it was only a member of the public role. I was also a member of another group that had sysadmin rights. Once I granted my domain account sysadmin role membership the DAC worked fine. Strange that it behaved this way – I haven’t had any access issues on those servers otherwise and have sysadmin rights when logged in normally.

  6. By the way, the DAC opens quite a few doors: aside from being useful in emergencies by providing a dedicated scheduler, it also provides access to certain metadata collections, which are only available through the DAC.
    Furthermore, there is a way to decrypt encrypted stored procedures through the DAC, if you have encrypted them and lost the source code.

  7. Pingback: Something for the Weekend – SQL Server Links 02/09/11

  8. I saw a blog that said to use the DAC from SSMS, you need to use the “Connect to Database Engine” button with “Connect to Database Engine” (to the right of the “New Query” button), not the “Connect to Server” with “Connect to Server” as the titlebar (oddly enough, under Object Explorer / Connect / Database Engine”.

    This worked, but what is the difference between “Connect to Server” and “Connect to Database Engine”?

  9. I saw a blog that said to use the DAC from SSMS, you need to use the “Connect to Database Engine” button with “Connect to Database Engine” as the titlebar (to the right of the “New Query” button), not the “Connect to Server” with “Connect to Server” as the titlebar (oddly enough, under Object Explorer / Connect / Database Engine”).

    This worked, but what is the difference between “Connect to Server” and “Connect to Database Engine”?

    • Ah, I see– when you try to connect from Object Explorer, it throws an error. On SSMS 2012 it gives me a nice detailed message, but which is a little funny. It says: “Dedicated administrator connections are not supported via SSMS as it establishes multiple connections by design.”

      However, it absolutely does work to connect via SSMS from a new query when it is NOT initiated from the object explorer bar, and instead you connect from the top menu.

      I’d never tried it from Object Explorer before, so I wasn’t aware of this quirk. Thanks for pointing this out!

  10. Pingback: Using named instances? Test your DAC connection

  11. Pingback: Dedicated administrator connections not supported. (ObjectExplorer) « Ramblings of a SQL DBA mind…

  12. Best part of this post is the query to determine if you have the DAC connection: “It’s me!” ;)

    I actually included your script in a post I threw together on an error I ran into trying to connect with DAC:

    http://www.sqldbpros.com/2013/10/error-when-trying-to-use-dac-connection-in-sql-server/

    Thanks!

  13. How do you disconnect the DAC?

    • Shelz,
      If you are using sqlcmd then just type in ‘Exit’ or just disconnect your connection if using SSMS.
      Thats it!!!

  14. Pingback: Configurar conexão DAC – Dedicated Administrator Connection

  15. I had not used DAC for a while, and today of all days it came in handy. I had enabled created a log in trigger and unfortunately I forgot to put a check in for the DBAdmin repository where the LoginEvents table was going to reside therefore the table didn’t get created because DBAdmin didn’t exist and the Trigger was created anyway, without any table to write to. Thus causing log in errors for one of our boxes, and luckily I remembered DAC and it helped out along w/ this article .

    sqlcmd -S xxxx\instance -A
    1> DISABLE TRIGGER [login_audit_trigger] ON ALL SERVER
    GO

    Thanks!

  16. Pingback: Escaping from a runaway Logon trigger | SQL Studies

  17. Pingback: Escaping from a runaway Logon trigger - SQL Server - SQL Server - Toad World

  18. Hi Kendra,

    I have tested the method of query connection to the DAC and this worked first time thank you this will be most useful.

    Rgds,
    Stu

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php