To view the port number of SQL Server

Background

These days you want to write a database connection test program that uses Java to connect to SQL Server. However, after viewing the database connection character format, you find the port number that requires the SQL Server database service. In the installation of SQL Server also did not mention the port number of the problem, the previous installation of MySQL had seen 3306 this port number, the installation of Oracle 1521 this port number is not seen. However, Oracle connects with 1521, such as Oracle's database connection string format: Jdbc:oracle:thin: @localhost: 1521:sid. It is not known whether installing two Oracle databases is still 1521 of this port number.

Viewing through stored procedures

Gossip Morte, we explain directly how to view the port number. What needs to be mentioned is that the sqlserver2008 and sqlserver2012 two versions of the database are installed on my machine. We first open the DB instance that SQL Server Management Studio (SSMS) connects to sqlserver2008, and then execute the following stored procedure:

-- Query Port number exec 0 1 ' Listening '

The results of the query are as follows:

From what we can see Sqlserver2008 's port number is 5419.

Then close SSMs, reopen it, and then connect sqlserver2012. Continue with the stored procedure described above, as shown in the query results:

Note that the port number for sqlserver2012 is 5413.

Through SQL Server Configuration Manager (SSCM)

First open the SSCM as shown in:

Then open the TCP/IP protocol in the SQL Server network configuration, as shown in:

Here we are going to turn on the TCP/IP protocol in the SQLExpress and SQL2012 two protocols.

Then look at the properties of the TCP/IP protocol as follows:

As shown, we see in Ipall that the value of "TCP Dynamic port" is 5419, which is the port number we sqlserver2008. We opened the SQL2012 protocol view and found the Ipall attribute as follows:

The port number of the sql2012 is 5413, which is exactly the same as the port number we found with the stored procedure.

View SQL Server listening protocols and ports via Sp_readerrorlog (supplemental: 2012-9-10)

Enter the Sp_readerrorlog command in SSMS to view the SQL Server information and return to the following similar listener information:

--TCP ProtocolServer isListening on [' any ' <ipv6>]. Server isListening on [' any ' <ipv4>]. Server isListening on [' any ' <ipv6> 1433]. Server isListening on [' any ' <ipv4> 1433]. Server isListening on [:: 1 <ipv6> 1434]. Server isListening on [127.0.0.1 <ipv4> 1434].--named pipe protocol cannot be closed, even if it is closed, there will be local connection providerServer Local connection Provider isReady toAccept Connection on [\\.\pipe\sql\query].--Share MemoryServer Local connection Provider isReady toAccept Connection on [\\.\pipe\sqllocal\mssqlserver].

This is in the case of closing the named pipe, if the named pipe is turned on, then it will be the following type:

-- Open Named pipe Pipe  is  to  on [].

in SQL Server, the local named pipe protocol cannot be closed .

***********************************************************************************************
* "Author": Xwdreamer
* "Date": June 12, 2014
* "URL": http://www.cnblogs.com/xwdreamer/archive/2012/06/23/ 2559344.html
* "Notice":
* 2, please respect the original results, reproduced and quoted please specify the author and source.
* 3, this article must be reproduced and quoted in full text, any organization and individuals are not authorized to modify any content, and is not authorized to be used for business.
* 4, this statement is part of the article, reprint and reference must be included in the original text.
***********************************************************************************************