#0365 – SQL Server – Auditing – Fetching properties of the current connection using CONNECTIONPROPERTY


Most enterprise applications implement auditing in areas of application that have business importance, data cleanup and data quality improvement. It therefore becomes important to track the following:

  1. From where a particular data manipulation request is coming? (Client Name, IP address, etc)
  2. What is the connection mechanism used?
  3. Which port is being used by the connection?
  4. What is the payload type (TSQL/SOAP or other)

While Microsoft SQL Server already provides us a way to access connection properties using the DMV – sys.dm_exec_connections, the challenge is in finding the required information for the current connection only, and not for all connections to the server.

The solution is therefore to use the system function – CONNECTIONPROPERTY().

CONNECTIONPROPERTY returns the connection properties for the connection on which the request came in. Hence, unless the auditing runs on a separate connection, we would always get information about the connection that is actually performing the data manipulation. This makes the function an ideal method to implement auditing within triggers.

Allow me to demonstrate it with 2 examples – one where the connection was done using Shared Memory (my SSMS client and the database engine are on the same machine) and using TCP/IP.

SELECT CONNECTIONPROPERTY('net_transport') AS TransportProtocol,
       CONNECTIONPROPERTY('protocol_type') AS PayloadType, 
       CONNECTIONPROPERTY('auth_scheme') AS AuthenticationUsed,
       CONNECTIONPROPERTY('local_net_address') AS TargetServerIPAddressIfTCPUsed,
       CONNECTIONPROPERTY('local_tcp_port') AS TargetServerTCPPortIfTCPUsed,
       CONNECTIONPROPERTY('client_net_address') AS ClientAddress,
       CONNECTIONPROPERTY('physical_net_transport') AS PhysicalTransportProtocol;
SQLTwins, Post #0365 - Output of CONNECTIONPROPERTY() when using a Shared Memory connection

Output of CONNECTIONPROPERTY() when using a shared memory connection

SQLTwins, Post #0365 - Output of CONNECTIONPROPERTY() when using a TCP/IP connection

Output of CONNECTIONPROPERTY() when using a TCP/IP connection

As demonstrated, the CONNECTIONPROPERTY() is the easiest way (in my opinion) to get the properties of the current connection for auditing purposes.

Further Reading

Until we meet next time,
Be courteous. Drive responsibly.

Advertisements

One thought on “#0365 – SQL Server – Auditing – Fetching properties of the current connection using CONNECTIONPROPERTY

  1. Pingback: #0366 – SQL Server – SSMS – Simulating a TCP/IP connection on the same machine as the server | SQLTwins by Nakul Vachhrajani

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