SQL Script to Find the SQL Server Listening Port

Issue
When troubleshooting application and database connectivity issues, verifying the port on which the SQL instance is listening
for connections is a basic and important troubleshooting step.  This is also important information to obtain, to verify this port is open and
not being blocked by any firewall rules.

Product
Struxureware Power Monitoring
Power Monitoring Expert
ION Enterprise
ION EEM

Environment
Microsoft SQL Server

Cause
Although the default port is 1433, the SQL instance can be configured to listen on any other desired port.

Resolution
*Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.*

1) Open SQL server Management Studio and connect to the desired instance

2) Start a new query

3) copy the script below and execute

DECLARE @key VARCHAR(50), @RegistryPath VARCHAR(200)
IF (SERVERPROPERTY('INSTANCENAME')) IS NULL
BEGIN
SET @RegistryPath='Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp'
END
ELSE
BEGIN
SET @RegistryPath='Software\Microsoft\Microsoft SQL Server\'+CONVERT(VARCHAR(25),SERVERPROPERTY('INSTANCENAME')) + '\MSSQLServerSuperSocketNetLib\Tcp'
END
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', @RegistryPath, 'tcpPort'

** see attachment containing the SQL script text

Publikováno pro: Schneider Electric Česká republika