SQL Server connection failure error and resolution [2]

server| Error | Resolve through the above several aspects of the inspection, error 1 The cause of the occurrence can basically be excluded. Let's describe in detail how to troubleshoot error 2.

The error message shown in Figure 2 is often encountered when a user attempts to connect to SQL Server using SA in Query Analyzer, or when using SA to create a new SQL Server registration in Enterprise Manager. This error occurs because SQL Server uses Windows only authentication, so users cannot connect using SQL Server's logon 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 the "SQL Server group", the right mouse 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 using Windows Authentication to connect to SQL Server fails in step 1th, then we are faced with a dilemma: First, the server allows only Windows authentication, and second, it still fails to connect even with Windows authentication On the server. This situation is vividly referred to as "locking yourself out of the door" because, in any way, the user cannot use the connection. In fact, we can change the authentication mode to SQL Server and Windows mixed authentication by modifying a registry key, as shown in the following steps:

1, click "Start"-"Run", input regedit, enter the Registry Editor;

2, expand the registry keys in turn, and browse to the following registry key:

[HKEY_LOCAL_MACHINE Oftware\microsoft\mssqlserver\mssqlserver]

3, on the right side of the screen to find the name "LoginMode", double-click Edit Double byte value;

4, change the original value from 1 to 2, click "OK";

5, close Registry Editor;

6. Restart the SQL Server service.

At this point, users can successfully use SA to create new SQL Server registrations in Enterprise Manager, but still cannot use Windows Authentication mode to connect to SQL Server. This is because there are two default login accounts in SQL Server: BUILTIN\Administrators and the machine name >\administrator are deleted. To recover both accounts, you can use the following methods:

1, open Enterprise Manager, expand the server group, and then expand the server;

2, expand Security, right-click Login, and click New Login;

3, in the "Name" box, input BUILTIN\Administrators;

4. In the Server Roles tab, select System Administrators;

5, click "OK" exit;

6, use the same method to add the machine name >\administrator login.

The following registry key

HKEY_LOCAL_MACHINE Oftware\microsoft\mssqlserver\mssqlserver\loginmode

Determines what authentication mode SQL Server will take. The value is 1, which means that the Windows Authentication mode is used; The value is 2, which means mixed mode (Windows authentication and SQL Server authentication).

After watching how to solve the first two errors, let's take a look at the third error shown in Figure 3.

If you encounter a third error, it is generally stated that the client has found the server and can connect, but that 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, which may cause the above timeout error. In some cases, this error can also be caused by network problems in 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, and the Query Analyzer is 15 seconds (which is why there is a greater likelihood of errors occurring in Enterprise Manager). The specific steps are:

1, in Enterprise Manager, select the "Tools" on the menu, and then select "Options";

2, in the pop-up SQL Server Enterprise Manager Properties window, click on the "Advanced" tab;

3. Enter a larger number, such as 20, in the box to the right of login timeout (seconds) under Connection settings.

It can also be set in the same location in the Query Analyzer.

Second, application connection failed

All three of the above error messages occur in SQL Server's own client tools, and similar error messages are encountered in the application, such as:

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 ' login failed. Reason: not associated with a trusted SQL Server connection.

Microsoft OLE DB Provider for ODBC Drivers error ' 80004005 '.
[Microsoft] The [ODBC SQL Server Driver] timeout has expired.

First, let's take a look at the diagram below to see what is different about using ODBC and using OLE DB to connect to SQL Server.

　　


From the above illustration, we can see that in practice, the application creates and uses various ADO objects, and the ADO object framework invokes the OLE DB provider that is enjoyed. To access a SQL Server database, OLE DB provides two different methods: 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 OLE DB providers for SQL Server:

Using SQL Server authentication:

oConn.Open "Provider=sqloledb" & _
"Data source=myservername;" & _
"Initial catalog=mydatabasename;" & _
"User id=myusername;" & _
"Password=mypassword"

To use Windows authentication (trusted connection):

oConn.Open "Provider=sqloledb" & _
"Data source=myservername;" & _
"Initial catalog=mydatabasename;" & _
"Integrated SECURITY=SSPI"

2. Use an OLE DB provider for ODBC (without using an ODBC data source):

Using SQL Server authentication:

oConn.Open "Driver={sql Server};" & _
"Server=myservername;" & _
"Database=mydatabasename;" & _
"Uid=myusername;" & _
"Pwd=mypassword"

To use Windows authentication (trusted connection):

oConn.Open "Driver={sql Server};" & _
"Server=myservername;" & _
"Database=mydatabasename;" & _
"Trusted_connection=yes"

3. Use an OLE DB provider for ODBC (using an ODBC data source):

oConn.Open "Dsn=mysystemdsn" & _
"Uid=myusername;" & _
"Pwd=mypassword"

If you encounter a failure of the connection, we simply follow the method shown in one, combining the connection string in the program to check, basically can be solved. In addition, there are several places to note:

1, when configuring an ODBC data source, click on the "Client" configuration option allows us to specify the network library, port number and other properties used in the connection, as shown in the following figure:

　　



2. If you encounter a connection timeout error, we can modify the timeout setting of the Connection object in the program, and then open the connection. For example:



<%
Set Conn = Server.CreateObject ("ADODB. Connection ")
Dsntest= "Driver={sql Server}; Server=servername; Uid=user; Pwd=password;database=mydatabase "
Conn. Properties ("Connect Timeout") = 15 ' in seconds
Conn.Open DSNtest
%>


3. If you encounter a query timeout error, we can modify the Recordset object time-out settings in the program, and then open the result set. For example:



Dim cn as New ADODB. Connection
Dim rs as ADODB. Recordset
. . .
CMD1 = Txtquery.text
Set rs = New ADODB. Recordset
Rs. Properties ("Command Time Out") = 300
' Same in seconds, if set to 0 means unrestricted
Rs. Open Cmd1, CN
Rs. MoveFirst
. . .


Third, summary

This article focuses on how to troubleshoot and troubleshoot connection failures in both cases of using SQL Server client tools and user-developed applications for the common failure of connections that most users use during SQL Server. After reading this article, I believe that every reader will have a more comprehensive and in-depth connection to how SQL Server works, how to authenticate, and how to develop applications. All of the tests or samples in this article are passed on Windows Advanced server + SQL Server 2000 Enterprise Edition.