Is there any way to find who used Dedicated Admin Connection?
Not active connection but the previous one which is already closed?
dacsql serversql-server-2012
Is there any way to find who used Dedicated Admin Connection?
Not active connection but the previous one which is already closed?
Best Answer
SQL Server doesn't maintain this information anywhere; if you're trying to catch someone abusing the DAC, either take away sysadmin from the people who clearly shouldn't have it, or at least set up some kind of polling mechanism to catch them. You can have a table like this:
And then determine some reasonable interval to collect info about a DAC connection whenever one exists, and then run the following at that frequency (using a SQL Server Agent job, probably):
This will work going forward, but you have to be lucky (or be polling frequently) to catch them if they're in and out quickly, so you may want to fine tune that schedule. Also, in case you wonder why this isn't a
LOGON TRIGGER
, there are two reasons: (1) DAC bypasses those, by design and necessity, and (2) the rows/data in these DMVs wouldn't exist until they escaped the trigger anyway.For events in the past, and again if you're lucky, folks who try to access the DAC from SSMS will get bitten by the background connection that tries to connect again, since that failed attempt will be written to the error log, and the IP address is appended to the end of the message (but none of the other information is included). This won't help if someone used remote desktop to the server, or an app other than SSMS that doesn't try to establish additional connections using the same
ADMIN:
credentials, but should be useful if they used SSMS remotely: