3

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.

Improve this question
1

1 Answer 1

Reset to default
-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
Improve this answer
1
  • 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
    – rogaa
    Commented Nov 19, 2019 at 6:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.