How to get the IP address, computer name and other information of the front-end connection in SQL Server

In some requirements, it may be necessary to know some system information about the front-end programs connected to SQL Server, such as the computer name of the front-end connection, the IP address, when to start requesting the connection, and when to end the connection.

If you are not familiar with SQL Server's system functions or views, this functionality may seem more complex, but in fact, the dynamic management view of SQL Server has given us this information, let's take a look at two dynamic management views.

1, sys.dm_exec_sessions
This view provides some information about all the clients that connect to SQL Server, following the columns returned by sys.dm_exec_sessions:

Column Name	Data Type	Description
session_id	smallint	Identifies the session associated with each active primary connection.
Login_time	Datetime	The time the session was established.
Host_name	nvarchar (128)	The host associated with the session.
Program_name	nvarchar (128)	The program associated with the session.
host_process_id	Int	The process ID associated with the session.
Client_version	Int	The version of the interface used by the client to connect to the server.
Client_interface_name	nvarchar (32)	The name of the interface used by the client to connect to the server.
security_id	varbinary (85)	The Microsoft Windows security ID that is associated with the login name.
Login_name	nvarchar (128)	The SQL logon name associated with the session.
Nt_domain	nvarchar (128)	The domain from which the session connection was established.
Nt_user_name	nvarchar (128)	The user name associated with the session.
Status	nvarchar (30)	The state of the session. Possible values: 1, run-one or more requests are currently running 2, sleep-no requests are currently running 3, Hibernate-session is in pre-logon state
Context_info	varbinary (128)	The context_info value of the session.
Cpu_time	Int	The CPU time (in milliseconds) that the session occupies.
Memory_usage	Int	The number of 8 KB memory pages that the session occupies.
Total_scheduled_time	Int	The total amount of time (in milliseconds) that is spent on the execution of the scheduled session that contains the request.
Total_elapsed_time	Int	Elapsed time (in milliseconds) since the session was established.
endpoint_id	Int	ID of the endpoint associated with the session.
Last_request_start_time	Datetime	The start time of the most recent session request. This includes the request that is currently executing.
Last_request_end_time	Datetime	The time when the last session request was completed.
Reads	bigint	The number of reads performed by the request in the session during the session.
Writes	bigint	The number of writes performed by the request in the session during the session.
Logical_reads	bigint	The number of logical reads that have been performed on the session.
Is_user_process	Bit	0 if the session is a system session. Otherwise, it is 1.
Text_size	Int	The TEXTSIZE setting for the session.
Language	nvarchar (128)	The LANGUAGE setting for the session.
Date_format	nvarchar (3)	The DateFormat setting for the session.
Date_first	smallint	The Datefirst setting for the session.
Quoted_identifier	Bit	The QUOTED_IDENTIFIER setting for the session.
Arithabort	Bit	The ARITHABORT setting for the session.
ansi_null_dflt_on	Bit	The ANSI_NULL_DFLT_ON setting for the session.
Ansi_defaults	Bit	The ANSI_DEFAULTS setting for the session.
Ansi_warnings	Bit	The ANSI_WARNINGS setting for the session.
Ansi_padding	Bit	The ANSI_PADDING setting for the session.
Ansi_nulls	Bit	The ANSI_NULLS setting for the session.
Concat_null_yields_null	Bit	The CONCAT_NULL_YIELDS_NULL setting for the session.
Transaction_isolation_level	smallint	The transaction isolation level of the session. 0 = unspecified 1 = uncommitted Read 2 = Read Committed 3 = repeatable 4 = Serializable 5 = Snapshot
Lock_timeout	Int	The LOCK_TIMEOUT setting for the session. The value is measured in milliseconds.
Deadlock_priority	Int	The Deadlock_priority setting for the session.
Row_count	bigint	The number of rows returned by the session so far.
Prev_error	Int	The ID of the most recent error returned by the session.

For example, we want to see those hosts connected to SQL Server servers, you can use the following SQL statement:

Select distinct host_name from sys.dm_exec_sessions

To see those users connected to SQL Server servers:

Select distinct login_name from sys.dm_exec_sessions

Of course, using the columns above, we can get more client information we want.

2, Sys.dm_exec_connections
This view returns the details of each connection connected to the SQL Server servers, and the following is the column returned by Sys.dm_exec_connections:

Column Name	Data Type	Description
session_id	Int	Identifies the session associated with this connection.
most_recent_session_id	Int	Displays the session ID of the most recent request associated with this connection.
Connect_time	Datetime	Timestamp when the connection was established.
Net_transport	nvarchar (40)	Describes the physical transport protocol used by this connection.
Protocol_type	nvarchar (40)	Specifies the protocol type of the payload. This parameter is currently distinguishable from TDS (TSQL) and SOAP.
Protocol_version	Int	The version of the data access protocol associated with this connection.
endpoint_id	Int	Unique identifier of the endpoint associated with this connection. This endpoint_id can be used to query the sys.endpoints view.
Encrypt_option	nvarchar (40)	A Boolean value that indicates whether encryption is enabled for this connection.
Auth_scheme	nvarchar (40)	Specifies the SQL server/nt authentication to use with this connection.
Node_affinity	smallint	Displays the SOS node associated with this connection.
Num_reads	Int	The number of read packets that have occurred in this connection.
Num_writes	Int	The number of write packets that have occurred in this connection.
Last_read	Datetime	Timestamp of the last read operation occurred in this connection.
Last_write	Datetime	Timestamp of the last write operation in this connection.
Net_packet_size	Int	The size of the network packet used for information and data.
Client_net_address	varchar (40)	The host address of the client to which this server is connected.
Client_tcp_port	Int	The port number on the client that is associated with the connection.
Local_net_address	varchar (40)	Displays the IP address of the destination server for this connection. Available only for connections that use the TCP transport provider.
Local_tcp_port	Int	If this connection uses a TCP transport, the TCP port of the destination server for that connection is displayed.
connection_id	uniqueidentifier	Each connection is uniquely identified.
parent_connection_id	uniqueidentifier	Identifies the primary connection that the MARS session is using.
Most_recent_sql_handle	varbinary (64)	The SQL handle of the last request executed on this connection. The Most_recent_sql_handle column is always synchronized with the most_recent_session_id column.

For example, I want to see the IP of the client IP that is currently connected and the server on which SQL Servers are located, using the following SQL query:

Select client_net_address ' Client IP ', local_net_address ' server IP ' from sys.dm_exec_connections where [email protected] @spid

The function of the @ @spid is to return the session ID of the current process.

How to get the IP address, computer name and other information of the front-end connection in SQL Server