Thursday 6 December 2012

How to Find Port Number Of a SQL Server Instance?

1) Using the GUI:
For SQL Server 2000, we can use SQL Server Network Utility, as below screen shot show how to check

For SQL server 2005 and 2008, we can use SQL  Server Configuration Manager as below.
2)  Using SQL Server Error log:  We can see some entry in the startup time after creating tempdb, as like in the below screen.
3)  Registry Entry: 
HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\TCP
 As below screen, you need run  regedit  in command line (be careful  while running regedit if you modify anything in the registry that will cause system failure).  If you are running multiple named instance then you have to select the named instance in regedit.

4) Using Extended Stored Procedure xp_regread: we can  find out the value of the TcpPort by using SQL.  

Example:
DECLARE @tcp_port nvarchar(5)
EXEC xp_regread
@rootkey    =    'HKEY_LOCAL_MACHINE',
@key        =    'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP',
@value_name    =    'TcpPort',
@value        =    @tcp_port OUTPUT
select @tcp_port
5)  Using “netstat –an”:  we can see all the ports listening on that server, in the below screen it shows 1433 which is SQL server port and 2383 is SSAS port  80 is SSRS port.  We can also use other tools like Microsoft  net monitor, network sniffer or any other third part tools.

No comments:

Post a Comment