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
Advertisements

2 thoughts on “T-SQL Script to check SQL Server Instance TCP/IP Port Number

  1. James Pierce

    Here is a bit of an adaptation of your script that combines everything in one result.
    — Find Database Port script by Jim Pierce 09/05/2018

    USE [master]
    GO

    DECLARE @DynamicportNo NVARCHAR(10);
    DECLARE @StaticportNo NVARCHAR(10);
    DECLARE @ConnectionportNo INT;

    — Look at the port for the current connection
    SELECT @ConnectionportNo = [local_tcp_port]
    FROM sys.dm_exec_connections
    WHERE session_id = @@spid;

    — Look for the port being used in the server’s registry
    EXEC xp_instance_regread @rootkey = ‘HKEY_LOCAL_MACHINE’
    ,@key =
    ‘Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll’
    ,@value_name = ‘TcpDynamicPorts’
    ,@value = @DynamicportNo OUTPUT

    EXEC xp_instance_regread @rootkey = ‘HKEY_LOCAL_MACHINE’
    ,@key =
    ‘Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll’
    ,@value_name = ‘TcpPort’
    ,@value = @StaticportNo OUTPUT

    SELECT [PortsUsedByThisConnection] = @ConnectionportNo
    ,[ServerStaticPortNumber] = @StaticportNo
    ,[ServerDynamicPortNumber] = @DynamicportNo
    GO

    Reply

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s