Ports d’écoute TCP – Scripts Center

Plusieurs moyens d’obtenir en T-SQL les ports d’écoute TCP de l’instance à laquelle on est connecté :

/*-------------------------------------------------------------------
[SCRIPT] TCP ports
[DESCRIPTION] find local tcp ports
[MAJ PAR] DATAFLY - Arian Papillon
[DATE] 20181105
-------------------------------------------------------------------*/
-- pour SQL 2012 et au delà
SELECT value_name AS [Name],value_data AS [Port Number]
From sys.dm_server_registry
Where registry_key like '%IPALL%' and
(value_name ='TcpPort' OR value_name = 'TcpDynamicPorts')

-- ou bien
EXEC xp_readerrorlog 0
                   , 1
                   , N'Server is listening on'
                   , N'any';

-- ou bien
DECLARE       @portNumber   NVARCHAR(10), @dynamicportNumber NVARCHAR(10)
EXEC   xp_instance_regread
@rootkey    = 'HKEY_LOCAL_MACHINE',
@key        = 'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
@value_name = 'TcpPort',
@value      = @portNumber OUTPUT
EXEC   xp_instance_regread
@rootkey    = 'HKEY_LOCAL_MACHINE',
@key        = 'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
@value_name = 'TcpDynamicPorts',
@value      = @dynamicportNumber OUTPUT
SELECT [Port Number] = @portNumber,[Dynamic Port Number]=@dynamicportNumber; 

-- ou bien 
SELECT DISTINCT local_tcp_port FROM sys.dm_exec_connections WHERE net_transport = 'TCP';

/*-------------------------------------------------------------------

[SCRIPT] TCP ports

[DESCRIPTION] find local tcp ports

[MAJ PAR] DATAFLY - Arian Papillon

[DATE] 20181105

-------------------------------------------------------------------*/

-- pour SQL 2012 et au delà

SELECT value_name AS [Name],value_data AS [Port Number]

From sys.dm_server_registry

Where registry_key like '%IPALL%' and

(value_name ='TcpPort' OR value_name = 'TcpDynamicPorts')

-- ou bien

EXEC xp_readerrorlog 0

, 1

, N'Server is listening on'

, N'any';

-- ou bien

DECLARE @portNumber NVARCHAR(10), @dynamicportNumber NVARCHAR(10)

EXEC xp_instance_regread

@rootkey = 'HKEY_LOCAL_MACHINE',

@key = 'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',

@value_name = 'TcpPort',

@value = @portNumber OUTPUT

EXEC xp_instance_regread

@rootkey = 'HKEY_LOCAL_MACHINE',

@key = 'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',

@value_name = 'TcpDynamicPorts',

@value = @dynamicportNumber OUTPUT

SELECT [Port Number] = @portNumber,[Dynamic Port Number]=@dynamicportNumber;

-- ou bien

SELECT DISTINCT local_tcp_port FROM sys.dm_exec_connections WHERE net_transport = 'TCP';