A login reaches SQL Server, and you need to know where the network connection came from. Finding the client IP address is straightforward for a TCP session, with important limits when proxies or local transports are involved.
Read the Client IP Address of the Current Connection
sys.dm_exec_connections exposes client_net_address for TCP connections. Filter by @@SPID to see the session running your query. Also read net_transport so a null address is not misinterpreted. Shared memory and some local connections do not provide a remote client IP because no remote TCP connection exists.
I run this first when a support ticket claims a query came from an unfamiliar host. The result is the network peer the engine sees for this connection. It does not prove which human sat behind that peer. Save the session ID and sample time if the result is part of an investigation.
SELECT session_id,
net_transport,
client_net_address,
client_tcp_port,
local_net_address,
local_tcp_port
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;Use CONNECTIONPROPERTY in a Session
CONNECTIONPROPERTY returns connection attributes for the current session. Client_net_address is useful in a stored procedure or a quick diagnostic query. It is scoped to your connection; it cannot report another session’s address. For another active session, use sys.dm_exec_connections with the appropriate permission and session ID.
The example returns the current client address and transport. Compare it with the DMV result when troubleshooting unusual drivers. If both are null for a local shared-memory connection, that is expected. The absence of a TCP address is not a SQL Server bug.
SELECT CONNECTIONPROPERTY(N'client_net_address') AS client_ip,
CONNECTIONPROPERTY(N'net_transport') AS transport,
CONNECTIONPROPERTY(N'local_tcp_port') AS server_port;Inspect the Client IP Address of Another Session
Join sessions to connections when you need the address associated with a login, host name, or program. Session metadata can help narrow the search, but host_name and program_name are supplied by the client and are not secure identity claims. The connection address is stronger network evidence, yet it still reflects the immediate peer.
I capture several attributes together instead of posting only an IP. Shared application servers can open many sessions for different users. A connection pool can reuse them. The address answers where SQL Server received the TCP connection, not which application user caused a specific business action.
SELECT s.session_id,
s.login_name,
s.host_name,
s.program_name,
c.net_transport,
c.client_net_address,
c.connect_time
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_connections AS c
ON c.session_id = s.session_id
WHERE s.is_user_process = 1
ORDER BY c.connect_time DESC;Account for Proxies and Gateways
An application server, load balancer, gateway, or proxy can sit between the user and SQL Server. The engine then sees that middle system’s IP as the client IP address. Network address translation can also change the visible address. Ask the infrastructure and application teams for the path before treating the IP as the original client. Correlate with their logs when you need user-level attribution.
I have seen every session appear to come from one address because every request passed through the same service. The SQL result was correct. The conclusion that one user did all the work was not. Draw the connection path and identify which layer records the original request. That layer’s timestamp and request ID are valuable evidence.
Understand Local Transports
A query run from SSMS on the server itself can connect with shared memory. In that case, client_net_address is null. Force a TCP test through an approved connection method if you need to validate network behavior. Do not change the server protocol configuration solely to populate a diagnostic column.
I compare local and remote tests. A remote connection helps verify firewalls, TLS, and the listener port; a local connection does not exercise those steps. If the complaint is from a remote application, use its path. A test that bypasses the failing network route can create false confidence.
Handle IPv4 and IPv6
The address can be IPv4 or IPv6 depending on the client and network path. Avoid scripts that assume four dotted numbers. Store addresses in a text type large enough for IPv6 if you capture them in a history table. Compare normalized values carefully when joining to firewall or application logs.
I include the actual address format in incident notes. A hostname lookup is a separate step and can be stale or misleading, especially behind shared services. Keep the raw IP from the connection as evidence and use DNS only as supporting context. The engine reports a connection address, not a verified machine identity.
Respect Permissions and Retention
Seeing other sessions’ connection details requires appropriate server performance permissions. Grant only the access needed for operations. Connection metadata can be sensitive when it identifies systems or users. Protect collected snapshots and define how long to keep them. Do not build a permanent address archive just because a DMV makes collection easy.
I use current connection data for active troubleshooting and targeted capture for recurring incidents. If the question concerns last week’s connection, the live DMV cannot answer it after the session ends. Configure an approved audit or telemetry source for future history. Be clear about that limit when responding to a request.
Correlate the Client IP Address With Login Events
An IP by itself does not prove successful authentication or a particular SQL statement. Pair connection data with login time, login name, application logs, and audit events for a complete timeline. Failed logins can appear in error or audit logs even when no active session exists. Plan the capture method around the question you expect to answer.
I ask whether the investigation needs network origin, account identity, or application user identity. Those are three different answers. The DMV gives one piece. Naming the required identity prevents a confident but incomplete response. A useful report says what SQL Server observed and what must be checked elsewhere.
Make the Next Investigation Easier
Record the usual application path and expected peer addresses in the runbook. When a new address appears, compare it to approved infrastructure changes and connection strings. Test from the real client environment after network changes. A baseline of expected routes helps distinguish a new deployment from an unexpected source.
Which address would SQL Server see if your application uses a gateway tomorrow? Answer that now with a controlled connection test. The result gives the team a reference point. Finding a client IP is easy; interpreting its place in the network takes the extra minute that makes it useful.
Related reading on this blog: How to Find IP Address of All SQL Server Connection? Interview Question of the Week #280 and Network Protocol and IP Address.
A client IP in SQL Server is not always a user address, it is the network peer the engine saw.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
