blocks|key|216888|text|试试这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|216889|USE+master
GO
xp_readerrorlog+0,+1,+N'Server+is+listening+on'+
GO|code-block|syntax|javascript|216890|http://www.mssqltips.com/sqlservertip/2495/identify-sql-server-tcp-ip-port-being-used/|offset|length|216891|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|0|2E|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|S|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|T|8|@]|9|@$I|U|J|V|1|W]]|A|$]]|$1|K|3|-4|5|6|7|X|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|H]]]]

Try this:

<pre><code>USE master
GO
xp_readerrorlog 0, 1, N'Server is listening on' 
GO
</code></pre>

<a href="http://www.mssqltips.com/sqlservertip/2495/identify-sql-server-tcp-ip-port-being-used/">http://www.mssqltips.com/sqlservertip/2495/identify-sql-server-tcp-ip-port-being-used/</a>

blocks|key|214683|text|非常简单。记下任务管理器中的sqlsrvr.exe+PID，然后运行以下命令：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|214684|netstat+-ano+%7C+findstr+*PID*|code-block|syntax|javascript|214685|它将显示您SQL+server的TCP和UDP连接(包括端口)标准为1433+(+TCP+)和1434+(+UDP|214686|示例：|214687|📷|atomic|offset|length|214688|​|214689|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/8ec263ac1e91b7a54d0864cbcd19110c.png|imageAlt^0|0|0|0|0|0|1|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|12|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|13|8|@]|9|@]|A|$]]|$1|K|3|L|5|M|7|14|8|@]|9|@$N|15|O|16|1|17]]|A|$]]|$1|P|3|Q|5|6|7|18|8|@]|9|@]|A|$]]|$1|R|3|-4|5|6|7|19|8|@]|9|@]|A|$]]]|S|$T|$5|U|V|W|A|$X|Y|Z|-4]]]]

very simple. 
make a note of the sqlsrvr.exe PID from taskmanager
then run this command: 

<pre><code>netstat -ano | findstr *PID*
</code></pre>

it will show TCP and UDP connections of your SQL server (including ports)
standard is 1433 for TCP and 1434 for UDP

example :
 <img src="https://i.stack.imgur.com/fwcmv.png" alt="enter image description here">

blocks|key|3072284|text|这是一个对我有效的方法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3072285|SELECT+DISTINCT+
++++local_tcp_port+
FROM+sys.dm_exec_connections+
WHERE+local_tcp_port+IS+NOT+NULL+|code-block|syntax|javascript|3072286|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This is the one that works for me:

<pre><code>SELECT DISTINCT 
 local_tcp_port 
FROM sys.dm_exec_connections 
WHERE local_tcp_port IS NOT NULL 
</code></pre>

blocks|key|2859041|text|如果您可以启动Sql+Server配置管理器>+SQL+Server网络配置>您的实例>+TCP/IP+>属性|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2859042|​|2859043|📷|atomic|offset|length|2859044|2859045|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/464e8527ea0dab0ba8b73b9bafaee2f0.png|imageAlt^0|0|0|0|1|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@$G|V|H|W|1|X]]|A|$]]|$1|I|3|C|5|6|7|Y|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|K|$L|$5|M|N|O|A|$P|Q|R|-4]]]]

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

<a href="https://i.stack.imgur.com/B6Y7J.png" rel="noreferrer"><img src="https://i.stack.imgur.com/B6Y7J.png" alt="enter image description here"></a>

blocks|key|214657|text|如果您已运行"netstat+-a+-b+-n“(从提升的命令提示符)，但根本看不到"sqlservr.exe”，则说明您的SQL+Server服务未在运行或其TCP/IP网络库已禁用。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|214658|运行SQL+Server配置管理器(开始%7C所有程序%7C+Microsoft+SQL+Server+2008+%7C配置工具)。|214659|导航到SQL+Server服务。在右窗格中查找SQL+Server+()。它停了吗？如果是这样，就启动它。|214660|导航至SQL+Server网络配置(或相应的SQL+Server网络配置(32位))，然后导航至的协议。在右窗格中查找"TCP/IP“。它禁用了吗？如果是，请将其启用，然后重新启动SQL+Server服务。|214661|请注意，对于默认实例，实例ID将是MSSQLSERVER。|214662|还请注意，您不必启用TCP/IP网络库即可将客户端连接到服务。客户端还可以通过共享内存网络库(如果客户端位于同一台计算机上)或命名管道网络库进行连接。|214663|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|P|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|Q|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|R|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|S|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|M|$]]

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.

blocks|key|2858863|text|也许它没有使用TCP/IP|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2858864|查看SQL+Server配置管理器，了解它使用的是什么协议。|2858865|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Maybe it's not using TCP/IP

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

blocks|key|217017|text|这是我使用的另一个脚本：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|217018|--+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|code-block|syntax|javascript|217019|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This is another script that I use:

<pre><code>-- 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
</code></pre>

blocks|key|216980|text|在我们的企业中，我不能访问MSSQL+Server，所以我不能访问系统表。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|216981|对我有效的是：|216982|打开与服务器的连接时，|216983|捕获网络流量Wireshark+(以管理员身份运行，选择网络接口)。|ordered-list-item|offset|length|style|CODE|216984|使用ip.dst+==+x.x.x.x+|216985|通过查找ip地址|216986|端口以src.port+->+dst.port格式显示在info列中|216987|entityMap^0|0|0|0|6|9|0|2|H|0|0|3|K|S|4|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|X|8|@$I|Y|J|Z|K|L]]|9|@]|A|$]]|$1|M|3|N|5|H|7|10|8|@$I|11|J|12|K|L]]|9|@]|A|$]]|$1|O|3|P|5|6|7|13|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|14|8|@$I|15|J|16|K|L]|$I|17|J|18|K|L]]|9|@]|A|$]]|$1|S|3|-4|5|6|7|19|8|@]|9|@]|A|$]]]|T|$]]

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

