How to find SQL Server running port?

A

16

118

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? :/

Amyl answered 6/9, 2012 at 9:57 Comment(1)

Did you check if the Sql Server Service is running? – Edging 6/9, 2012 at 10:3

D

199

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/

Docket answered 6/9, 2012 at 10:4 Comment(3)

did u try using master db and runnig this – Docket 6/9, 2012 at 10:24

If it returns 0 rows TCP/IP is disabled. Enabled TCP/IP in SQL Server Configuration Manager. – Luteal 30/3, 2016 at 14:17

Just to add - in recent versions it seems TCP is disabled by default in the Developer Edition, while UDP is on. – Clunk 3/11, 2020 at 18:9

C

83

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

(this assumes you're connected with SSMS remotely over TCP, not with SSMS on the local machine with named pipes)

Ctenoid answered 5/2, 2014 at 19:14 Comment(1)

See also gist.github.com/ststeiger/5f1f8af65023bd751252c4913d9e4175 – Ctenoid 25/8, 2021 at 9:40

S

82

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 : enter image description here

Savory answered 19/6, 2013 at 10:6 Comment(1)

This worked for me "in reverse": needed to find (non-default) port number of remote machine that I was connected to in SSMS. – Floriated 25/5, 2016 at 19:44

B

61

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

Bors answered 3/8, 2015 at 15:6 Comment(0)

P

9

If you don't want to look in SQL Server Management (sqlservermanager15.msc), then run this query in the database, e.g. from sqlcmd or ssms:

SELECT * FROM [sys].[dm_tcp_listener_states]

listener_id	ip_address	is_ipv4	port	type	type_desc	state	state_desc	start_time
1	::1	False	1433	0	TSQL	0	ONLINE	2021-01-01 00:00:00.000000
2	127.0.0.1	True	1433	0	TSQL	0	ONLINE	2021-01-01 00:00:00.000000

Thanks to @vladimir-bashutin for pointing out this one. Here is another one:

SELECT [name]
  ,[protocol_desc]
  ,[type_desc]
  ,[state]
  ,[state_desc]
  ,[is_admin_endpoint]
FROM [master].[sys].[endpoints]

name	protocol_desc	type_desc	state_desc	is_admin_endpoint
TSQL Local Machine	SHARED_MEMORY	TSQL	STARTED	False
TSQL Named Pipes	NAMED_PIPES	TSQL	STARTED	False
TSQL Default TCP	TCP	TSQL	STARTED	False
TSQL Default VIA	VIA	TSQL	STARTED	False

So now you have the port and protocol. If you don't have access to these system tables, consider using an SSRP client, such as https://github.com/adzm/ssrpc.

Poetaster answered 3/9, 2021 at 16:53 Comment(0)

U

8

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.

Uncovered answered 11/3, 2013 at 2:29 Comment(0)

M

4

Maybe it's not using TCP/IP

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

Melchizedek answered 6/9, 2012 at 10:2 Comment(4)

SQL Native Client config? TCP/IP is Enabled. But default port is set to 1433 :/ – Amyl 6/9, 2012 at 10:14

@Amyl No - that's for clients. You want the server configuration utiltiy. – Melchizedek 6/9, 2012 at 10:18

where I can find that config? – Amyl 6/9, 2012 at 10:20

On the start menu - msdn.microsoft.com/en-us/library/ms174212(v=sql.100).aspx – Melchizedek 6/9, 2012 at 10:22

S

3

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

Simonton answered 6/7, 2018 at 12:8 Comment(0)

I

3

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

Incomer answered 5/9, 2018 at 17:22 Comment(0)

U

3

select * from sys.dm_tcp_listener_states

More there: https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tcp-listener-states-transact-sql?view=sql-server-2017

Uncap answered 16/10, 2018 at 9:27 Comment(2)

Please elaborate your answer. – Devinne 16/10, 2018 at 9:47

While this link may assist in your answer to the question, you can improve this answer by taking vital parts of the link and putting it into your answer, this makes sure your answer is still an answer if the link gets changed or removed :) – Swanky 6/11, 2018 at 9:38

P

2

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 -> dst.port

Paralipomena answered 9/8, 2016 at 14:23 Comment(0)

S

1

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

Sedgewick answered 29/11, 2014 at 12:22 Comment(0)

P

0

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

Pertinacious answered 8/3, 2013 at 20:37 Comment(0)

C

0

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 "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$instName\MSSQLServer\SuperSocketNetLib\Tcp").TcpPort
Write-Host The SQL Instance:  `"$instName`"  is listening on `"$tcpPort`"  "TcpPort."

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

Cockspur answered 7/4, 2020 at 18:37 Comment(0)

A

0

From PowerShell you can use this to see what port your instance is using:

You can change MSSQLSERVER to your own instance name.

$wmi = New-Object 'Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer' localhost
$tcp = $wmi.ServerInstances['MSSQLSERVER'].ServerProtocols['Tcp']
$ipAll = $tcp.IPAddresses | where { $_.Name -eq "IPAll" }
write-host ($ipAll.IPAddressProperties.value)

Alto answered 19/9, 2021 at 5:27 Comment(0)

V

0

In my case the server was remote and used a named instance. The SQL Browse Service is what will translate that into a port for the client. Just make a connection through SQL Management Studio. Perform an nslookup of the server name to obtain its IP. Then do a:

netstat -ano | findstr {ip}

Should have a few remote connections in the list all using the same port number. If the server is configured to use dynamic ports then this will change.

Viscount answered 20/12, 2022 at 13:19 Comment(0)

Recommended topics

Hot tags