How to Identify port number of SQL server

Question

I Install SQL server in my system and I have to check on which port number SQL is working in my system

http://stackoverflow.com/questions/12297475/how-to-find-sql-server-running-port — Teja, Oct 18 '13 at 13:58
Duplicate of: http://stackoverflow.com/questions/12297475/how-to-find-sql-server-running-port — Brian, Oct 18 '13 at 13:59
Does this answer your question? [How to find SQL Server running port?](https://stackoverflow.com/questions/12297475/how-to-find-sql-server-running-port) — Alexei - check Codidact, May 22 '23 at 13:09

Punter015 · Answer 1 · 2013-10-23T14:53:28.910

95

Open SQL Server Management Studio
Connect to the database engine for which you need the port number
Run the below query against the database

select distinct local_net_address, local_tcp_port from sys.dm_exec_connections where local_net_address is not null

The above query shows the local IP as well as the listening Port number

edited Oct 23 '13 at 14:53

answered Oct 18 '13 at 14:51

Punter015

1,718
10
13

This query can also be executed in `sqlcmd`. – Ozair Kafray Dec 11 '14 at 04:58
24

Didn't return any rows. – trilawney Aug 03 '16 at 13:07
3

@trilawney If you are connecting to the database locally it won't return any rows. You will have to connect to the database remotely. When you connect locally it uses shared memory to connect, when you connect remotely it connects through TCP. – Punter015 Aug 04 '16 at 07:30
1

Thanks Punter. That was helpful. I figured it out using netstat -abn – trilawney Aug 04 '16 at 12:40
@Punter015.. my sys is not having this table. Does it have any other name as well. M getting error with the query: "mysql> select distinct local_net_address, local_tcp_port from sys.dm_exec_connections where local_net_address is not null; ERROR 1146 (42S02): Table 'sys.dm_exec_connections' doesn't exist" – Yashi Srivastava Aug 14 '18 at 06:19
1

@YashiSrivastava, This query is for Microsoft SQL Server. You can use "SHOW VARIABLES WHERE Variable_name = 'port'" for MySQL. For more information on show variables refer, https://dev.mysql.com/doc/refman/8.0/en/show-variables.html – Punter015 Aug 14 '18 at 08:39
This is the chicken! We couldn't work out why we couldn't connect to a co-worker's SQL Server on his laptop. Turned out it's running in shared memory not TCP/IP. doh! – Becky York Jan 08 '21 at 14:20

score 41 · Answer 2 · edited Aug 18 '20 at 17:04

41

Open Run in your system.
Type %windir%\System32\cliconfg.exe
Click on ok button then check that the "TCP/IP Network Protocol Default Value Setup" pop-up is open.
Highlight TCP/IP under the Enabled protocols window.
Click the Properties button.
Enter the new port number, then click OK.

enter image description here

edited Aug 18 '20 at 17:04

plr108

1,201
11
16

answered Oct 18 '13 at 14:00

Ankit jain

4,198
4
19
24

This caused my SQL Server Management Studio to malfunction (due to a null number bug in Visual Studio 2010). It was able to connect and list tables, as well as open an edit 200 rows view, but failed to open a new query window. – Thor Hovden Mar 29 '15 at 20:42
@Ankit Jain: Can you explain what this does? If this is changing the server config, then what are the side-effects of the said change? – Chintan Pathak Jan 31 '22 at 20:55

score 40 · Answer 3 · answered Aug 26 '15 at 10:06

40

You can also use this query

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

Source : sqlauthority blog

answered Aug 26 '15 at 10:06

Sunil Agarwal

4,097
5
44
80

This should be the answer. Worked for me. Thanks – BoundForGlory Sep 10 '21 at 19:01
No rows for me SQL Server Express 15.0.2000 – Hein du Plessis Sep 01 '22 at 06:39
This works on SQL Server Express 2019 – Martin Riddar Dec 01 '22 at 20:26

score 22 · Answer 4 · answered Oct 18 '13 at 14:02

22

Visually you can open "SQL Server Configuration Manager" and check properties of "Network Configuration":

SQL Server Configuration

answered Oct 18 '13 at 14:02

Yuriy Galanter

38,833
15
69
136

What does Enabled = No mean? I have values defined from IP1 to IP8, but for all of them Enabled = No is set. – Ozair Kafray Dec 10 '14 at 12:18

score 8 · Answer 5 · edited Jun 12 '17 at 10:53

8

This query works for me:

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

edited Jun 12 '17 at 10:53

Muhammad Muazzam

2,810
6
33
62

answered Jun 12 '17 at 09:43

Unsal Aslan

101
1
5

1

just blank for me – greendino Sep 15 '22 at 07:45

score 5 · Answer 6 · answered Oct 18 '13 at 13:58

5

To check all the applications listening on all ports, there is command:

netstat -ntpl

answered Oct 18 '13 at 13:58

Paco

4,520
3
29
53

5

On Windows Server 2008 and Windows 7 I need to use `netstat -ap TCP` to display listening (and connected) TCP ports – Paul B. Apr 09 '14 at 07:37
This is the only way I could find to get the port number from the client side, without the ability to RDP into the server, or run administrative stored procs. Thanks so much! – EverPresent May 10 '17 at 19:27

score 5 · Answer 7 · answered Jul 18 '19 at 12:41

PowerShell solution that shows all of the instances on the host as well as their incoming traffic addresses. The second bit might be helpful if all you know is the DNS:

ForEach ($SQL_Proc in Get-Process | Select-Object -Property ProcessName, Id | Where-Object {$_.ProcessName -like "*SQL*"})
{
    Get-NetTCPConnection | `
     Where-Object {$_.OwningProcess -eq $SQL_Proc.id} | `
      Select-Object -Property `
                                @{Label ="Process_Name";e={$SQL_Proc.ProcessName}}, `
                                @{Label ="Local_Address";e={$_.LocalAddress + ":" + $_.LocalPort }},  `
                                @{Label ="Remote_Address";e={$_.RemoteAddress + ":" + $_.RemotePort}}, State | `
      Format-Table
}

This worked great. Would be nice if it could somehow include the instance name. — Sean Gough, Feb 28 '23 at 02:33

How to Identify port number of SQL server

7 Answers7

Linked