Sur quel port TCP écoute mon instance SQL ?

Pour configurer le firewall et ouvrir les ports nécessaires, j'ai besoin de connaître sur quel port TCP mon instance est à l'écoute. Tout va bien si j'ai le gestionnaire de configuration SQL Server à ma disposition, mais comment faire si je n'ai accès qu'à une fenêtre de requête ?

La première méthode consiste bien évidemment à utiliser le gestionnaire de configuration.

Pour une instance par défaut, le port configuré, réservé par l'IANA (Internet Assigned Numbers Authority) pour SQL Server est le TCP 1433. Cela suffit pour nous connecter avec le nom de machine ou l'adresse IP du serveur.

Pour une instance nommée (avec un nom d'instance), par contre, SQL Server assigne dynamiquement un port TCP disponible lors du premier démarrage de SQL Server. Lors des démarrages suivants, il tentera d'utiliser ce même port, mais si ce port est déjà utilisé par ailleurs, il pourra sélectionner un autre port sans prévenir !

Ce port dynamique n'étant pas connu par le client qui veut se connecter (avec le nom du serveur et le nom de l'instance), il sera résolu par l'intermédiaire du service SQL Browser dont c'est le rôle, et qui est à l'écoute sur le port UDP 1434.

Dans la boîte de dialogue du gestionnaire de configuration, il est aussi possible de sélectionner un port statique de son choix : il ne pourra changer (et SQL Server ne pourra pas démarrer correctement s'il est déjà pris).

La configuration du port est stockée parmi les clés de registre concernant l'instance. On peut donc aussi aller voir avec regedit...!

Il m'arrive parfois que j'aie besoin de connaître le port TCP alloué à SQL Server (statique ou dynamique) sans pouvoir avoir accès au gestionnaire de configuration. Il existe plusieurs solutions pour ce faire en TSQL :

1. Avec la dmv sys.dm_server_registry : à partir de la version 2012 de SQL Server

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');

2. Avec la procédure stockée étendue xp_instance_regread

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;

3. En lisant le journal d'erreurs

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

4. En regardant sur quel port sont les sessions connectées

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

Bonne administration SQL Server !