Home > SQL Server > Get a property value of the SQL connection using ConnectionProperty function – SQL Server

Get a property value of the SQL connection using ConnectionProperty function – SQL Server


CONNECTIONPROPERTY (‘Property name’) : This function returns the specific connection property of the current connection made to server.

There are predefined property names available in SQL server. So, we should use the same name to get the value of the property.

Property Names

  • net_transport – Gives the name of the transport protocol of the connection
  • protocol_type – Give the name of the protocol type used in the connection
  • auth_scheme – Gives SQL server authentication name
  • local_net_address – Gives the IP address of the targeted connection
  • local_tcp_port – Gives an integer port number of the targeted connection
  • client_net_address – Gives the client IP address of the connection
  • physical_net_transport – Gives the physical transport protocol used in the connection. It gives value if the connection has (MARS) enabled.

If we use any other value apart from the above list then we will get null value.

Implementation

Executing ConnectionProperty function with specific property name argument

Script

SELECT
ConnectionProperty(‘net_transport’) AS ‘Net transport Protocol’,
ConnectionProperty(‘local_net_address’) AS ‘Server IP Address’,
ConnectionProperty(‘local_tcp_port’) AS ‘Port’,
ConnectionProperty(‘protocol_type’) AS ‘Protocol type’,
ConnectionProperty(‘client_net_address’) AS ‘Client IP Address’,

ConnectionProperty(‘SQL Server Rider’) AS ‘SQLhelper’

Output

pic1We can also use DMV sys.dm_exec_connections to get details of all active connections in the SQL Server.

Execute this query in your SQL server: Select * from sys.dm_exec_connections

This DMV contains the lot of information about the connections.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

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

%d bloggers like this: