Wednesday, March 18, 2015

Query to find the Port number of the SQL server





Step 1


DECLARE @tcp_port nvarchar(5)
if @@SERVICENAME = 'MSSQLSERVER'
BEGIN


EXEC xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP',
@value_name = 'TcpPort',
@value = @tcp_port OUTPUT
END
ELSE
BEGIN
declare @keyname nvarchar(500)


set @keyname = '' + 'Software\Microsoft\Microsoft SQL Server\' + @@SERVICENAME + N'\MSSQLServer\SuperSocketNetLib\TCP'


EXEC xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = @keyname,
@value_name = 'TcpPort',
@value = @tcp_port OUTPUT
END


select @tcp_port




Step 2




SELECT DISTINCT
local_tcp_port

FROM sys.dm_exec_connections
WHERE local_tcp_port IS NOT NULL
and net_transport = 'TCP'