CODEWITHSUNDEEP
sql-serverwindows-10sql-server-2016
Kevin Wu
Kevin Wu

Reputation: 1467

Find out port of database running in SQL Server 2016

I am wondering how to find out at which port a database running locally in SQL Server 2016 is accepting requests.

The Problem is that the database was set up by a partner, but the port wasn't documented. I tried the default port of 1433, but that did not work.

I also tried using SQL Server Management Studio, but did not find what I was looking for.

Upvotes: 0

Views: 8165

Answers (1)

Wes Palmer
Wes Palmer

Reputation: 880

You can use this query I have been using:

SELECT DISTINCT local_net_address, local_tcp_port
FROM sys.dm_exec_connections 
WHERE local_net_address IS NOT NULL

OR -- Execute below script if SQL Server is configured with dynamic port number

SELECT local_tcp_port
FROM   sys.dm_exec_connections
WHERE  session_id = @@SPID

OR

-- 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

At least one of these will work for you.

Upvotes: 2

Related Questions