SQL Server – Finding Source of SPIDs with No Hostname

connectionssql serversql-server-2012

I have a busy SQL 2012 server where connections to one particular database (using the same SQL login) don't show a "hostname", and I need to find out which box (of several) these connections are coming from.

I've tried all the normal built-in and 3rd-party tools (master.sys.processes, sys.dm_exec_sessions, sp_who2, sp_WhoIsActive, the SSMS Activity Monitor, even SQL Profiler and Idera Diagnostic Manager), none of them can give me the hostname of the server/client behind these connections, presumably because they all use the same underlying system tables/views.

I'd be fine with an IP address, too, but don't see any way to determine that from within SQL either.

In case this is any help:

  • sys.dm_exec_sessions shows the client_interface_name is ODBC
  • Nothing captured for program_name either
  • The user is a SQL account that happens to be a SYSADMIN (yes, I know…)

I found a related question here (albeit from the other side), but that went unanswered.

At this point I'm tempted to disable the user so I can capture some failed logins in the SQL errorlog, but I don't imagine the application team will be happy with that.

Best Answer

Looks like I was just asking the question wrong, I found the answer under: How to get the client IP address from SQL Server 2008 itself?:

As Aaron mentioned in the comments, I had to use sys.dm_exec_connections to get the source IP. Here is the final version of the query I used:

SELECT s.session_id, s.login_name, DB_NAME(s.database_id) as database_name, 
    s.host_name, s.program_name, c.client_net_address, client_tcp_port,
    c.net_transport, c.auth_scheme, s.login_time
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_connections c 
ON s.session_id = c.session_id
WHERE s.login_name = 'MYSTERY_USER'
ORDER BY s.session_id
Related Question