MSSQL Server XE column client_connection_id accessible through system view/table for auditing?

Question

In our .NET application we create a SqlConnection to talk to the database. After the connection is opened, we get the ClientConnectionId, a GUID.

Within the extended events I can manage to trace the information what happens on the database regarding this ClientConnectionId via the column client_connection_id (for e.g. in the sql_statement_completed event).

Looking at the sys.dm_exec_sessions or the sys.dm_exec_connections I can't find a matching to the client_connection_id from the extended event. In sys.dm_exec_connnections is a column connection_id but the uuid differs from the one created by the SqlClient in .NET.

Question

Where does the SQL Server store the client_connection_id (table/view) and is this accessible without creating an extended event?

Background

Our support team still uses the SQL Profiler and don't know anything about extended events. The profiler is good enough to get some small insight what happens on the database which then can be handed over to the developing team for further investigations.

Usually we used the SPID, but there will be some changes made to the application where we will use connection pooling in the future and with ef core there will be no permanent connection opened for each client, but rather for each db query a new connection.

Now I am trying to get a solution in how to be still possible to audit the database for each client (and not too complex for the support team). Manipulate the application name or the hostname in the connectionstring would be another possibility, but I doubt this is recommended.

Any thoughts and help are warmly appreciated.

• SqlWorldWide: I am afraid this information is only available in extended event trace (or ring buffer). Accessing diagnostic information in the extended events log.

Answer 1

You may use this and filter using program_name, login, IP address...

select c.connection_id from sys.dm_exec_connections c
join sys.dm_exec_sessions s on c.session_id=s.session_id

• rogaa: Thanks for your input, unfortunately this will not work, because many of our customers use our software via terminalservers. In the past the spid was unique, now it's getting tricky when using an applicationserver. I tested your command: The .NET application gives me the following clientConnectionId = 0d3cf332-333e-4efb-87ce-a58a49297eb9 Checking the dmv I'll get the following connection_id = 67949421-94A0-4CF2-A521-F04AF986EB17