Identify SQL Server TCP IP port being used

By:   |   Updated: 2022-02-24   |   Comments (11)   |   Related: 1 | 2 | > SQL Server Configurations


Problem

How do I find out what TCP/IP port SQL Server is using for a specific SQL Server instance? In this tip we look at different ways a database administrator can identify the port used by instance of SQL Server.

Solution

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.

In this tip we will take a look at 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

Let's take a look at each of the above options in detail.

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', N'any', NULL, NULL, N'asc' 
GO

sql server error log port number

XP_READERRRORLOG

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. Hence I recommend that you read this tip Increase the Number of SQL Server Error Logs.

If you use sp_cycle_errorlog to cycle the SQL Server Error Logs you will need to look in the archive files to find the port information, because this is only stored in the startup error log.

Also, if you are using endpoints, such as Database Mirroring these will show up as ports as well. The way to differentiate the ports being used is to look at the data where the ProcessInfo column equals 'Server' to find the port used for the database engine.

2 - Identify Port used by SQL Server Database Engine Using SQL Server Configuration Manager

1. Open 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.

sql server configuration manager port number

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 dynamic port or TCP Port for a static port as highlighted in the snippet below.

sql configuration manager port number

3 - Identify Port used by SQL Server Database Engine Using Application Event Viewer

1. Open Event Viewer, Computer Management or Server Manager (to find Event Viewer)

2. Under 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.

window event viewer sql server port number

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.

window event viewer sql server port number details
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ashish Kumar Mehta Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2022-02-24

Comments For This Article




Tuesday, April 10, 2018 - 5:35:52 PM - Xikki Back To Top (75666)

Thanks a lot for this.  I have spent endless hours trying to connect from a Linux Machine to a windows SQL server through SQLCMD and I couldn't figure out the correct Port number since I have several instances.

Thanks to you I finally got the correct port number from the Dynamic Ports and connected :)

 

 

 

Monday, March 7, 2016 - 2:12:54 PM - Yamanote Back To Top (40880)

Hi ESquelbe,

I tried netstat -abn posted on:

MSSQL: Ver puerto de una instancia
 
and it works but you must open CMD with UAC disabled.
 
Also read in this post, to check the port number exploring a registry key:
 
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SQLVERSION.INSTANCENAME\MSSQLServer\SuperSocketNetLib\Tcp\IPAll
 
 

 

Monday, March 7, 2016 - 6:34:48 AM - ESquelbe Back To Top (40875)

Very useful post!

You can also use netstat.

If you run from command line: netstat -abn you can find the process sqlservr.exe and the port binding.

Source: http://www.sysadmit.com/2016/03/mssql-ver-puerto-de-una-instancia.html

 

Wednesday, September 30, 2015 - 8:16:49 AM - Dinesh Back To Top (38792)

Hi ,

Am trying to migrate sql server to oracle via sql developer tool.

i have added jtds driver to enable sql serevr option.unable to retrieve sql db

Thursday, November 29, 2012 - 5:01:57 PM - gaultz Back To Top (20646)

This...

Tuesday, September 25, 2012 - 4:33:00 AM - sammo Read The Tip

if you are using SQL 2005 or above, you can also try

SELECT distinct local_tcp_port FROM sys.dm_exec_connections

Works like a charm! Thanks sammo

 

Tuesday, September 25, 2012 - 4:33:00 AM - sammo Back To Top (19671)

if you are using SQL 2005 or above, you can also try

SELECT distinct local_tcp_port FROM sys.dm_exec_connections

Friday, August 3, 2012 - 2:44:51 AM - Arnold Back To Top (18896)

Configuration: Windows Server 2008 R@ with SQL server 2008. Running 4 SQL instances. 1 is the deafult and 3 mamed instances. On my remote workstation I can connect to the default instance with Management Studio.  Just Server name and done. But I cannot connect to the instances. I used servername\instancename.  it worked locally, but not remote. Please help.

Tuesday, November 1, 2011 - 1:36:02 PM - NJ-DBA Back To Top (14982)

You could also run Netstat -a -b from the command line.

Thursday, September 22, 2011 - 7:31:23 AM - Fausto Branco Back To Top (14720)

You can use the view Sys.DM_Exec_Connections:

 

Select Net_Transport, Protocol_Type, Local_TCP_Port
  from sys.dm_exec_connections r
  Where Session_id = @@SPID


Wednesday, September 21, 2011 - 8:59:01 AM - Greg Robidoux Back To Top (14711)

Nikolay, thanks for the alternative approach.

Wednesday, September 21, 2011 - 8:13:29 AM - Nikolay Back To Top (14709)

Here is another script to see the port number:

set nocount on
DECLARE @test varchar(20), @key varchar(100)
if charindex('\',@@servername,0) <>0
begin
set @key = 'SOFTWARE\MICROSOFT\Microsoft SQL Server\'+@@servicename+'\MSSQLServer\Supersocketnetlib\TCP'
end
else
begin
set @key = 'SOFTWARE\MICROSOFT\MSSQLServer\MSSQLServer\Supersocketnetlib\TCP'
end
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',@key=@key,@value_name='Tcpport',@value=@test OUTPUT
SELECT 'Server Name: '+@@servername + ' Port Number:'+convert(varchar(10),@test)














get free sql tips
agree to terms