You probably know that by default, the SQL Server Database Engine listens on port 1433 for TCP/IP connections and port 1434 is used for UDP connections. However, if you have more than one instance of SQL Server running on the same server or if you have changed the default port then it may be difficult to know the port used by the database engine.
There are three different ways you can identify the port used by an instance of SQL Server.
1. Reading SQL Server Error Logs
2. Using SQL Server Configuration Manager
3. Using Windows Application Event Viewer
1. Identify Port used by Named Instance of SQL Server Database Engine by Reading SQL Server Error Logs
The SQL Server Error Log is a great place to find information about what is happening on your database server. The SQL Server Error Log records information with respect to the port in which an instance of the SQL Server Database Engine is listening. You can execute the below TSQL command which uses the XP_READERRORLOG extended stored procedure to read the SQL Server Error Log to find the port the SQL Server Database Engine is listening.
USE master GO xp_readerrorlog 0, 1, N'Server is listening on', 'any', NULL, NULL, N'asc' GO
The parameters you can use with XP_READERRRORLOG are mentioned below for your reference:
1. Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc…
2. Log file type: 1 or NULL = error log, 2 = SQL Agent log
3. Search string 1: String one you want to search for
4. Search string 2: String two you want to search for to further refine the results
5. Search from start time
6. Search to end time
7. Sort order for results: N’asc’ = ascending, N’desc’ = descending
By default, there are six archived SQL Server Error Logs along with the ERRORLOG which is currently used. However, it is a Best Practice to increase the number of SQL Server Error Logs from the default value of six.
2. Identify Port used by SQL Server Database Engine Using SQL Server Configuration Manager
1. Click Start -> Programs -> Microsoft SQL Server 2008 -> Configuration Tools -> SQL Server Configuration Manager.
2. In SQL Server Configuration Manager, expand SQL Server Network Configuration and then select Protocols for <instance name> on the left panel. To identify the TCP/IP Port used by the SQL Server Instance, right click on TCP/IP and select Properties from the drop down as shown below.
3. In TCP/IP Properties window click on the IP Addresses tab and you will see the Port used by the instance of SQL Server in either TCP Dynamic Ports for a dymanic port or TCP Port for a static port as highlighted in the snippet below.
3. Identify Port used by SQL Server Database Engine Using Application Event Viewer
1. Click Start -> All Programs -> Administrative Tools -> Server Manager.
2. In Server Manager, expand Diagnostics, expand Event Viewer, expand Windows Logs and then select Application on the left side panel. In the right panel you need to filter for events with Event ID 26022 as shown in the below snippet. To set a filter right click on Application and select Filter Current Log.
3. To view the Port Number double click an event and you can see the event properties as shown below. In this case, the named instance of SQL Server is listening on Port 57319. Note: you should look for the following Event “Server is listening on [ ‘any’ <ipv4> PortNumber]” in the event viewer. Also, if you have endpoints setup like Database Mirroring these will show up under EventID 26022 as well, so it gets a little harder to tell using this method.
If you found any of the information on this page helpful in anyway then please consider sharing this content with your favorite social network or by leaving your thoughts in the comment section. Thanks!