When working with SQL Server, it is common to need to determine the port(s) that SQL Server is listening on. However, there may be situations where you need to know on which port(s) connections are coming through from the client to the database server. This can be particularly useful when dealing with vendors who access the SQL Servers through specific ports in order to distinguish their connections from internal employee connections.
In a recent scenario, I found myself needing to provide a quick report on the vendor connections to our SQL Servers. Initially, I tried searching online for a solution but couldn’t find a straightforward answer. Realizing that this might not be a common operation, I decided to seek help from the SQL Server Central (SSC) community. Thanks to the assistance of fellow SQL Server enthusiasts, I was able to quickly obtain the information I needed.
The solution involved using two system views in SQL Server: sys.dm_exec_connections and sys.dm_exec_sessions. These views provide a wealth of information about the connections to the SQL Server, including the port number, login name, host name, and program name.
Here is a simple script that retrieves the necessary information:
SELECT c.session_id, c.local_tcp_port, s.login_name, s.host_name, s.program_name
FROM sys.dm_exec_connections AS c
INNER JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id
WHERE c.local_tcp_port <> 1433
Running this script will return a result set similar to the following:
| session_id | local_tcp_port | login_name | host_name | program_name |
|---|---|---|---|---|
| 88 | 11433 | DomainXX\vendor1 | D0004785 | .Net SqlClient Data Provider |
| 304 | 2048 | DomainXX\vendor2 | Laptop123 | .Net SqlClient Data Provider |
| 1022 | 2096 | DomainXX\vendor3 | MRE23098 | Microsoft SQL Server Management Studio – Query |
In my case, I saved the output to a CSV file for internal auditing purposes. However, this query can also be used as a data source for SQL Server Reporting Services (SSRS) to generate more formal reports for management.
Aside from its immediate usefulness in providing vendor connection information, this query has also become a valuable tool in my process for tracking down server performance issues. Whenever I receive a performance-related call for one of the servers where vendors have direct access to the databases, I run this query first. It helps me identify any potential performance bottlenecks caused by the vendors’ queries.
I hope this information proves helpful to you and gives you some ideas for managing SQL Server connections. Feel free to reach out if you have any questions or need further assistance.