5

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.

  • It would be a major PITA, and possibly not do-able due to having to install a special driver, but you could use WireShark or Microsoft Network Monitor to track what is connecting to the server via the specified SQL port. – Wes May 12 '17 at 21:33
  • You don't mention sys.dm_exec_connections - I'm sure you've looked there too, but wanted to mention it just in case? Other places to check - default trace (if they do anything that gets logged there) or set up your own trace or extended events session. – Aaron Bertrand May 12 '17 at 21:47
  • @AaronBertrand Yep, that was it, see my posted answer. – BradC May 12 '17 at 21:49
  • Sounds like a shared memory connection which can only originate from the dB server host itself. You can check the net_transport column indm_exec_connections to see it. – eckes May 12 '17 at 22:11
5

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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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