Is there any way to find who used Dedicated Admin Connection?
Not active connection but the previous one which is already closed?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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:
CREATE TABLE dbo.DBA_DacAccess ( ConnectTime datetime, FirstObservance datetime2 NOT NULL DEFAULT SYSUTCDATETIME(), LoginName sysname, HostName sysname, AppName sysname, Interface nvarchar(32), ClientNetAddress nvarchar(48) );
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):
INSERT dbo.DBA_DacAccess ( ConnectTime, LoginName, HostName, AppName, Interface, ClientNetAddress ) SELECT c.connect_time, s.login_name, s.[host_name], s.[program_name], s.client_interface_name, c.client_net_address FROM sys.dm_exec_connections AS c INNER JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id WHERE c.endpoint_id = 1 AND NOT EXISTS ( SELECT 1 FROM dbo.DBA_DacAccess WHERE connect_time = c.connect_time );
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:
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: 192.168.0.99]