Helping people on the various forums gives me a great sense of satisfaction. Recently, I was looking going through the unanswered questions section in the Ask module of this site, when I stumbled across a very interesting question:

How to get the IP address associated to the SQL Server without the use of xp_cmdshell?

Now, the solution that I am going to present today is a way of getting the client connection and SQL server IP address using DMVs. I myself have never used this in production, because all of our servers are on static IPs, so we knew and planned the IPs even before the systems were ever built. Hence, if you notice any obvious flaws in this approach, please feel free to discuss them in the comments section below.

You can customize the script provided below as per your need. Basically, you need to connect to your instance of SQL Server with a user having VIEW SERVER STATE rights (required by the DMV) and simply run the script.

USE tempdb;

GO

SELECT sec.client_net_address,

       sec.client_tcp_port,

       sec.local_net_address,

       sec.local_tcp_port,

       st.text,

       ses.session_id,

       ses.login_time,

       ses.original_login_name,

       ses.nt_domain,

       ses.nt_user_name,

       ses.host_name,

       ses.program_name,

       ses.host_process_id,

       ses.client_version,

       ses.client_interface_name,

       ses.ansi_defaults,

       ses.ansi_null_dflt_on,

       ses.ansi_nulls,

       ses.ansi_padding,

       ses.ansi_warnings,

       ses.arithabort,

       ses.open_transaction_count,

       ses.concat_null_yields_null,

       ses.transaction_isolation_level,

       ses.lock_timeout

FROM sys.dm_exec_sessions AS ses

INNER JOIN sys.dm_exec_connections AS sec ON ses.session_id = sec.session_id

INNER JOIN sys.dm_exec_requests AS ser ON sec.connection_id = ser.connection_id

CROSS APPLY sys.dm_exec_sql_text(ser.plan_handle) AS st

WHERE ses.is_user_process = 1;

GO

The output looks similar to the one shown in the screen-shot below:

The columns of interest are:

• client_net_address = IP address of the client who established the connection
  
• client_tcp_post = TCP Port number over which the client established the connection
  
• local_net_address = Represents the IP address on the server that this connection targeted
  
• local_tcp_port = Represents the Server TCP port that the connection targetted

Please note: The IP addresses and port information will only be available in case of TCP/IP connections. Shared Memory and Named Pipes do not use IP addresses to establish the connection.

Further Reading

• sys.dm_exec_connections [Link]

Until we meet next time,

Be courteous. Drive responsibly.