• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 218
  • Last Modified:

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
0
ncomper
Asked:
ncomper
1 Solution
 
Rainer JeschorCommented:
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
0
 
Aaron TomoskyTechnology ConsultantCommented:
If that fails, You can get firewall logs for 1433. As you start turning things off, you should see those connections disappear
0
 
Ryan McCauleyCommented:
SQL Profiler is for sure the way to do this - you can audit logins to the server over whatever period of time you have the client open. You have to have the "Performance Tools" components of the SQL Client installed to use Profiler, but it's included with your normal SQL license, so you can install it from the Standard edition DVD if it's not already.

Alternatively, you can issue an "EXEC SP_WHO2" in SQL Management Studio to see who is connected at the moment (both the login and the workstation). While that's just point in time, it will list open connections and it's easy to do.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Marten RuneCommented:
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
0
 
ncomperAuthor Commented:
Thanks all, ill take a look
0
 
ncomperAuthor Commented:
Thanks
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now