Ways to remotely connect to a SQL Server server

If you need to remotely connect to a SQL Server server, what should you do? The following will teach you the solution for remote connection to the SQL Server server for your reference.

A look at ping server IP can ping through.

This actually looks at whether the physical connection to the remote SQL Server 2000 Server exists. If not, check the network 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-side operating system has SP2 patches, the Windows Firewall must be configured, 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 the server's DOS or command line below

Netstat-a-N or Netstat-an, in the results list, see if there are any items that resemble the TCP 127.0.0.1 1433 listening. 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.

Three 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 connection 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.

Five errors are typically caused by SQL Server using 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 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 a local server, it is common to use the named pipe protocol (which is visible in the Server Network Utility for the enabled protocol), the default port is 445, so it is not a problem to connect to a remote server, which is a completely different protocol.