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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
You can look at the currently running processes in SSMS in Activity Monitor.
SELECT DB_NAME(database_id) as [DB] , login_name , nt_domain , nt_user_name , status , host_name , program_name , COUNT(*) AS [Connections] FROM sys.dm_exec_sessions WHERE database_id > 0 -- OR 4 for user DBs GROUP BY database_id, login_name, status, host_name, program_name, nt_domain, nt_user_name;
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
connect_time, ports, ...
sys.dm_exec_requests can also give useful information:
This query is just a sample. You must
JOIN these 3 views together and output/store relevant information from either of them.
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
create table Session_Information ( session_id int, net_transport nvarchar(40), host_name nvarchar(128), program_name nvarchar(128), nt_user_name nvarchar(128), connect_time datetime, client_interface_name nvarchar(128), client_net_address varchar(48), local_net_address varchar(48), login_name nvarchar(128), nt_domain nvarchar(128), login_time datetime );
Then insert records into this table:
Insert into Session_Information SELECT c.session_id, c.net_transport, s.host_name, s.program_name, s.nt_user_name, c.connect_time, s.client_interface_name, c.client_net_address, c.local_net_address, s.login_name, s.nt_domain, s.login_time FROM sys.dm_exec_connections AS c JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id;
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
Enable SQL Server login auditing for successful and failed logins: https://learn.microsoft.com/en-us/sql/ssms/configure-login-auditing-sql-server-management-studio
Changing the auditing scope would require a SQL service restart. Once service is restarted, all successful logins(user) and its IP Address will start getting logged in SQL Server logs. This data can be collected for about a week or 2 and then the log file be analysed to list down all the IP Addresses (and user) that have accessed SQL Server databases during the monitored period.
If SSRS component is also installed on the same box, then ExecutionLog table of ReportServer database can be analysed to list down all the logins that access the SSRS portal.