asked on

Audit servers that connect to SQL server's databases

HI

I have an SQL 2008 R2 server to be decommissioned that has over 50 databases on it, It has been inherited as part of a take over and we don't have any documentation on it.

Is there a way we can track what servers the connections are coming from, or can you only log the user account they use to login to the DB with?

Thanks

Rainer Jeschor

Hi,
you might be able to use SQL profiler to capture all logon events and save this to a SQL table. Then run the profiler for a sufficient time frame (eg one week) and then you can simply run SQL queries against this table to get the distinct lists of servers, users ...

HTH
Rainer

Aaron Tomosky

If that fails, You can get firewall logs for 1433. As you start turning things off, you should see those connections disappear

ASKER CERTIFIED SOLUTION

Ryan McCauley

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Marten Rune

select con.session_id
, con.auth_scheme
, con.client_net_address
, ses.host_name
, ses.host_process_id
, ses.client_interface_name
, ses.original_login_name
, ses.status
from sys.dm_exec_connections con
left join sys.dm_exec_sessions ses
on con.session_id = ses.session_id
where ses.session_id <> @@spid

This shows you session information.
You can easily Schedule a job that stores this information in a table.
Schedule it to run every minute or so, and do a delete of unecessary rows nightly.
i e delete rows where hostname and original_login_name are duplicated.

Regards Marten

ncomper

ASKER

Thanks all, ill take a look

ncomper

ASKER

Thanks