reference:
http://support.microsoft.com/default.aspx?scid=kb;zh-cn;827422#4
How to solve connection problems in SQL Server 2000
Important: Before starting to resolve the connection problem of SQL Server 2000, make sure that the MSSQLServer service is started on a computer running SQL Server.
The four most common errors in the SQL Server connection:
I. "SQL Server does not exist or visited"
This is the most complicated, the cause of the error occurs, and it needs to be checked.
Generally speaking, there are several possibilities:
1, SQL Server name or IP address spelling incorrectly
2, the server-side network configuration is wrong
3, client network configuration is wrong
To solve this problem, we generally follow the following steps to find out the cause of the error.
============= First, check the network physical connection =============
PING
If the PING
Another may be due to the installation of firewall software between the client and the server, such as ISA Server. Firewall software may shield a response to ping, telnet, etc.
Therefore, when checking the connection problem, we must first temporarily close the firewall software, or open all closed ports.
If ping
Then, the name resolution has a problem, at this time, check whether the DNS service is normal.
Sometimes the client and server are not in the same local area network, which is likely to use the server name directly to identify the server, and we can use the Hosts file to make a name resolution.
The specific method is:
1. Use Notepad to open the HOSTS file (in general in C: / Winnt / System32 / Drivers / ETC).
Add a corresponding record of the IP address and the server name, such as:
172.168.10.24 MyServer
2. Configure it in the client network utility of SQL Server, which will be described in detail later.
============= Secondly, use the telnet command to check the SQL Server server operating status =============
Telnet
If the command is successfully executed, you can see the screen after flashing, the cursor does not flash in the upper left corner, which means that the SQL Server server works fine and is listening to the TCP / IP connection of the 1433 port.
If the command returns an error message that cannot be opened, the server side does not start SQL Server service.
It is also possible that the server end does not enable the TCP / IP protocol, or the server side does not listen in the SQL Server default port 1433.
============= Next, we have to check the server-side network configuration on the server. Check if the named pipe is enabled. Do you have a TCP / IP protocol, etc. ======== =====
You can use the server network used by SQL Server to use tools to check.
Click: Program - Microsoft SQL Server - Server Network Tools
After opening this tool, you can see which protocols have been enabled in "General". In general, we enable named pipes and TCP / IP protocols.
TCP / IP protocol, select "Properties", we can check the settings of the default port of the SQK Server service
In general, we use SQL Server default 1433 port. If the Hidden Server is selected, it means that the client cannot see this server by enumerating the server, but does not affect the connection.
============= Next we have to check the client to check the client's network configuration =============
We can also use the client network used tools with SQL Server to check,
The difference is this time to run this tool in the client.
Click: Program - Microsoft SQL Server - Client Network Usage Tools
After opening this tool, in the General item, you can see which protocols have been enabled.
In general, we also need to enable naming pipes and TCP / IP protocols.
Click the TCP / IP protocol to select "Properties", you can check the settings of the client default connection port, which must be consistent with the server.
Click the Alias tab, you can also configure alias for the server. The alias of the server is used to connect,
The server in the connection parameter is a real server name, both of which may be the same or different. The settings of the alias are similar to those using the HOSTS file.
Through the above aspects, the first error can be substantially excluded.
-------------------------------------------------- ---------------------------
II. "Unable to connect to the server, user XXX landing failed"
The reason for this error is because SQL Server uses "Windows" authentication mode,
Therefore, users cannot connect using SQL Server login accounts (such as SA). The solution is as follows:
1. Use the Enterprise Manager on the server side and select "Use Windows Authentication" to connect SQL Server
Steps:
In the enterprise manager
- Right click on your server instance (that is, the one has a green icon)
- Edit SQL Server Registration Properties
- Select "Using Windows Authentication"
- Select "Use SQL Server Authentication"
- Login Name Enter: SA, password Enter SA password
--determine
2. Set the SQL Server as login
Steps:
In the enterprise manager
- Expand "SQL Server Group", right-click the name of the SQL Server server
- Select "Properties"
- Select "Security" tab
- Under "Authentication", select SQL Server and Windows.
- OK, and restart the SQL Server service.
In the above solution, if you use "using Windows Authentication" in step 1, connect SQL Server failed,
Then resolve this issue by modifying the registry:
1. Click "Start" - "Run", enter regedit, enter the registry editor
2. Expand the registry key in turn, browse to the following registry key:
[HKEY_LOCAL_MACHINE / SOFTWARE / Microsoft / MSSQLServer / MSSQLServer]
3. Find the name "Loginmode" on the right side, double-click Edit Double-byte value
4. Change the original value from 1 to 2, click "OK"
5. Close the Registry Editor
6. Restart SQL Server services.
At this point, the user can successfully use SA to create new SQL Server registration in the Enterprise Manager.
But still unable to connect SQL Server using Windows authentication mode. This is because there are two default login accounts in SQL Server:
Builtin / Administrators
To restore these two accounts, you can use the following methods:
1. Open Enterprise Manager, expand the server group, and expand the server
2. Expand "Security", right-click "Login", then click New Login "
3. In the Name box, enter Builtin / Administrators.
4. In the Server Role tab, select "System Administrators"
5. Click "OK" to exit
6. Use the same method to add
Description:
The following registration key:
HKEY_LOCAL_MACHINE / SOFTWARE / Microsoft / MSSQLServer / MSSQLServer / Loginmode
The value determines what authentication mode will be taken by SQL Server.
1. Represents the "Windows Authentication" mode
2. Represents using a mixed mode (Windows Authentication and SQL Server Authentication).
-------------------------------------------------- ---------------------------
3. Tip connection timeout
If you encounter a third error, in general, the client has found this server and can be connected.
However, it is wrong because the time of connection is greater than the allowable time.
This situation generally occurs when the user runs the enterprise manager on the Internet, and register another server on the Internet.
And when it is slowly connected, it is possible to cause the above timeout error. Some cases, such errors can also cause such errors due to the network problems of the local area network.
To solve such an error, you can modify the client's connection timeout setting.
By default, the timeout setting for another SQL Server is registered by the Enterprise Manager is 4 seconds.
The query analyzer is 15 seconds (this is why the possibility of errors in the Enterprise Manager is relatively large).
The specific steps are:
Settings in Enterprise Manager:
1. In the Enterprise Manager, select "Tools" on the menu, then select Options.
2. In the "SQL Server Enterprise Manager Properties" window that pops up, click the Advanced tab.
3. Enter a relatively large number in the box under "Login Timeout (Seconds)" on the "Connection Settings", such as 20.
Query the settings in the analyzer:
Tools - Options - Connections - Set the login timeout to a large number
-------------------------------------------------- -------------------------------
IV. Most of the machines use TCP / IP to succeed, once I found named pipes?
Reply to: Leimin (Huangshan Guangmingding)
This is because in the operating system after Windows 2000, MS will configure TCP / IP configuration to solve the security problem of SQL Server.
For SQL Server's default connection protocol, you can see TCP / IP and Name Pipes in the Client Network Utility
Order.
You can also:
[HKEY_LOCAL_MACHINE / SOFTWARE / Microsoft / MSSQLServer / Client / SuperSocketNetLib]
"Protocolorder" = HEX (7): 74,00, 63,00, 70,00, 00, 00, 00
See the default protocol.
2. How do you change Named Pipes, TCP / IP in the program, how to write it with SQL statement? You can modify the location of the registry mentioned above:
Client side:
[HKEY_LOCAL_MACHINE / SOFTWARE / Microsoft / MSSQLServer / Client / SuperSocketNetLib]
"Protocolorder" = HEX (7): 74,00, 63,00, 70,00, 00, 00, 00
Server end:
[HKEY_LOCAL_MACHINE / SOFTWARE / Microsoft / MSSQLServer / MSSQLServer / SuperSocketNetlib]
"Protocolorder" = HEX (7): 74,00, 63,00, 70,00, 00, 00, 00
