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
Super bro this helped me a lot
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