2024年1月13日发(作者：)

SQL数据库连接失败的解决办法

In the process of using SQL Server, the most problem that users

encounter is that the connection fails. Generally speaking,

there are two ways to connect to the SQL Server, one is to use

SQL built-in Server client tools, such as enterprise manager,

query analyzer, profiler; two is the use of users to develop

their own client programs, such as ASP, VB script program, the

client program and the use of ODBC or OLE DB connection SQL

Server. Next, we will talk about these two ways of connection,

specifically how to solve the problem of connection failure.

First, the client tool connection failed

When using SQL Server's own client tool (for example,

enterprise manager) to connect to SQL Server, the most common

errors are as follows:

1, SQL Server does not exist or access is denied

ConnectionOpen (Connect ())

2, user'sa'logon failed. Reason: not associated with trusted

SQL Server connection.

3, the timeout has expired.

Here we are in turn explaining how to solve the three most common

connection errors.

The first error, "SQL Server does not exist or is denied access",

is usually the most complex, the reason for the error is

relatively large, and there are more aspects to be examined.

Generally speaking, there are several possibilities:

1, the SQL Server name or IP address spelled incorrectly;

2, server-side network configuration is wrong;

3, the client network configuration is wrong.

To solve this problem, we usually follow the following steps

to find out the cause of the error step by step.

First, check the network physical connection:

Ping < server IP address >

perhaps

Ping < server name >

If Ping < server IP address > fail to indicate physical

connection problem, check hardware equipment such as network

card, HUB, router and so on. Another possibility is that

firewall software is installed between the client and the

server, such as ISA Server. Firewall software may mask the

response to Ping, Telnet, and so on, when checking connection

problems, we have to temporarily shut down firewall software,

or open all closed ports.

If Ping < server IP address > successful and Ping < server name >

failed, then the name resolve problem, at this time to check

the DNS service is normal. Sometimes the client and the server

is not in the same LAN, this is probably not directly use the

server name to identify the server, we can use the HOSTS file

to name resolution, the specific method:

1, use Notepad to open the HOSTS file (usually in

C:WINNTsystem32driversetc)

2, add a IP address and server name corresponding records, such

as:

172.168.10.24 Myserver

You can also configure it in the SQL Server client network

utility, which will be described in detail later.

Next, check the SQL Server server working state using the telnet

command:

Telnet < server IP address > 1433

If the command is executed successfully, can see the cursor in

the upper left corner of the screen flash flashing, indicating

that the SQL Server server is working, and is listening on port

1433 TCP/IP connection; if a command returns "cannot open

connection" error message, the server does not start the SQL

Server service, the server may not enable the TCP/IP protocol

server, or not in the SQL Server default port 1433 monitor.

Next, we'll check the server's network configuration on the

server, check whether the named pipe is enabled, whether the

TCP/IP protocol is enabled, and so on. We can use SQL Server

own server network to use tools to check.

Click: program > Microsoft SQL Server > server network using

tools, after opening the tool to see the picture as shown in

the following picture:

From here, we can see what protocols have been enabled by the

server. In general, we enable named pipes as well as TCP/IP

protocols.

Click on the TCP/IP protocol and select properties, so we can

check the settings for the default port of the SQL Server

service, as shown in the following figure:

In general, we use the default port 1433 of SQL Server. If the

hidden server is selected, it means that the client cannot see

the server through the enumeration server, and it has a

protective effect, but it doesn't affect the connection.

Check out the server side network configuration, and then we'll

go to the client to check the client's network configuration.

We can also use SQL Server own client network using tools to

check, the difference is that this is the client to run this

tool.

Click: program > Microsoft SQL Server > client network using

tools, after opening the tool to see the picture as shown in

the following picture:

From here, we can see which protocols are enabled by the client.

In general, we also need to enable named pipes as well as TCP/IP

protocols.

Click the TCP/IP protocol and select properties to check the

settings for the client's default connection port, as shown in

the following figure.

The port must be consistent with the server.

Click the alias tab, and you can also configure aliases for the

server. The alias of the server is the name used to connect,

and the server in the connection parameter is the real server

name, both of which can be the same or different. As shown in

the following figure, we can use Myserver instead of the real

server name sql2kcn-02, and use the network library Named Pipes.

The alias setting is similar to using the HOSTS file.

Through the above examination, the cause of error 1 can be

eliminated basically. Here's a more detailed description of how

to solve the bug 2.

When the user attempts to use SA in the query analyzer to connect

to the SQL Server,

Or use the SA in the enterprise manager to create a new SQL

Server. When you register, you'll often encounter error

information as shown in figure 2. The reason for this error is

that the SQL Server uses a "Windows only" authentication method,

so the user cannot connect with the login account of the SQL

Server (such as SA). The solution is as follows:

1, use the enterprise manager on the server side and choose to

use Windows authentication to connect to SQL Server;

2. Expand the 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 above solution, if use Windows authentication in the

first step SQL Server connection fails, then we will encounter

a dilemma: first, the server allows only the authentication of

Windows; secondly, even though the use of Windows

authentication is still not connected to the server. This

situation is visually called "locking yourself out of the door"

because no user can use the connection in any way. In fact, we

can change authentication to SQL, Server, and Windows hybrid

validation by modifying a registry key, as shown below:

1, click start, run, enter regedit, enter, and enter the

registry editor;

2, in turn expand the registry key, browse to the following

registry key:

[HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSSQLServerMSSQLServer]

3, find the name "LoginMode" on the right of the screen and

double-click edit double byte values;

4, the value will be changed from 1 to 2, click "ok";

