Let's say I found a SQL Server and I have no idea which applications connect to it. Maybe I find one application but I am not sure if it is the only one using it.
Is there a nice way of finding all distinct connections?
You can look at the currently running processes in SSMS in Activity Monitor.
Hostname, process name or domain user names often give more information than just an IP address. Other columns can be added such as:
IP and other information can be retrieved with a JOIN to sys.dm_exec_connections on
sys.dm_exec_requests can also give useful information:
This query is just a sample. You must
Data from these view disapear when SQL Server is restarted. Therefore if the application is not regularly connected it may be a good idea to store them on a regular basis using a job or a script (Powershell).
I would also suggest what Julien suggested but I guess scheduling the query through SQL Server Agent would be better idea and then every time it runs you dump the information into a physical table. Two DMVs are joined
Like first create a physical table
Then insert records into this table:
Schedule this to run via SQL Server Agent every 15 mins or so and at end of the day you can see everything you like from the table