SQL Server Trace – Based on Client TCP Port

Securitysql serversql-server-2008-r2trace

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?

Best Answer

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;

Related Solutions

Sql-server – How Should TCP/IP be configured to allow an external application access to SQL via the same port #

Not sure if I fully understand the question. But the internal applications should be fine about the port number unless there are also firewalls internally, in which case you just allow the port through there.

For the external access, I'd consider a static port so you can stop doing what you have to do. In an ideal world you'd only really expose your SQL Server to the web server itself externally, so if the firewall can just trust the webservers IP only that is a good thing to do only, instead of just being wide open for that port for any incoming traffic.

But you just ned to set an static port in the network config:

enter image description here

So instead of a 0 for dynamic ports, type a port number in for the TCP Port you wish to use.

Sql-server – JDBC connection error for SQL Server Database

It is unlikely that a named instance is running on port 1433, unless you explicitly set it as such. I also suspect that SSMS is using shared memory (if local) or named pipes (if remote), unlike JDBC which is using TCP/IP (and I don't know how to set JDBC to use a different protocol).

From this documentation, it seems your connection string should be:

jdbc:sqlserver://MS8AT09;instanceName=AT09;...

With no port designation. You could also try using the IP address instead of the name, in case name resolution is hampered for some reason.

Please check what port the instance is running on (look at SQL Server Configuration Manager)...

...and confirm your connection string does not hard-code a port that is different (JDBC might ). Do not hard-code the dynamic port, because it may not be the port used next time SQL Server starts. From Microsoft's documentation:

We recommend that the port number always be specified, as this is more secure than using sqlbrowser.

So, you may want to set your named instance to run on a specific port number, instead of using a dynamic port (the default), and then hard-coding that port number in the JDBC connection string. You can use the one that the instance was using dynamically, since you know it isn't in use by anything else. Also see KB #823938. So your connection string would become (assuming you used port 56737):

jdbc:sqlserver://192.168.0.65;instanceName=AT09;port=56737;...

Best Answer

Related Solutions

Sql-server – How Should TCP/IP be configured to allow an external application access to SQL via the same port #

Sql-server – JDBC connection error for SQL Server Database

Related Question