Analysis and elimination of failure error in SQL Server connection failure

server| Error

A recent project (Asp.net+sql Server 2000) had no problems running on the machine that was originally developed. But when I debug a program (native debugging) on another machine, there is always "SQL Server does not exist or access is denied". believe that in any one search website input such search words, will certainly get more than n pages.
A summary of the four most common error resolution scenarios for SQL Server connections is as follows:
One. " SQL Server does not exist or access is denied "

This is the most complex, the cause of the error is more, need to check the aspect also more.

Generally speaking, there are several possibilities:

1,sql server name or IP address is spelled incorrectly
2, server-side network configuration is incorrect
3, the client network configuration is incorrect

To solve this problem, we generally 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 ping < server IP address > is unsuccessful, indicating that there is a problem with the physical connection, check hardware devices such as NIC, HUB, router, etc.
Another possibility is that the firewall software is installed between the client and the server, such as ISA server. Firewall software may mask responses to ping,telnet, etc.
So when checking connectivity problems, we have to temporarily shut down the firewall software, or open all the blocked ports.

Ping < server name > failed if ping < server IP address > success
If there is a problem with name resolution, check to see if the DNS service is normal.
Sometimes the client and the server are not in the same LAN, it is very likely that the server name can not be used directly to identify the server, at this time we could use the Hosts file for name resolution,
The specific methods are:

1. Use Notepad to open the Hosts file (usually located in C:\WINNT\system32\drivers\etc).
Add a corresponding record for the IP address and server name, such as:
172.168.10.24 MyServer

2. Or configure in SQL Server's client Network Utility, which is explained in detail later.

============= second, use the Telnet command to check the working status of the SQL Server server =============
Telnet < server IP address > 1433

If the command succeeds, you can see the cursor flashing in the upper-left corner after the screen flashes, indicating that the SQL Server server is working and listening for TCP/IP connections on port 1433
If the command returns an error message "Cannot open connection", the server side does not start the SQL Server service.
It is also possible that the TCP/IP protocol is not enabled on the server side, or the server side is not listening on SQL Server's default port 1433.

============= Next, we check the server-side network configuration on the server to check if named Pipes are enabled. TCP/IP protocol enabled and so on =============
You can use SQL Server's own server network usage tools for checking.

Click: Program-Microsoft SQL Server-server network usage tool

After you open the tool, you can see in general what protocols are enabled by the server.
Generally speaking, we enable named Pipes and TCP/IP protocols.
Point in the TCP/IP protocol, select "Properties", we can check the SQK Server service default port settings
In general, we use SQL Server's default 1433 port. If "Hide Server" is selected, it means that the client cannot see the server by enumerating the servers, and has a protective effect, but does not affect the connection.

============= Next we are going to the client to check the client's network configuration =============
We can also take advantage of SQL Server's own client network usage tools for checking,
The difference is that this is the client to run this tool.

Click: Program--Microsoft SQL Server--Client Network usage tool

After you open the tool, in the general item, you can see which protocols are enabled by the client.
In general, we also need to enable named Pipes and TCP/IP protocols.
Click the TCP/IP protocol and select Properties to check the client default connection port settings, which must be consistent with the server.

Click the Aliases tab, and you can also configure aliases for the server. The alias of the server is the name used to connect,
The server in the connection parameter is the true server name, and the two can be the same or different. The alias settings are similar to those used in the Hosts file.

Through the above several aspects of the inspection, basically can eliminate the first error.

Two. " Unable to connect to server, user XXX login failed.

This error occurs because SQL Server uses the Windows only authentication method,
Therefore, users cannot connect using SQL Server's login account (such as SA). The workaround is as follows:

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 on 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 above workaround, if you failed to connect to SQL Server using Windows authentication in step 1th,
Then fix the problem by modifying the registry:

