Change the default port of SQL Server

Today, I encountered a problem where SQL server could not be logged on. In services, the service was unavailable. However, if you encounter a problem when starting the service, you cannot start the service. Go to Administrative Tools-> Event Viewer and check the following error (this error can be found in the SQL server log, and the directory is in C: \ Program Files \ Microsoft SQL Server \ mssql10.mssqlserver \ MSSQL \ log ):

Server TCP Provider failed to listen on ['any' <IPv4> 1433]. TCP port is already in use.

Tdssniclient initialization failed with error 0x2740, status code 0xa. reason: Unable to initialize the TCP/IP listener. only one usage of each socket address (Protocol/network address/port) is normally permitted.

You can run the netstat-ano command to view the system port usage. Port 1433 is not found to be in use after the check.

Therefore, the SQL server's TCP/IP Port is changed directly. The change method is as follows:

1. In SQL Server Configuration Manager, expandSQL SERVER network configuration, And then click on the server instance you want to configure.
2. In the right pane, double-clickTCP/IP.
3. InTCP/IP propertiesDialog box, clickIP addressesTab.
4. InTCP portBox ofIpallSection, type an available port number. For this tutorial, we will use1500.
5. ClickOKTo close the dialog box, and clickOKTo the warning that the service must be restarted.
6. In the left pane, clickSQL Server services.
7. In the right pane, right-click the instance of SQL Server, and then clickRestart. When the database engine restarts, it will listen on port 1500.

After the change, you can finally access it.

 

Appendix: detailed explanation of the netstat command

All netstat parameters and descriptions are as follows:
Displays the Protocol statistics and the current TCP/IP network connection.

Netstat [-A] [-B] [-E] [-N] [-O] [-P proto] [-R] [-S] [-V] [Interval]

-A displays all connection and listening ports.
-B: displays the information contained in creating each connection or listening port.
Executable components. Executable components are known in some cases
Has multiple independent components, and in these cases
Component sequence included in creating a connection or listening port
Displayed. In this case, the executable component name
In [] at the bottom, the top is the component it calls,
Wait until the TCP/IP part. Note this option
It may take a long time. If you do not have sufficient permissions
May fail.
-E displays Ethernet statistics. This option can be used with-S
Option combination.
-N: the address and port number are displayed in numbers.
-O displays the ID of the process associated with each connection.
-P proto shows the connection of the Protocol specified by proto; proto can be
One of the following protocols: TCP, UDP, tcpv6, or udpv6.
If used together with the-s option to display statistics by Protocol, proto can be the following protocol
One:
IP, IPv6, ICMP, ICMPv6, TCP, tcpv6, UDP, or udpv6.
-R shows the route table.
-S displays statistics by protocol. By default, the IP address,
Statistics of IPv6, ICMP, ICMPv6, TCP, tcpv6, UDP, and udpv6;
The-P option is used to specify the default subset.
-V and-B options will be included in
Create a connection or listener port for All executable Components
Component.
Interval re-displays the selected statistics.
Pause interval (in seconds ). Press Ctrl + C to stop and restart
Displays statistics. If omitted, netstat displays the current
Configuration Information (only once)