Configure SQL Server 2000 to allow remote access

Configure SQL Server 2000 to allow remote access
Suitable for faults:
1. Use the SQL Enterprise Manager to access SQL Server 2000 (because it adopts the Named Pipes method), but cannot be accessed using the ado.net method (UDP.
2. Do not allow remote access using the ado.net method.
Possible causes of faults include:
1. SQL Server 2000 has not installed the SQL Server 2000 sp3a or above, and port 1433 (UDP) has not been enabled and enabled );
2. The system firewall does not open port 1433.

Solution:
1. Check whether SQL Server 2000 is enabled and port 1433 is enabled (on the SQL2000 server)
Method:
Start --> Run --> open --> execute the "cmd" command --> execute the "netstat-Na" command.
Check whether there is any
TCP 127.0.0.1: 1433 0.0.0.0: 0 listening
TCP 192.168.123.98: 1433 0.0.0.0: 0 listening
If not, the SQL2000 port listening service is not started.
Go to step 3.
If yes, go to step 2.

2. Check the firewall settings of the machine where the SQL Server 2000 server is located and whether the SQL Server 2000 Server starts the SQL Server service.
Check whether the firewall is set to allow port 1433 communication. The simple method is to disable the firewall and try again.
Method:
Run the following command in the "command prompt" Window of the client or local machine: Telnet <SQL2000 Server IP address> 1433
If the command is successfully executed, the SQL Server server is working normally and listening for TCP/IP connection on port 1433
If the command returns an error message "unable to open the connection", it indicates that the server has not started the SQL Server service,
The TCP/IP protocol may not be enabled on the server, or the server does not listen on the default port 1433 of SQL Server.
Whether to enable the SQL Server service or listener port through the SQL2000 Network Configuration tool.

3. Check whether SQL Server 2000 has been upgraded to version 8.00.760 or later.
Method:
Run the following command in the SQL query Analyzer:
Select serverproperty ('productlevel ')
Select @ version
Select serverproperty ('productversion ')

If it is 8.00.760 or lower, you must install SQL2000 sp3a or SQL2000 SP4
SP4:
Http://www.microsoft.com/downloads/details.aspx? Familyid = 8e2dfc8d-c20e-4446-99a9-b7f0127f8bc5 & displaylang = ZH-CN

Note that you need to back up the current database file before installation for restoration when you reinstall the earlier version of SQL2000.
Click Start> Control Panel> Administrative Tools> Local Security Policies> Local Policies> Security Options
Set "install device: Unsigned Driver" to "continue by default ".
After downloading the file, run Setup. bat.
For more details, see SQL2000 SP4 readme
Http://www.microsoft.com/downloads/details.aspx? Familyid = 8e2dfc8d-c20e-4446-99a9-b7f0127f8bc5 & displaylang = ZH-CN

========================================================== ====================================
========================================================== ====================================
Four Most Common Errors in SQL Server connection (reproduced)
========================================================== ====================================
========================================================== ====================================
1. "SQL server does not exist or access is denied"
 
This is the most complex. There are many causes of errors and there are many aspects to check.
 
Generally, there are several possibilities:

1. incorrect spelling of the SQL server name or IP address
2. The server network configuration is incorrect.
3. The client network configuration is incorrect.
 
To solve this problem, we generally need to follow the steps below to find out the cause of the error step by step.
 

============== First, check the network physical connection ================
Ping <Server IP Address/Server Name>
 
If the ping <Server IP address> fails, the physical connection is faulty. In this case, Check hardware devices, such as NICs, hubs, and routers.
Another possibility is that a firewall software is installed between the client and the server. For example, the ISA Server. firewall software may block

Ping, telnet, and other responses
Therefore, when checking for connection problems, we must temporarily disable the firewall software or open all closed ports.

If the ping <Server IP address> succeeds, the ping <Server Name> fails.
It indicates that there is a problem with name resolution. At this time, check whether the DNS service is normal.
Sometimes the client and server are not in the same LAN. At this time, the server name may not be directly used to identify the server. At this time, we can

Use the hosts file for name resolution,
The specific method is:

1. Use notepad to open the hosts file (usually in c: \ winnt \ system32 \ drivers \ etc ).
Add a record corresponding to the IP address and server name, for example:
172.1610.24 myserver
 
2. You can also configure it in the SQL Server Client Network utility, which will be described in detail later.
 

============ Second, use the Telnet command to check the running status of the SQL Server server ==============
Telnet <Server IP addresses> 1433

If the command is successfully executed, you can see that the cursor keeps flashing in the upper left corner after the screen flashes. This indicates that the SQL Server is working normally and is listening.