5, close the registry editor;

6. Restart the SQL Server service.

At this point, the user can successfully use SA to create a new

SQL Server registry in the enterprise manager, but still cannot

use the Windows authentication mode to connect to SQL Server.

This is because there are two default login accounts in SQL

Server: BUILTINAdministrators and machine name

Administrator are 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 login, and then click New

login";

3, in the name box, enter BUILTINAdministrators;

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.

Following registry key

HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSSQLServerMSSQLServerLoginMode

The value determines what authentication mode the SQL Server

will take.

This value is 1, indicating the use of the Windows

authentication mode; the value is 2, indicating the use of mixed

mode (Windows authentication and SQL Server authentication).

After looking at how to solve the first two errors, let's look

at the third errors shown in figure 3.

If you encounter third errors, generally speaking, the client

has found the server and can connect, but the connection time

is greater than the allowable time to cause an error. This

usually occurs when a user runs an enterprise manager on

Internet to register another server on the same Internet, and

a slow connection may cause the timeout error above. In some

cases, such problems can also result from local network

problems.

To resolve such errors, you can modify the connection timeout

settings for the client. By default, registered another SQL

Server through the enterprise manager timeout is 4 seconds,

while the query analyzer is 15 seconds (which is why the

enterprise manager error occurs the possibility of more

reasons). Specific steps for:

1, in the enterprise manager, select the "tools" on the menu,

and then select "options"";

2, in the pop-up SQL Server enterprise manager properties

window, click the advanced tab;

3, in the connection settings under the "login timeout

(seconds)" to the right of the box, enter a larger number, such

as 20.

The query analyzer can also be set in the same location.

Two, the application connection failed

The above three error messages occur in the client tool that

comes with SQL Server, and we will encounter similar error

information in the application, for example:

Microsoft, OLE, DB, Provider, for, SQL, Server (0x80004005)

[DBNETLIB][ConnectionOpen (Connect ()).]Specified, SQL,

server, not, found.

Microsoft, OLE, DB, Provider, for, SQL, Server (0x80004005)

User'sa'logon failed. Reason: not associated with trusted SQL

Server connection.

Microsoft, OLE, DB, Provider, for, ODBC, Drivers,

error,'80004005'.

[Microsoft][ODBC SQL Server Driver] timeout has expired

First, let's take a detailed look at the following diagram to

see what makes the difference between using ODBC and using OLE

DB to connect to SQL Server.

From the figure above, we can see that in actual use, the

application creates and uses various ADO objects, and the ADO

object framework calls for the enjoyment of the OLE DB provider.

In order to access the SQL Server database, OLE DB provides two

different approaches: the OLE DB provider for SQL Server and

the OLE DB provider for ODBC. These two different methods

correspond to two different connection strings, and the

standard connection string is written as follows:

1, use the OLE DB provider for SQL Server:

Using SQL Server authentication:

oconn。打开“供应商= SQLOLEDB；”_

“数据源= myservername；”_

“初始目录= mydatabasename；”_

“用户ID = myusername；”_

“密码= mypassword”

使用Windows身份验证（信任连接）：

oconn。打开“供应商= SQLOLEDB；”_

“数据源= myservername；”_

“初始目录= mydatabasename；”_

“综合安全= SSPI”

2、使用用于ODBC的OLE DB提供者的（不使用ODBC数据源）：

使用SQL Server身份验证：

oconn。打开“司机= { SQL Server }；”_

“服务器myservername；”_

“数据库= mydatabasename；”_

“_ UID = myusername；”

“mypassword pwd=”

使用Windows身份验证（信任连接）：

oconn。打开“司机= { SQL Server }；”_

“服务器myservername；”_

“数据库= mydatabasename；”_

“trusted_connection =是的”

3、使用用于ODBC的OLE DB提供者的（使用ODBC数据源）：

oconn。打开“DSN = mysystemdsn；”_

“_ UID = myusername；”

“mypassword pwd=”

如果遇到连接失败的情况，我们只要按照一中所示的方法，结合程序中的连接字符串进行检查，基本都能得到解决另外，还有以下几个要注意的地方：

1、配置ODBC数据源时，点击”客户端”配置选项可以让我们指定连接使用的网络库、端口号等属性，如下图所示：

2、如果遇到连接超时的错误，我们可以在程序中修改连接对象的超时设置，再打开该连接。例如：

＜%

设置conn =服务器。CreateObject（“数据连接”）

dsntest =“司机= { SQL Server }；服务器%ServerName；UID =用户；pwd=密码；数据库=名称”

康涅狄格州的性质（“连接超时”）＝15”以秒为单位

dsntest

%＞

3、如果遇到查询超时的错误，我们可以在程序中修改记录集对象的超时设置，再打开结果集。例如：

昏暗的CN作为新ADODB.连接

记录集Recordset未定义呢。

。..

cmd1 = txtquery。文本

集=新的数据集。

ties（”命令的时间”）= 300

“同样以秒为单位，如果设置为0表示无限制

的。打开CMD1，CN

rst

。..

三、小结

本文针对大部分用户在使用SQL Server过程中常见的连接失败的错误，重点讨论了在使用SQL Server客户端工具以及用户开发的应用程序两种情况下，

How do I diagnose and troubleshoot connection failures?. After

reading this article, I believe that each reader will be SQL

Server connection, working principle, authentication methods,

as well as application development, such as a more

comprehensive and in-depth connection. All of the tests or

examples in this article are passed on the Windows 2000 Advanced

Server + SQL Server 2000 enterprise edition.