remote connection to SQL Server 2000 servers method _mssql

Source: Internet
Author: User
Tags microsoft sql server mssqlserver
Test condition: A public network IP, two static IP.
Specific steps:
   A, ping server IP can ping pass
Observe whether the physical connection exists for the remote SQL Server 2000 Server. If not, check to see the configuration and, of course, make sure that the remote SQL Server 2000 server has the correct IP spelling.
   Second, enter the Telnet server IP port under DOS or command line to see if it is connected
such as Telnet 202.114.100.100 1433
Typically, the port value is 1433 because 1433 is the default listening port for TCP/IP for SQL Server 2000. If there is a problem, usually this step will go wrong. The usual hint is "... Unable to open connection, connection failed.
If there is a problem with this step, you should check the following options.
1. Check to see if the remote server has started the SQL Server 2000 service. If not, start.
2. Check that the TCP/IP protocol is not enabled on the server side because remote connections (via the Internet) require this protocol. The check method is to open the Start menu on the server-> program->microsoft SQL server-> Server Network Utility to see if there is a TCP/IP protocol in the enabled Protocol, and if not, enable it.
   3. Check that the TCP/IP port for the server is configured with port 1433. Still in the Server Network Utility, view the properties of the TCP/IP enabled in the protocol, ensure that the default port is 1433, and that the Hide Server checkbox is not checked.
In fact, if the default port is modified, it is OK, but when the client does a Telnet test, the port number of the write server must be the same as the server-configured port number. If the Hidden server checkbox is checked, it means that the client cannot see the server by enumerating the servers, and that it protects, but does not affect the connection, but the default port for the TCP/IP protocol is implicitly modified to 2433 and must be changed when the client connects.
4. If the server end of the SP2 patch, you have to configure Windows, to open 1433 ports to it, usually in the test can be directly turned off Windows Firewall (other firewalls are also turned off the best).
   5. Check to see if the server is listening on port 1433. If the server is not listening on the 1433 port of the TCP connection, it is not connected. The check method is to enter Netstat-a-N or Netstat-an below the server's DOS or command line, and see if there are any items similar to the TCP 127.0.0.1 1433 listening in the results list. If not, you typically need to have at least SP3 patches on SQL Server 2000. In fact, start Query Analyzer on the server side, enter the SELECT @ @version can see the version number, version number under 8.0.2039 needs to be patched.
If the above is all right, then you do telnet server IP 1433 test, you will see the screen after a flash of the cursor in the upper left corner flashing. Congratulations, you can start the connection in Enterprise Manager or Query Analyzer immediately.
Third, check client settings
Program->microsoft SQL Server-> Client network usage tools. As in the server Network Utility, make sure that the client-side TCP/IP protocol is enabled and that the default port is 1433 (or another port, consistent with the server side).
four, in Enterprise Manager or query that Analyzer connectivity test
Enterprise Manager-> Right key SQL Server group-> new SQL Server registration-> next-> write remote ip-> next-> Select SQL Server login-> next-> write login name and password (SA, Password)-> Next-> next-> complete
Query Analyzer-> file-> connection-> write to remote ip-> write logins and Passwords (Sa,password)-> determine
It is generally recommended to do so in Query Analyzer because by default the timeout setting for registering another SQL Server through Enterprise Manager is 4 seconds, and the Query Analyzer is 15 seconds.
to modify the default connection timeout method:
Enterprise Manager-> Tools-> options-> in the SQL Server Enterprise Manager Properties window that pops up, click the Advanced tab-> connection settings-> Enter a larger number in the box after the login timeout (seconds)
Query Analyzer-> tool-> option-> Connection-> Enter a larger number in the box after the login timeout (seconds)
You can usually connect and, if prompted incorrectly, go to the next step.
V. The cause of the error is usually because SQL Server uses Windows only authentication, so users cannot connect using SQL Server's login account, such as SA. The workaround looks like this:
1. Use Enterprise Manager on the server side and select "Use Windows Authentication" to connect to SQL Server.
2. Expand SQL Server Group, right-click the name of the SQL Server server, select Properties, and then select the Security tab.
3. Under Authentication, select SQL Server and Windows.
4. Restart the SQL Server service. (net stop MSSQLSERVER stops service at DOS or command line, net start MSSQLServer service is also a quick way).
Note: When connecting to the local server, commonly used is the named pipe protocol (which can be seen in the Server Network Utility), the default port is 445, so the local connectivity is not a problem, connecting the remote server is a completely different protocol), connect again, display connection success.
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.