current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 7 down vote favorite

I have a Windows terminal server with many different users logging in via RDP to run an application. The application makes one or more connections per user to a SQL Server 2008 R2 instance. All users access the same database using the same SQL login. I would like to be able to trace a particular user's SQL session, but I have not found a way to determine which SQL session(s) belongs to which user. However, I am able to determine the source TCP port each instance of the application is using.

Is there a way to trace a SQL session based on the client's TCP port?

share|improve this question

1 Answer 1

up vote 6 down vote accepted

Is there a way to trace a SQL session based on the client's TCP port?

Yes. You can query sys.dm_exec_connections to identify a session from the client's TCP port (column client_tcp_port).

For example:

SELECT DEC.session_id
FROM sys.dm_exec_connections AS DEC
WHERE DEC.client_net_address = '192.168.0.100'
AND DEC.client_tcp_port = 63465;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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