How to Find IP Address of All SQL Server Connection? – Interview Question of the Week #280

Question: How can I see the client IP addresses connected to SQL Server? Join sys.dm_exec_connections to sys.dm_exec_sessions. Connection details give the address; session details add the application and login context.

A client asked this during a Comprehensive Database Performance Health Check. Their mobile applications and APIs were constantly moving data. We wanted to identify the source behind a large amount of connection activity, then investigate what that application was doing.

Grouped sources and individual connections

-- SQL Server instance: current physical user connections grouped by source.
SELECT c.client_net_address, s.program_name, s.host_name, s.login_name,
       COUNT_BIG(*) AS ConnectionCount,
       COUNT(DISTINCT c.session_id) AS SessionCount,
       SUM(CONVERT(bigint, c.num_reads)) AS ConnectionReads,
       SUM(CONVERT(bigint, c.num_writes)) AS ConnectionWrites
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s ON s.session_id = c.session_id
WHERE s.is_user_process = 1
  AND c.parent_connection_id IS NULL
GROUP BY c.client_net_address, s.program_name, s.host_name, s.login_name
ORDER BY ConnectionCount DESC;

-- Keep the client's port when you need an individual connection inventory.
SELECT c.session_id, c.client_net_address, c.client_tcp_port,
       c.net_transport, c.connect_time, s.program_name,
       s.host_name, s.login_name, c.num_reads, c.num_writes
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s ON s.session_id = c.session_id
WHERE s.is_user_process = 1
  AND c.parent_connection_id IS NULL
ORDER BY c.client_net_address, c.session_id;

The first query groups by address, application, host and login. The second retains each client’s TCP port. I intentionally leave the port out of the grouped query: grouping by an ephemeral client port often splits one application’s connections into separate rows, defeating the useful source summary.

The parent-connection filter avoids counting extra MARS logical connections as additional physical connections. A connection count and a session count are labeled separately rather than assuming every DMV row is an independent session.

What these counters do, and do not, tell us

num_reads and num_writes describe reads and writes over the connection. They aren’t the query’s logical page reads or physical database-file writes, and they aren’t measurements of CPU or current execution time. They accumulate over a connection’s lifetime, so a long-lived connection can have a large total without being busy right now.

In that engagement the address was a useful lead. We traced it to a mobile application, investigated its workload and identified changes for the application team. The address alone wasn’t the diagnosis.

Connection pooling, proxies and NAT can put many users behind the same address. Non-TCP connections may not have a useful IP value. host_name and program_name are client-supplied labels, not trustworthy authentication evidence. Use request-level metrics, Query Store or a suitable trace to investigate the workload behind the lead.

For SQL Server 2022 and later, the connection DMV requires VIEW SERVER PERFORMANCE STATE; earlier SQL Server versions use VIEW SERVER STATE. This is an instance-oriented SQL Server example. Azure SQL Database has different scope and permissions, and its client TCP-port field is NULL.

• Consulting 101 – Why Do I Never Take Control of Computers Remotely?
• Consulting 102 – Why Do I Give 100% Guarantee of My Services?
• Consulting 103 – Why Do I Assure SQL Server Performance Optimization in 4 Hours?
• Consulting 104 – Why Do I Give All of the Performance-Tuning Scripts to My Customers?
• Consulting 105 – Why Don’t I Want My Customers to Return Because of the Same Problem?
• Consulting Wrap Up – What Next and How to Get Started

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.