Tag: Port Number

  • T-SQL Script to check SQL Server Instance TCP/IP Port Number

    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