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's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 4 down vote accepted

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

share|improve this answer

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.

share|improve this answer

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
share|improve this answer
    
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. – Julien Vavasseur Mar 2 at 9:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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