SQL Server connection port problems

SQL Server connection port problems

Yesterday, I encountered A strange problem. Machine A and machine B are equipped with SQL Server2000 and C #. The program connects the database from A to B, however, there is always an exception in the database connected to A from B. The exception prompt is "An error occurred while establishing A connection with the server. When connecting to SQL Server 2005, it may fail if SQL Server does not allow remote connection under the default settings ".

As VS2005 was installed on both A and B, and SQL Server2005Express was used, I suspected that SQL Server2005 was A ghost and I searched the internet with abnormal information, I configured SQL Server2005 step by step according to the solution on the Internet, and the problem was not solved.

Later, we found that the exception information is sometimes misleading. The actual problem is irrelevant to SQL Server2005Express, but lies in the port settings of SQL Server2000, because the database port on machine A is not the default 1433 port, the program cannot be connected.

I also found an article about SQL Server2000 remote connection and sorted it out. I can follow the steps below to handle it:

1) check whether the Ping server ip address can be reached. This is to see whether the physical connection to the remote SQL Server 2000 Server exists. If not, check the network and check the configuration. Make sure that the IP address of the Remote SQL Server 2000 Server is correctly spelled.

2) input the Telnet server IP port in Dos or command line to check whether the port can be connected. For example, Telnet 202.114.100.100 1433. 1433 is the default listening port of SQL Server 2000 for TCP/IP. If there is a problem with this step, check the following options:

(1) check whether the remote Server has started the SQL Server 2000 Service. If not, start.

(2) check whether the TCP/IP protocol is enabled on the server, because remote connection (via the Internet) depends on this protocol. The check method is to open the Start menu on the Server> program> Microsoft SQL Server> Server network utility to check whether the enabled protocol contains the TCP/IP protocol. If not, enable it.

(3) check whether the server's TCP/IP Port is configured as port 1433. Check the TCP/IP attribute in the enabled protocol in the server network utility to make sure that the default port is 1433 and the check box of the hidden server is not checked.

In fact, it is acceptable if the default port is modified. However, when the client performs a Telnet test, the server port number must be consistent with the port number configured on the server. If the "Hide server" check box is selected, it means that the client cannot see this server by enumerating the server, which protects the server but does not affect the connection, however, the default port of TCP/IP protocol is implicitly modified to 2433, which must be changed when the client is connected.

(If the port is not 1433, set the connection string in the program, it should be similar to "server = 192.168.6.10, 2433; database = ....")

(4) If the operating system on the server has been patched with sp2, you must configure windows Firewall and open port 1433 to it, you can usually turn off the windows Firewall directly during testing (Other firewalls are also the best ).

(5) check whether the server is listening on port 1433. If the server does not listen on port 1433 of the Tcp connection, it cannot be connected. The check method is to enter "netstat-a-n" or "netstat-an" under the dos or command line of the server. check whether there are items similar to tcp 127.0.0.1 1433 listening in the result list. If not, you usually need to patch SQL Server 2000 with at least sp3.