Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Find which port SQL Server is using to establish connections

By Dan Hess,

By default, SQL Server listens on port 1433 for TCP/IP connections and 1434 for UDP broadcasts. But what if this was changed and what about a named instance? You can find the listener port in a few ways:

  1. Check the Windows Application event log - Event ID 26022
  2. Use SQL Server Configuration Manager
  3. Query the Windows Registry

Windows application event log

Each time an instance starts, SQL Server will record its listening port(s) in the Windows Event Viewer. To find this, open up the Windows Event Viewer and choose the 'Application' event log. If you filter on Event ID 26022, you should see four events associated with the start up. Look for the event that has this in the message body:

 

 

I've shown here that the named instance 'JOHNSQLSERVER' is listening on port 49526. As I stated, there are four events associated with the start up; the other three events will report the IPv6 protocol and the UDP broadcast port:

Server is listening on [ 127.0.0.1 49527].

Server is listening on [ ::1 49527].

Server is listening on [ 'any' 49526].

Make sure to look for the message in the image. The same information also apples for the default instance.

SQL Server Configuration Manager

Another easy and effective way is to use SQL Server Configuration Manager. Open it up and expand 'SQL Server Network Configuration' in the left hand pane. You'll see menu items for protocols for your various instances. Single click one of the protocol menu items and you'll get a list of protocols for that instance appearing in the right hand pane. Right click 'TCP/IP' and choose 'Properties':

 

 

Choose the 'IP Addresses' tab. Scroll to the bottom of the list and find the 'IPAll' section. The 'TCP Port' setting will have the listening port, unless the instance is set up to listen dynamically. If so, the 'TCP Dynamic Ports' setting will contain the listening port:

 

Using the Windows Registry (via TSQL)

The registry also stores this information. It can be read using the following:

DECLARE @InstName VARCHAR(16)

DECLARE @RegLoc VARCHAR(100)

SELECT @InstName = @@SERVICENAME

IF @InstName = 'MSSQLSERVER'
  BEGIN
    SET @RegLoc='Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\'
  END
 ELSE
  BEGIN
   SET @RegLoc='Software\Microsoft\Microsoft SQL Server\' + @InstName + '\MSSQLServer\SuperSocketNetLib\Tcp\'
  END

EXEC [master].[dbo].[xp_regread] 'HKEY_LOCAL_MACHINE', @RegLoc, 'tcpPort'

This returns the port(s) in a result set as shown here:

 

 

Conclusion

There are multiple ways to determine the port on which a SQL Server instance is listening. If you have trouble connecting, use one of the techniques outlined here to confirm that you're connecting to the correct port.

Total article views: 6654 | Views in the last 30 days: 10
 
Related Articles
BLOG

SQL Server – Finding TCP Port Number SQL Instance is Listening on

By default SQL Server listens on TCP port number 1433, and for named instances TCP port is dynamical...

FORUM
FORUM

Disable Windows Event Log services

Windows event viewer causing server issues

BLOG

How to Make SQL Server Listen on Multiple Ports

Have you ever needed an instance of SQL Server to listen on multiple TCP/IP port numbers but didn't ...

BLOG

Using DMVs to Find the Ports that SQL Server is Listening On

The other day I was asked to provide the port number that a SQL Server instance was listening on. As...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones