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

User · Answer

Try to enable the protocol by  Configuration Manger   SQL server Network Configuration   Protocols for MSSQLSERVER   properties of TCP IP

User · Answer

In our enterprise I don t have access to MSSQL Server  so I can r access the system tables   What works for me is    capture the network traffic Wireshark  run as Administrator  select Network Interface  while opening connection to server  Find the ip address with ping filter with ip dst    x x x x    The port is shown in the column info in the format src port - gt  dst port

User · Answer

SQL Server 2000     Programs      MS SQL Server      Client Network Utility      Select TCP IP then Properties   SQL Server 2005    Programs      SQL Server      SQL Server Configuration Manager      Select Protocols for MSSQLSERVER or select Client Protocols and right click on TCP IP

User · Answer

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

User · Answer

Maybe it s not using TCP IP  Have a look at the SQL Server Configuration Manager to see what protocols it s using

User · Answer

very simple   make a note of the sqlsrvr exe PID from taskmanager then run this command    netstat -ano   findstr  PID    it will show TCP and UDP connections of your SQL server  including ports  standard is 1433 for TCP and 1434 for UDP  example

User · Answer

select   from sys dm tcp listener states    More there  https   docs microsoft com en-us sql relational-databases system-dynamic-management-views sys-dm-tcp-listener-states-transact-sql view sql-server-2017

User · Answer

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

User · Answer

If you have run  netstat -a -b -n   from an elevated command prompt  and you don t see  sqlservr exe  at all then either your SQL Server service is not running or its TCP IP network library is disabled   Run SQL Server Configuration Manager  Start   All Programs   Microsoft SQL Server 2008   Configuration Tools    Navigate to SQL Server Services  In the right-hand pane look for SQL Server     Is it stopped  If so  start it   Navigate to SQL Server Network Configuration  or SQL Server Network Configuration  32-bit  as appropriate  then Protocols for   In the right-hand pane look for  TCP IP   Is it disabled  If so  enable it  then restart the SQL Server service   Note that he Instance ID will be MSSQLSERVER for the default instance   Please also note that you don t have to enable the TCP IP network library to connect a client to the service  Clients can also connect through the Shared Memory network library  if the client is on the same machine  or the Named Pipes network library

User · Answer

try once -    USE master DECLARE        portNumber   NVARCHAR 10  EXEC   xp instance regread  rootkey       HKEY LOCAL MACHINE    key           Software Microsoft Microsoft SQL Server MSSQLServer SuperSocketNetLib Tcp IpAll    value name    TcpDynamicPorts    value         portNumber OUTPUT SELECT  Port Number     portNumber GO

User · Answer

Perhaps not the best options but just another way is to read the Windows Registry in the host machine  on elevated PowerShell prompt you can do something like this   Get SQL instance s Port number using Windows Registry   instName    Get-ItemProperty  HKLM  SOFTWARE Microsoft Microsoft SQL Server   InstalledInstances 0   tcpPort    Get-ItemProperty  quot HKLM  SOFTWARE Microsoft Microsoft SQL Server  instName MSSQLServer SuperSocketNetLib Tcp quot   TcpPort Write-Host The SQL Instance     quot  instName  quot   is listening on   quot  tcpPort  quot    quot TcpPort  quot    Ensure to run this PowerShell script in the Host Server  that hosts your SQL instance   SQL Server installation   which means you have to first RDP into the SQL Server Box VM  then run this code  HTH

User · Answer

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

User · Answer

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

[sql-server] How to find SQL Server running port?

Examples related to sql-server

Examples related to sql-server-2008

Examples related to connection-string