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
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.
sys.dm_exec_connnections is a column
connection_id but the uuid differs from the one created by the
SqlClient in .NET.
Where does the SQL Server store the client_connection_id (table/view) and is this accessible without creating an extended event?
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).
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.