Sql-server – MSSQL Server XE column client_connection_id accessible through system view/table for auditing

ado.netextended-eventsprofilersql server

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.

Best Answer

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

Related Solutions

SQL Server Profiler – Error Using Triggers with Profiler Tables

These are the messages that get logged:

16943 Could not complete cursor operation because the table schema changed after the cursor was declared.
19052 The active trace must be stopped before modification.

So it seems like this is an inherent issue with creating a trigger on the trace table (and I did not crack open a debugger to see exactly what Profiler is trying to do in this case). This has nothing to do with the fact that you're pulling spid values in the trigger, or using DBCC INPUTBUFFER, or using dynamic SQL. A simple trigger that just does PRINT N'hi'; also causes the same symptom: those two exceptions, the error dialog in Profiler, and the trace stops logging to that table altogether.

Perhaps you can just set up a trace that collects both the sort warning events and SQL:StmtCompleted and try to manually correlate them using sequence / transaction info or simple intuition. Or you could try my idea from above:

You can always automate pulling in trace data to a table from a file once a server-side trace is up and running - I suspect such a table would have the ability to utilize such a trigger when Profiler is not involved.

It's not exactly as simple as it sounds, and it would have to poll quite frequently in order for the trigger to rely on the timeliness of subsequent inputbuffer data. I don't want to get too deep into the implementation details of that, it's just an off-the-cuff idea.

The other idea is to upgrade. :-)

SQL Server 2012 – Extended Event Session to Expose Network Protocol

Found the answer.

The login event is correct and the network protocol is exposed via the options_text action. options_text was always blank for me previously and this needs to be set on via SET collect_options_text=(1). An example session might be: -

CREATE EVENT SESSION [Login] ON SERVER
ADD EVENT sqlserver.login(SET collect_options_text=(1)
    ACTION(
    sqlserver.session_id
    ,sqlserver.username
    ))
ADD TARGET package0.ring_buffer
WITH (
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=1 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=ON,
    STARTUP_STATE=ON
)
GO

And this would then populate options_text with something like: -

network protocol: TCP/IP set quoted_identifier on set arithabort off set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_commit off set implicit_transactions off set language us_english set dateformat mdy set datefirst 7 set transaction isolation level read committed

I found this out be chance by creating a session from the "Connection Tracking" Microsoft shipped extended events template.

That template includes "SET collect_options_text=(1)" and I wasn't previously aware such an option existed.

An example is also given in this sql-server-performance article. The last screenshot shows options_text populated with "network protocol: LPC.." LPC is shared memory.