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:
|
1 2 3 4 |
EXEC sp_configure 'remote admin connections', 1; GO RECONFIGURE GO |
There’s always a catch. You may 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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.
42 Comments. Leave new
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.
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.
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 0x57, 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.
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 0x102, 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 0x102, 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? 🙂
me with same problem.. plz reply soon.
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.
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.
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”?
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!
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!
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!!!
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!
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
I would like to know, what if you start SQL with the -f option, in single user mode.
And then some application steals the first connection. Is it possible to connect via the DAC then?
We had a situation where the memory was accidentally changed on a production server to 128mb. SQL would not start up normally. After adding the startup parameter -f it started up with minimal resources but also in single user mode. this doesnt help when there are other connections grabbing the first connecting. After about 5 attempts we eventually manageed to get in by restarting SQL and immediately opening up management studio, changed the max memory setting to 64GB and all is well. but this is not a very reliable way of connecting. So is there anyway to connect with the DAC when SQL is in single user mode(-f) ??
You can always disable other logins if you need to.
Kendra – Reading through your post just made my day. And I’ll remember to “wipe the seat”.!!
How can I run the ‘Who’s Got the DAC’ query when I can’t connect?
Sometimes you can connect and it’s just really slow, but you can still run that query 🙂
My organization has come back and wants to disable DAC or more like not allow the use of DAC so what does MS recommend? What do i tell my company to convince them that DAC is useful as the security officer came back and said “Enabling remote administration increases exposure of the system and heightens the risk of unauthorized access to admin functionality”.
What do i tell them?
Kal – tell them that the standard port of SQL Server also allows remote administration. Do they want to disable that too?
They also want to change the default port to another port ?
Kal – ask them if that will remove the ability of hackers to do remote administration. If the answer is no, then why are they worried about the DAC?
Using a script in seconds, hacker can figure out which ports are open; with a script, s/he can figure out whether getting sql server response on the port by sending in the right protocol communication message text. Changing port number only locks out good, non-technical, innocent people, not hackers. Your network protection, authentication protection, encryption protection is far more critical.
From our discovery when Remote Admin Connections is disabled it doesn’t mean that DAC is disabled, it simply blocks remote connections for DAC. You can still use DAC locally on the SQL Server. I can see enabling this would be useful for physical machines, but most environments are Virtualized and logging in locally to a server is much easier even if you can’t RDP into the SQL server.
As for security I can see both points. I think the intention is to limit the exposure of your sql server environment if you were compromised. Example: If you were a hacker and you successfully acquired internal domain access to a SQL server. DAC would probably be the best tool to use to not be detected to query databases and the quickest way to steal information. You can then broadcast to other sql servers as what Jason pointed out on 10/12/2017 without having to login to them. If Remote admin was disabled you would merely just slow down the hackers ability to access data. If you had over 100 database servers and Remote Admin Connection was disabled it would take much longer to pull out data than if DAC was allowed for remote execution. Minutes vs Hours? Hours vs Days? shrug…
Kal’s security officer is probably referencing Center for Internet Security recommendations which lists this item as High. I’m not a hacker, but if CIS which deals with hackers all the time says to disable it, I’m going to disable it. For the mere fact that I like having a job and I don’t claim to be a data breach expert.
J – yeah, what you really want to do when you have a server in an emergency is RDP in and open SSMS. Maybe watch a movie.
I apologize Erik, I didn’t mean to offend you, or this article. I think I agree with you that being able to use DAC remotely from another server provides options to quickly resolve sql server issues. I was merely pointing out that you could still utilize DAC on the server having an issue with remote admin connections being disabled. Also for people like Kal who may work for a company where security is audited by compliance officers who need to ask these questions. Those audits are conducted, because there are financial ramifications on whether we’re meeting “certain” requirements. We all know the value of DAC and this article absolutely true.
most of the time, when you *need* to use the DAC, you cannot actually login to the physical console or through RDP since the CPU is pinned to the roof. Remote access to the DAC is often the *only* way to connect to a SQL Server that is otherwise unreachable, and offers the only practical way to understand what caused the problem.
[…] If you think that this is a bit of a security risk, there is a way to check exactly who has been using the DAC account. Kendra Little covers it here. […]
Sorry for the late reply so yes my CISO wants me to disable DAC and change the default port numbers but i need to provide a good justification why i should keep DAC enabled but changing the port number isnt an issue but i agree with you guys that if a hacker wants to find the port numbers then he or she will find them one way or another but we follow the security hardening practices and provide our recommendations to the CISO.
My justification to the CISO would be something like this:
“DAC is recommended to be enabled incase the SQL Server renders unaccessible locally and requires investigation”
That is one possible justification but what else can i include?
Have a lovely week everyone
Kal
[…] mode, incoming connections are locked down and the service remains in the Starting status. Only the Dedicated Administrator Connection can be used to connect to the instance […]
[…] as far as resources go, the only way to log into the instance to do something about it is using DAC; even if you are on the same machine, if you’re trying to log in, as usual, it won’t make any […]
Is it possible to receive false positive error messages as follows:-
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.
The above error is occurring from time to time but nobody should be using DAC to connect…
I have seen the following solution being reported:-
After working up through several layers of Microsoft support, it was discovered that the solution to this issue was to change ” pooling=false” to “Pooling=False” It is case sensitive.
Has any one else run into this recurrent error message?
Your best bet for general questions is a Q&A site like https://dba.stackexchange.com.
You mentioned “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:”.
However, trying to connect vial SSMS gives an error: “Dedicated administrator connections are not supported via SSMS as it establishes multiple connections by design “
Sorry,
found the answer for that after posting the question.
Oleg – that’s because IntelliSense is trying to connect behind the scenes. You actually did connect. Cheers.