MS-SQL port in sql server

[Aaliya Seth]

hello sir,
I am using MS-SQL server,a developer asked me suddenly a question regarding the network implementation of SQL server.he asked me- how would I know that the particular instance is running on which port.He is also unaware of this answer and finding the solution.

I don't know also how to do it.please would you help me find out the solution.

[Praetor]

The database is running in the network environment and which one port is occupied by the instance.There are so many ways to find out the port number of SQL server instance.

1- Using the GUI
2- Check the error log
3- Registry entry
4- Using the extended stored procedure

These are the ways through you can find the port number for running instance of SQL server database.

[Katty]

Using the GUI

In SQL Server 2000, The Server Network Utility is available for you to find the port number and on other hand SQL Server 2005 provide you to check the port number using SQL Server Configuration Manager.
Investigate within the SQL Server 2005 Network Configuration and see at the TCP port for the TCP/IP protocol.

These are the basic configuration tools which can be used with both database versions.

[kelfro]

Using error log :

When you use the error log then you will get an entry tagged in the file as follows -

“Server is listening on [ 'any' <ipv4> 1433].”

The last digits constructed with four numbers is the TCP/IP port number which is currently used by the running instance from where the log error file belongs to.The IP address is also extended .

[Zecho]

Using the extended stored procedure

One of the procedure which is created by default in the database for accessing the network information can be used to get the port number of running instance and the name of the procedure is xp_regread.

The code carried by the procedure is as follows-

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

Execute this code within the SQL statement and get the port number of current instance.