SQL Server Error Log
-- Check the error log for port number EXEC xp_ReadErrorLog 0, 1, N'Server is listening on', N'any', NULL, NULL, 'DESC' GO
Executing DMV Script
-- you must connect remotely using TCP/IP SELECT local_tcp_port FROM sys.dm_exec_connections WHERE session_id = @@SPID GO
For Dynamic Port Number
— Read the registry for port number
-- Execute below script if SQL Server is configured with dynamic port number DECLARE @portNo NVARCHAR(10) EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = 'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll', @value_name = 'TcpDynamicPorts', @value = @portNo OUTPUT SELECT [PortNumber] = @portNo GO
For Static Port Number
-- Execute below script if SQL Server is configured with static port number DECLARE @portNo NVARCHAR(10) EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = 'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll', @value_name = 'TcpPort', @value = @portNo OUTPUT SELECT [PortNumber] = @portNo GO