SQL Server connection failed error and solution [2]

xiaoxiao2021-03-06  65

Through the above aspects, the cause of error 1 can be substantially excluded. Below we will describe how to solve the error 2 in detail.

When the user is trying to connect SQL Server using SA in the query analyzer, or when using SA to create a SQL Server registration, the error message shown in FIG. 2 is often encountered. The reason for this error is because SQL Server uses "Windows" authentication mode, so users cannot connect to 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;

2. Expand "SQL Server Group", right-click the name of the SQL Server server, select "Properties", and select the Security tab;

3. Under "Authentication", select "SQL Server and Windows".

4. Restart SQL Server service.

In the above solution, if you use "using Windows Authentication" to connect SQL Server in step 1, we will encounter a two difficult situation: First, the server only allows Windows authentication; second, even if it is used Windows Authentication still cannot connect to the server. This situation is called "lock yourself outside the door", because the user does not use it in any way. In fact, we can change the authentication method to SQL Server and Windows hybrid verification by modifying a registry key value, steps below:

1. Click "Start" - "Run", enter regedit, enter the registry editor to enter the registry;

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, turn off the registry editor;

6, restart SQL Server service.

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, and / administrator deleted. To restore these two 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 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 / administrator login. The following registration key

HKEY_LOCAL_MACHINE / SOFTWARE / Microsoft / MSSQLServer / MSSQLServer / Loginmode

The value determines what authentication mode will be taken by SQL Server. This value is 1, indicating using a Windows authentication mode; this value is 2, indicating using a mixed mode (Windows Authentication and SQL Server Authentication).

After reading how to solve the first two wrong methods, let's take a look at the third error as shown in Figure 3.

If you encounter a third error, in general, the client has already found this server and can be connected, but the time is greater than the time allowed to cause an error. This generally occurs when the user runs the Enterprise Manager on the Internet, which may cause the above timeout error when it is slowly connected to the Internet. In some cases, due to the network problems of the LAN, such errors can also be caused.

To solve such an error, you can modify the client's connection timeout setting. By default, the timeout setting for another SQL Server through the Enterprise Manager is 4 seconds, and the query analyzer is 15 seconds (this is why the possibility of errors in the Enterprise Manager is relatively large). The specific steps are:

1. In the Enterprise Manager, select "Tools" on the menu and select Options.

2. In the Snql Server Enterprise Manager Properties window that pops up, click the Advanced tab;

3. Enter a relatively large number in the "Login Timeout (Second)" right side of "Connection Settings", such as 20.

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

Second, the three error messages above the application connection failure are in the client tool that occurred in SQL Server, and we will also encounter similar error messages 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. Cause: Not associated with trusted SQL Server connections. 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 schematic to find out what is different from using ODBC and connects SQL Server using OLE DB. The place. From the above figure, we can see that in actual use, the application creates and uses various ADO objects, the ADO object framework calls the OLE DB provider. In order to access the SQL Server database, OLE DB provides two different methods: OLE DB providers for SQL Server and OLE DB providers for ODBC.

These two different ways correspond to two different connection strings, and the standard connection string is written as follows: 1. OLE DB providers for SQL Server: Use SQL Server authentication: Oconn.open "Provider = SQLOLEDB; "& _" data source = myservername; "& _" initial catalog = mydatabaseename; "& _" user id = myusername; "& _" password = mypassword "Using Windows Authentication (Trust Connection): Oconn.open "Provider = sqloledb;" & _ "data source = myservername;" & _ "Initial catalog = myDatabaseName;" & _ "Integrated Security = SSPI" 2, using OLE DB providers for ODBC (not using ODBC data sources) : Use SQL Server authentication: Oconn.open "Driver = {SQL Server};" & _ "Server = myservername;" & _ "Database = MyDatabaseName;" & _ "uid = myusername;" & _ "pwd = mypassword" Use Windows Authentication (Trust Connection): Oconn.open "Driver = {SQL Server};" & _ "Server = myserName;" & _ "Database = MyDatabaseName;" & _ "Trusted_Connection = YES" 3, use for ODBC OLE DB provider (using ODBC data sources): Oconn.open "DSN = mysystemdsn;" & _ "uid = myusername;" & _ "pwd = mypassword" If we encounter connection failure, we will follow The method shown, the connection string in the program is checked, and can basically be resolved. In addition, there are several ways to pay attention to: 1. When configuring an ODBC data source, click on the "Client" configuration option to let us specify attributes such as the network library, port number and other attributes used, as shown below: 2, if In case of an error in the connection timeout, we can modify the timeout setting of the Connection object in the program, and then open the connection. E.g:

<% 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 unit conn.open dsntest%> 3, if you encounter a query timeout error, we can modify the timeout setting of the Recordset object in the program, and open the result set. E.g:

DIM CN As New Adodb.connectionDim Rs as adoDb.recordset... Cmd1 = txtQuery.textSet RS = New AdoDB.Recordsetrs.properties ("Command Time Out") = 300 'The same in seconds, if set to 0 Limits RS.Open CMD1, CNRS.MoveFirst..

Third, Summary This article focuses on how to diagnose and resolve incorrectly, how to diagnose and resolve the connection failure in both applications using SQL Server client tools and users developed in the use of SQL Server client tools and users . After reading this article, I believe that each reader will have a more comprehensive and in-depth connection to the connection work of SQL Server, authentication mode, and application development. All tests or examples in this article are passed on the Enterprise Edition of Windows 2000 Advanced Server SQL Server 2000.

转载请注明原文地址:https://www.9cbs.com/read-90122.html

New Post(0)