We help IT Professionals succeed at work.

Audit servers that connect to SQL server's databases

229 Views
Last Modified: 2014-03-28
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
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2013
Awarded 2012

Commented:
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 TomoskyDirector, SD-WAN Solutions
CERTIFIED EXPERT

Commented:
If that fails, You can get firewall logs for 1433. As you start turning things off, you should see those connections disappear
Senior Data Architect
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Marten RuneSQL Expert/Infrastructure Architect
CERTIFIED EXPERT

Commented:
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

Author

Commented:
Thanks all, ill take a look

Author

Commented:
Thanks
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.