How to find SQL Server running port?
Sql ServerSql Server-2008Connection StringSql Server Problem Overview
Yes I read this https://stackoverflow.com/questions/1518823/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? :/
Sql Server Solutions
Solution 1 - Sql Server
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/
Solution 2 - Sql Server
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 :
Solution 3 - Sql Server
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
Solution 4 - Sql Server
If you can start the Sql Server Configuration Manager > SQL Server Network Configuration > Your instance > TCP/IP > Properties
Solution 5 - Sql Server
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 (
Navigate to SQL Server Network Configuration (or SQL Server Network Configuration (32-bit) as appropriate) then Protocols for
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.
Solution 6 - Sql Server
Maybe it's not using TCP/IP
Have a look at the SQL Server Configuration Manager to see what protocols it's using.
Solution 7 - Sql Server
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
Solution 8 - Sql Server
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
Solution 9 - Sql Server
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
Solution 10 - Sql Server
select * from sys.dm_tcp_listener_states
Solution 11 - Sql Server
Try to enable the protocol by: Configuration Manger > SQL server Network Configuration > Protocols for MSSQLSERVER > properties of TCP/IP
Solution 12 - Sql Server
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 | state_desc | is_admin_endpoint |
---|---|---|---|---|---|
TSQL Local Machine | SHARED_MEMORY | TSQL | 0 | STARTED | False |
TSQL Named Pipes | NAMED_PIPES | TSQL | 0 | STARTED | False |
TSQL Default TCP | TCP | TSQL | 0 | STARTED | False |
TSQL Default VIA | VIA | TSQL | 0 | 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.
Solution 13 - Sql Server
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
Solution 14 - Sql Server
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)
Solution 15 - Sql Server
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