1. Click "Start"-"Run", input regedit, enter Registry Editor
2. Expand the registry key sequentially and browse to the following registry keys:
[HKEY_LOCAL_MACHINE\Software\Microsoft\MSSqlserver\MSSqlServer]
3. Locate the name "LoginMode" on the right side of the screen, double-click to edit the double byte value
4. Change the original value from 1 to 2 and click "OK"
5. Close Registry Editor
6. Restart the SQL Server service.

At this point, the user can successfully use SA to create a new SQL Server registration in Enterprise Manager.
However, you still cannot use Windows Authentication mode to connect to SQL Server.
This is because there are two default logon accounts in SQL Server:
BUILTIN\Administrators
< machine name >\administrator was deleted.
To recover both 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 Login, and then click New Login
3. In the Name box, enter BUILTIN\Administrators
4. On the Server Roles tab, select System Administrators
5. Click "OK" to exit
6. Use the same method to add < machine name >\administrator login.

Description

The following registry key:
Hkey_local_machine\software\microsoft\mssqlserver\mssqlserver\loginmode
Determines what authentication mode SQL Server will take.
1. Means using Windows authentication mode
2. Indicates the use of mixed mode (Windows authentication and SQL Server authentication).

Three. Prompt connection timeout

If you encounter a third error, it generally means that the client has found the server and can connect,
However, an error occurs because the connection time is greater than the allowed time.
This situation typically occurs when a user runs Enterprise Manager on the Internet to register another server that is also on the Internet.
And is a slow connection, it is possible to cause the above timeout error. In some cases, this error can be caused by network problems on the LAN.

To resolve such an error, you can modify the client's connection timeout setting.
By default, the timeout setting for registering another SQL Server through Enterprise Manager is 4 seconds,
The Query Analyzer is 15 seconds (which is why there is a greater likelihood of errors occurring in Enterprise Manager).

The specific steps are:
Settings in Enterprise Manager:
1. In Enterprise Manager, select Tools on the menu, and then select options
2. In the SQL Server Enterprise Manager Properties window that pops up, click the Advanced tab
3. Enter a larger number, such as 20, in the box to the right of login timeout (seconds) under Connection settings.

Settings in Query Analyzer:
Tools--Options--connection--Sets the login timeout to a larger number

Four. Most of the machines use TCP/IP to succeed, once I found that with named pipes can?

This is because in the operating system after Windows 2000, MS configures TCP/IP to address SQL Server security issues
For SQL Server's default connection agreement, you can see TCP/IP and name PIPE in the Client Network Utility
The order.

You can also:
[Hkey_local_machine\software\microsoft\mssqlserver\client\supersocketnetlib]
"Protocolorder" =hex (7): 74,00,63,00,70,00,00,00,00,00
See the default protocol.

2. How to change Named Pipes, TCP/IP, and how to write SQL statements in a program?
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,00

Server End:
[Hkey_local_machine\software\microsoft\mssqlserver\mssqlserver\supersocketnetlib]
"Protocolorder" =hex (7): 74,00,63,00,70,00,00,00,00,00

My question should belong to the first category, unfortunately I do not follow the above mentioned method can not solve the problem. I use: Telnet < server IP address > 1433 display does not connect correctly. The way it is said does not solve the problem. Some netizens said that 1433 port can not connect because it is SQL Server 2000 did not patch the reason, but I have played after SP4 still can not properly connect 1433 ports.
My setting in the web.config is:
<configuration>
<appSettings>
<!--"Data source=sasank;initial catalog=coder;persist security info=false;user size=4096"-->
<add key= "ConnectionString" value= "server=localhost; Trusted_connection=false;user/>

</appSettings>
<system.web>
For a long time to find a reason, can not.
Later, try to change the database connection entry to:
<add key= "ConnectionString" value= "server=192.168.254.60; Trusted_connection=false;user/> is to change the server to my machine in the LAN IP address (note I am debugging, I changed the server to machine name also not), connected to run, unexpectedly succeeded! The problem is solved, but I still do not quite understand what is the reason? Why did I use the Telnet command to open port 1433 unsuccessfully? Hope someone can explain!