In the process of using SQL Server, the number of users encountered is that the connection failed. In general, there are two ways to connect SQL Server, one is to use the client tools that come with SQL Server, such as enterprise manager, query analyzer, transaction detector, etc .; Second, the client program developed by users. Such as an ASP script, VB program, etc., the client program is also connected to SQL Server using ODBC or OLE DB. Below, we will talk about how to resolve the connection failure on these two ways.
First, the client tool connection failed
The most common error is as follows when connecting SQL Server using SQL Server (Using Enterprise Manager as an example).
1, SQL Server does not exist or visited
ConnectionOpen (connection)
2, the user 'sa' failed. Cause: Not associated with trusted SQL Server connections.
3, timeout has expired.
Below we describe how to solve these three most common connection errors.
The first error "SQL Server does not exist or access" is often the most complicated, and there are more reasons for error, and there is more ways to check. Generally speaking, there are several possibilities:
1, the SQL Server name or IP address spelling is incorrect;
2, the server-side network configuration is incorrect;
3, client network configuration is incorrect.
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
or
PING
If ping
If the Ping
1. Use Notepad to open the HOSTS file (in general in C: / WinNT / System32 / Drivers / etc).
2, add a corresponding record of the IP address and the server name, such as:
172.168.10.24 MyServer
You can also configure it in the client network utility of SQL Server, which will be described in detail later.
Second, check the SQL Server server working status using the Telnet command:
Telnet
If the command is executed successfully, you can see the screen after flashing, the cursor does not flash in the upper left corner, which means that the SQL Server server is working properly and is listening to the TCP / IP connection of the 1433 port; if the command returns "Unable to open the connection" error Information, the server side does not start the SQL Server service, or the server-side does not enable the TCP / IP protocol, or the server side does not listen on the default port 1433 of SQL Server. Next, we have to check the server-side network configuration on the server, check whether named pipes are enabled, and the TCP / IP protocol is enabled. We can use the Server network using the SQL Server to check.
Click: Program -> Microsoft SQL Server -> Server Network Using Tools, the screen you see after opening the tool is shown below:
From here we can see which protocols have been enabled. In general, we enable naming pipes and TCP / IP protocols.
TCP / IP protocol, select "Properties", we can check the settings of the SQK Server service default port, as shown below:
In general, we use SQL Server default 1433 ports. 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.
Check the server-side network configuration, then we want to check the client's network configuration. We can also use the client network used by SQL Server to check, the difference is that this time is the client running this tool.
Click: Program -> Microsoft SQL Server -> Client Network Using Tools, the screen you see after opening the tool is shown below:
From here we 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, select Properties, you can check the settings of the client default connection port, as shown below.
This port must be consistent with the server.
Click the Alias tab, you can also configure alias for the server. The alias of the server is the name used to connect, and the server in the connection parameter is a real server name, both of which may be the same or different. As shown in the figure, we can use MyServer instead of the true server name SQL2KCN-02, and use the network library Named Pipes. The settings of the alias are similar to using the HOSTS file.
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
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
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. For example: DIM CN AS New Adodb.ConnectionDim Rs as adoDb.recordset... Cmd1 = txtQuery.textSet RS = new adod.com.recordsetrs.properties ("Command Time Out") = 300 'The same in seconds, if set to 0 Indicates unlimited RS.Open CMD1, cnrs.movefirst..................................... .. In both cases of the program, how to diagnose and resolve an error in the connection failure. 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.
