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;

Output of CONNECTIONPROPERTY() when using a shared memory 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

• CONNECTIONPROPERTY – [MSDN/BOL Page]
• DMV – sys.dm_exec_connections – [MSDN/BOL Page]

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