What works for me is:

<ol>
<li>capture the network traffic <code>Wireshark</code> (run as Administrator, select Network Interface),while opening connection to server.</li>
<li>Find the ip address with <code>ping</code></li>
<li>filter with <code>ip.dst == x.x.x.x</code> </li>
</ol>

The port is shown in the column <code>info</code> in the format <code>src.port -&gt; dst.port</code>

blocks|key|2859103|text|尝试一次：-|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2859104|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|code-block|syntax|javascript|2859105|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

try once:- 

<pre><code>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
</code></pre>

blocks|key|2859152|text|select+*+from+sys.dm_tcp_listener_states+|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|2859153|更多信息：https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tcp-listener-states-transact-sql?view=sql-server-2017|unstyled|offset|length|2859154|entityMap|0|LINK|mutability|MUTABLE|url|https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tcp-listener-states-transact-sql?view=sql-server-2017^0|0|5|46|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|R|8|@]|9|@$G|S|H|T|1|U]]|A|$]]|$1|I|3|-4|5|F|7|V|8|@]|9|@]|A|$]]]|J|$K|$5|L|M|N|A|$O|P]]]]

<pre><code>select * from sys.dm_tcp_listener_states 
</code></pre>

More there:
<a href="https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tcp-listener-states-transact-sql?view=sql-server-2017" rel="nofollow noreferrer">https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tcp-listener-states-transact-sql?view=sql-server-2017</a>

blocks|key|214714|text|尝试通过以下方法启用协议：配置管理器>+SQL+server网络配置>Manger服务器的协议服务器>/IP服务器的属性|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|214715|entityMap^0|D|5|K|E|19|5|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]|$9|K|A|L|B|C]|$9|M|A|N|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|O|8|@]|D|@]|E|$]]]|G|$]]

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

blocks|key|214908|text|SQL+Server2000程序%7C+MS+SQL+Server+%7C客户端网络实用程序%7C选择TCP_IP，然后选择属性|type|unstyled|depth|inlineStyleRanges|entityRanges|data|214909|SQL+Server+2005程序%7C+SQL+Server+%7C+SQL+Server配置管理器%7C选择MSSQLSERVER的协议或选择客户端协议，然后右键单击TCP/IP|214910|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

blocks|key|215094|text|在PowerShell中，您可以使用此命令查看您的实例正在使用的端口：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|215095|您可以将MSSQLSERVER更改为您自己的实例名称。|offset|length|style|CODE|215096|$wmi+=+New-Object+'Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer'+localhost
$tcp+=+$wmi.ServerInstances['MSSQLSERVER'].ServerProtocols['Tcp']
$ipAll+=+$tcp.IPAddresses+%7C+where+{+$_.Name+-eq+"IPAll"+}
write-host+($ipAll.IPAddressProperties.value)|code-block|syntax|javascript|215097|entityMap^0|0|4|B|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@$D|Q|E|R|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|S|8|@]|9|@]|A|$K|L]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

From PowerShell you can use this to see what port your instance is using:
You can change <code>MSSQLSERVER</code> to your own instance name.
<pre><code>$wmi = New-Object 'Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer' localhost
$tcp = $wmi.ServerInstances['MSSQLSERVER'].ServerProtocols['Tcp']
$ipAll = $tcp.IPAddresses | where { $_.Name -eq &quot;IPAll&quot; }
write-host ($ipAll.IPAddressProperties.value)
</code></pre>

blocks|key|3072502|text|也许不是最好的选择，但只是另一种方式是读取主机中的Windows+Registry，在提升的PowerShell提示符下，您可以这样做：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3072503|#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."|code-block|syntax|javascript|3072504|​|3072505|📷|atomic|offset|length|3072506|确保在Host+Server+(托管您的SQL实例/+SQL+Server安装)中运行此PowerShell脚本，这意味着您必须首先在SQL+Server/Box/VM中运行RDP，然后运行此代码。|3072507|HTH|3072508|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/0d7e580a87ad6fe1d0c4803214e9c036.png|imageAlt^0|0|0|0|0|1|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|12|8|@]|9|@]|A|$]]|$1|I|3|J|5|K|7|13|8|@]|9|@$L|14|M|15|1|16]]|A|$]]|$1|N|3|O|5|6|7|17|8|@]|9|@]|A|$]]|$1|P|3|Q|5|6|7|18|8|@]|9|@]|A|$]]|$1|R|3|-4|5|6|7|19|8|@]|9|@]|A|$]]]|S|$T|$5|U|V|W|A|$X|Y|Z|-4]]]]

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:
<pre><code>#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;
</code></pre>
<a href="https://i.stack.imgur.com/0rdFp.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/0rdFp.png" alt="enter image description here" /></a>
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

Yes I read this <a href="https://stackoverflow.com/questions/1518823/how-to-find-the-port-for-ms-sql-server-2008">How to find the port for MS SQL Server 2008?</a>

no luck.

<blockquote>
 telnet 1433
</blockquote>

returns connection failed, so I must specify other port.

I tried to use

<blockquote>
 netstat -abn
</blockquote>

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

Why it so difficult to find that port? :/

How to find SQL Server running port?

是的，我读了这个不走运。远程登录1433returns连接失败，所以我必须指定其他端口。我试着用 netstat -abn但我在这个列表中没有看到sqlservr.exe或类似的东西。为什么很难找到那个端口？:/

问如何找到SQL Server运行端口？
EN

回答 14

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何找到SQL Server运行端口？EN

回答 14

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何找到SQL Server运行端口？
EN