How to find SQL Server running port?

Question

Yes I read this How to find the port for MS SQL Server 2008?

no luck.

telnet 1433

returns connection failed, so I must specify other port.

I tried to use

netstat -abn

but I don't see sqlservr.exe or something similar on this list.

Why it so difficult to find that port? :/

Answer 1

Try this:

USE master
GO
xp_readerrorlog 0, 1, N'Server is listening on' 
GO

http://www.mssqltips.com/sqlservertip/2495/identify-sql-server-tcp-ip-port-being-used/

Answer 2

If you can start the Sql Server Configuration Manager > SQL Server Network Configuration > Your instance > TCP/IP > Properties

Answer 3

In our enterprise I don't have access to MSSQL Server, so I can'r access the system tables.

What works for me is:

1. capture the network traffic Wireshark (run as Administrator, select Network Interface),while opening connection to server.
2. Find the ip address with ping
3. filter with ip.dst == x.x.x.x

The port is shown in the column info in the format src.port -> dst.port

Answer 4

Maybe it's not using TCP/IP

Have a look at the SQL Server Configuration Manager to see what protocols it's using.

Answer 5

This is the one that works for me:

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

Answer 6

This is another script that I use:

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