12

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?

4 Answers 4

17

You can look at the currently running processes in SSMS in Activity Monitor.

You can also get it using sys.dm_exec_sessions as well as sys.dm_exec_connections and sys.dm_exec_requests.

Query:

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: login_time, last_successful_logon

IP and other information can be retrieved with a JOIN to sys.dm_exec_connections on session_id: client_net_address, local_net_address, connect_time, ports, ...

sys.dm_exec_requests can also give useful information: command type, sql_handle, ...

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).

5

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 sys.dm_exec_connections and sys.dm_exec_sessions

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 session_information.

0
2

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.

-2

From Get Client IP Address in SQL Server by Sarvesh Kumar Gupta:

CREATE FUNCTION [dbo].[GetCurrentIP] ()
RETURNS varchar(255)
AS
BEGIN
    DECLARE @IP_Address varchar(255);

   SELECT @IP_Address = client_net_address
    FROM sys.dm_exec_connections
    WHERE Session_id = @@SPID;

   Return @IP_Address;
END
1
  • This will return the client_net_address of the connexion running it. This may be useful in some case but is not what is needed here, especially since the unknown application won't call this function. Mar 2, 2016 at 9:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.