#0290 – SQL Server – How to get the IP address associated with a connection and of the server using DMVs


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:


image


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.

Advertisements

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s