TCP/IP connection to port 1433
If the command returns an error message "unable to open the connection", it indicates that the server has not started the SQL Server service,
The TCP/IP protocol may not be enabled on the server, or the server does not listen on the default port 1433 of SQL Server.

Then, we need to check the network configuration on the server and whether the named pipe is enabled. whether the TCP/IP protocol is enabled

And so on
SQL Server's built-in server network tools can be used for inspection.
Click: program -- Microsoft SQL Server -- server network usage Tool
 
After you open the tool, you can see which protocols are enabled on the server in "General.
In general, we enable Named Pipes and TCP/IP protocols.
Click "properties" in the TCP/IP protocol to check the default port settings of the sqk Server service.
In general, we use the default port 1433 of SQL server. If "Hide server" is selected, it means that the client cannot see it by enumerating the server.

This server plays a protective role, but does not affect the connection.

============== Next we will go to the client to check the network configuration of the client ================
We can also use the client network tools provided by SQL Server to perform checks,
The difference is that this tool is run on the client this time.
 
Click: program -- Microsoft SQL Server -- client network usage Tool

After the tool is enabled, You can see which protocols are enabled on the client in the "General" item.
In general, we also need to enable the named pipe and TCP/IP protocol.
Click TCP/IP protocol and select "properties" to check the default connection port settings of the client. The port must be consistent with the server.
Click the "alias" tab and configure the alias for the server. The server alias is the name used for connection,
The server in the connection parameter is the real server name. The two can be the same or different. The alias settings are similar to those in the hosts file.

Through the above checks, the first error can be basically ruled out.

-----------------------------------------------------------------------------

2. "unable to connect to the server, user XXX Login Failed"
 
This error occurs because SQL server uses the "Windows only" authentication method,
Therefore, you cannot connect to the SQL Server login account (such as SA). The solution is as follows:
 
1. Use the enterprise manager on the server side and select "use Windows Authentication" to connect to SQL Server
In Enterprise Manager
-- Right-click your server instance (the one with the green icon)
-- Edit SQL Server Registration attributes
-- Select "use Windows Authentication"
 
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.
In the preceding solution, if you fail to connect to SQL server by using "use Windows Authentication" in step 1,
Modify the Registry to solve this problem:

1. Click "start"-"run", enter regedit, and press enter to enter the Registry Editor.
2. Expand the registry key in sequence and browse to the following registry key:
[HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ MSSQLServer]
3. Find the name "loginmode" on the right of the screen and double-click to edit the double-byte value.
4. Change the original value from 1 to 2 and click "OK"
5. Disable Registry Editor
6. Restart the SQL Server service.
In this case, you can successfully use SA to create an SQL Server registration in the Enterprise Manager,
However, you still cannot connect to SQL Server in Windows Authentication mode.
This is because there are two default logon accounts in SQL Server:
Builtin \ Administrators
<Machine Name> \ Administrator deleted.
To restore these two accounts, you can use the following methods:
 
1. Open the Enterprise Manager, expand the server group, and then expand the server
2. Expand security, right-click logon, and click New logon"
3. In the Name box, enter builtin \ Administrators
4. On the "server role" tab, select "System Administrators"
5. Click "OK" to exit
6. Use the same method to add <machine Name> \ administrator to log on.
Note:
 
The following registry key:
HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ MSSQLServer \ loginmode
The value determines the Authentication mode that SQL server will adopt.
1. indicates that the "Windows Authentication" mode is used.
2. indicates that the hybrid mode is used (Windows Authentication and SQL Server Authentication ).

-----------------------------------------------------------------------------
3. Prompt connection timeout
 
If a third error occurs, it generally indicates that the client has found this server and can connect to it,
However, an error occurs because the connection time exceeds the allowed time.
This usually occurs when you run the enterprise manager on the Internet to register another server that is also on the Internet,
In addition, during slow connection, the above timeout error may occur. In some cases, this error may also occur due to lan network problems.
 
To solve this problem, you can modify the connection timeout settings of the client.
By default, the timeout setting for registering another SQL server through the Enterprise Manager is 4 seconds,
The query analyzer is 15 seconds (this is also the reason why there is a high possibility of errors in the Enterprise Manager ).
 
The procedure is as follows:
Settings in Enterprise Manager:
1. In Enterprise Manager, select "Tools" from the menu, and then select "options"
2. In the displayed "SQL Server Enterprise Manager properties" window, click the "advanced" tab.
3. Enter a large number in the "Login timeout (seconds)" box on the right under "connection settings", for example, 20.
Query settings in Analyzer:
Tool -- option -- connection -- set logon timeout to a large number