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!!
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).
[...] 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. [...]
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.
Thanks a ton, I used the Configuration Manager to discover that my named instance was using a dynamic port of 1105.
Thanks a lot . The information helped me
xp_readerrorlog 0,1,’listen’
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.
Re:
“Turns out someone (no one owns up) created an alias directing it to the wrong port.”
How can this happen?
Thanks, the information was really helpful.
Thanks ! this info regarding SQL server helped me , prior to this i was banging my head with portal-ext only but it wast working