Solved

Audit servers that connect to SQL server's databases

Posted on 2014-01-15
7
208 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
0
Comment
Question by:ncomper
7 Comments
 
LVL 44

Expert Comment

by:Rainer Jeschor
ID: 39784532
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
 
LVL 38

Expert Comment

by:Aaron Tomosky
ID: 39784538
If that fails, You can get firewall logs for 1433. As you start turning things off, you should see those connections disappear
0
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 500 total points
ID: 39797222
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 20

Expert Comment

by:Marten Rune
ID: 39798548
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
 
LVL 5

Author Comment

by:ncomper
ID: 39832158
Thanks all, ill take a look
0
 
LVL 5

Author Closing Comment

by:ncomper
ID: 39847969
Thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SqlDataBase 7 47
SQL Server Generate Scripts Fails 5 35
what are the unique tables in SQL master database 5 58
CPU high usage when update statistics 2 28
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question