Rotating Header Image

What TCP Port is SQL Server running under?

This question came up as a result of a consolidation exercise.  We wanted to ensure the TCP port is the same between the two servers we were looking to consolidate. By default (unless changed) the default TCP Port for SQL Server is 1433. If you have a named instance the TCP port is dynamically configured.

There are a few options when trying to find out this information… one way would be to go right to the registry and look at the TCP settings:

SQL 2005
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.<InstanceNumber>\MSSQLServer\
SuperSocketNetLib\TCP\

SQL 2008
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.<InstanceName>\MSSQLServer\
SuperSocketNetLib\TCP\

Another way would be to run the undocumented system procedure xp_readerrorlog.  This will show the current SQL Server log and you can find the row “Server is listening on”.  This is NOT a recommended approach because if you cycle your error logs frequenty the current log might not have this information as it is only generated when the SQL Server service restarts.  You may have to go through a few logs before finding the information you’re looking for and that’s just a waste of time.

An easier way to find out the same information would be to use the SQL Server Configuration Manager.  If you expand the SQL Server Network Configuration and select Protocols for MSSQLSERVER (default instance) in the right hand pane you’ll see your protocols (Shared Memory, Named Pipes, TCP/IP, VIA).  If you double click on TCP/IP the properties dialog box will appear and by selecting the IP Addresses tab you will now see your IP addresses and which TCP port they are running under.

If you need to change this port number make sure you restart the SQL Server service as this change will not take affect until you do. (Here is the BOL entry for changing the TCP Port)

(K. Brian Kelley with an additional way to find out this information)

“Another way, if you’re on the system.

Task Manager
View | Select Columns | Check PID | OK
Find sqlservr.exe as the Image Name, note the PID
Command prompt:
netstat -ano | findstr *PID*

The PID is displayed in the far right. Look for the TCP entry. The port will be after the colon (e.g. 0.0.0.0:1433 means the port is 1433).”

Thanks!!



Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

10 Comments

  1. Another way, if you’re on the system.

    Task Manager
    View | Select Columns | Check PID | OK
    Find sqlservr.exe as the Image Name, note the PID
    Command prompt:
    netstat -ano | findstr *PID*

    The PID is displayed in the far right. Look for the TCP entry. The port will be after the colon (e.g. 0.0.0.0:1433 means the port is 1433).

  2. [...] What TCP Port is SQL Server running under? The default port for SQL Server is 1433, but when you run multiple instances of SQL Server on the same server, you have to do a little detective work to find the port number of each instance. [...]

  3. Paul Els says:

    Approx 2 weeks ago a developer comes running to me, his SQL Server instance is running, but one cannot connect to it.

    Turns out someone (no one owns up) created an alias directing it to the wrong port.

    It was clearly visible when starting that instance in single user mode and checking which port it is starting up on.

  4. mknopf says:

    Thanks a ton, I used the Configuration Manager to discover that my named instance was using a dynamic port of 1105.

  5. ashwinjk says:

    Thanks a lot . The information helped me

  6. alan says:

    xp_readerrorlog 0,1,’listen’

  7. Colin says:

    Hi Alan

    Thanks for the reply but if you cycle your error log periodically this will not work because unless your SQL Server has been restarted since the last log cycle.

  8. Ian says:

    Re:

    “Turns out someone (no one owns up) created an alias directing it to the wrong port.”

    How can this happen?

  9. Sam says:

    Thanks, the information was really helpful.

  10. Neo says:

    Thanks ! this info regarding SQL server helped me , prior to this i was banging my head with portal-ext only but it wast working

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.