[Common SQL server connection failure errors and solutions]

Common SQL server connection failure errors and Solutions

 

A--SQL server does not exist or access denied

The following error may occur:
A. the SQL server name or IP address is incorrectly spelled.
B. The server network configuration is incorrect.
C. Client Network Configuration Error

 

Solution:
1. Check the physical connection of the network.
Ping server name or IP address
Failed to ping the Server IP Address: indicates that the physical connection is faulty. You need to check the hardware device. You need to disable the firewall because it may block your ping command.
===" Ping server ip address successfully ping server name failed: indicates that the server name is incorrect. If the server and the client are not in the same Lan, you may not be able to directly use the server name to mark the server.

2. Use the Telnet command to check the working status of the SQL Server
Telnet Server IP 14323 (default port number)
==> If there is "cannot open connection" information, it means your SQL Server service is not enabled, or your TCP/IP protocol is not enabled, or your server does not listen on port 1433.

3. Check the network configuration of the server to see if the named pipe is enabled and whether the TCP/IP protocol is enabled.
This can be found in the Tool Manager of different versions of SQL Server, for example, 2000 is the server network use tool 2008 is the SQL Server Configuration Manager

4. Check the network configuration of the client and view the protocols used.
Generally, you need to enable named pipes;

 

 

 

B -- User SA Logon Failed

The following error may occur:
1. Your login identity is "Windows login only"
2. After you change the logon mode, you have not run sp_password after setting the account.

 

Solution to the first cause: Take 2000 as an example:
1. Use the enterprise manager on the server side and select "use Windows Authentication" to connect to SQL Server
2. Expand "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 preceding solution, if you fail to connect to SQL server by using "use Windows Authentication" in step 1, modify the Registry to solve the problem.:
1. Click "start" "Run", enter regedit, and press enter to enter the Registry Editor.
2. Expand the registry key in sequence and browse to the following registry key:
[Hkey_local_machinesoftware Microsoft MSSQLServer]
3. Find the name "loginmode" on the right of the screen and double-click to edit the double-byte value.
4. Change the original value from 1 to 2 and click "OK"
5. Disable Registry Editor
6. Restart the SQL Server service;

-- Or

Use [Master]

Go exec xp_instance_regwrite n 'HKEY _ LOCAL_MACHINE ', n' software/Microsoft/MSSQLServer', n' loginmode ', REG_DWORD, 2 go

In this case, you can successfully use SA to create an SQL Server registration in the Enterprise Manager, but you still cannot connect to SQL Server in Windows Authentication mode. This is because there are two default logon accounts in SQL Server: builtinadministrators and <machine Name> administrator. 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 logon, and click New logon ";

3. In the Name box, enter builtinadministrators;

4. On the "server role" tab, select "System Administrators ";

5. Click "OK" to exit;

6. Use the same method to add <machine Name> administrator to log on.

The following registry key

Hkey_local_machinesoftwaremicrosoftmssqlservermssqlserverloginmode

The value determines the Authentication mode that SQL server will adopt. If the value is 1, it indicates that the Windows Authentication mode is used; If the value is 2, it indicates that the Windows Authentication and SQL Server authentication are used ).

Solution for the second reason: SQL server2005 is used as an example.
1. Change the logon setting to "SQL Server" and "Windows Authentication,
The specific settings are as follows:
Manage Manager-> Windows authentication (used in windows for the first time),-> select your data server in object Resource Manager-right-click> Properties> Security> sqlserver and Windows authentication.

2. Set a user name and password for SQL Server as follows:
Manage Manager-> Windows Authentication> New query> sp_password null, 'sa123456', 'sa ', then a user with the username SA and password sa123456 is set, the next time you log on, you can use SQL Server. The user name is SA and the password is sa123456.
3: Use SSMs to run sp_password null, 'sa123456', 'sa ', and then restart the SQL statement;

 

 

C -- connection timeout

If such an error occurs, it generally indicates that the client has found this server and can be connected. However, the connection time exceeds the allowed time, leading to an error.
This situation is rare,Generally, when a user runs the enterprise manager on the Internet to register another server that is also on the Internet and has a slow connection, the above timeout error may occur.
In some cases, the LAN may cause such errors.

 

To solve this problemYou can modify the connection timeout settings of the client. By default, the timeout setting for registering another SQL server through the Enterprise Manager is 4 seconds, and the query analyzer is 15 seconds.
The procedure is as follows:
Settings in Enterprise Manager:
1. In Enterprise Manager, select "Tools" from the menu, and then select "options ";
2. In the displayed "SQL Server Enterprise Manager properties" window, click the "advanced" tab;
3. Enter a large number, such as 30, in the "Login timeout (seconds)" box on the right under "connection settings.
Query settings in Analyzer:
Click "Tools"-> "options"-> "connection"; set the logon timeout value to a large number, and change the connection timeout value to 0.

 

D-Logon Error 1069:

Symptoms:
Because the name of your computer is changed, "the service cannot be started due to logon Failure" will occur when you start again.
Solution:
1. Change the name of your computer;
2. Use the Control Panel to find the MS Server Service, change the startup account information to the information during installation, and then start the service;
3.Create a User Dedicated to starting the serviceWhen installing SQL, use this user to start SQL Server, which can avoid frequent changes to the 1069 error caused by Administrator.
Even if SQL server has been installed, you can change the service account information in the service under the Control Panel to the user who is started.

 

E-SQL Server 200518452/18456 connection Error

See http://blog.csdn.net/cxin917/archive/2010/05/02/5549935.aspx

Http://www.gishr.com/blog/u/mygis/archives/2007/116.html

 

Solution to F-SQL Server 2005 Error 233

 

G-SQL Server express 2005 cannot be accessed using the SA user

 

When installing vs2008/2005 (vs2008 installed on my machine), SQL Server express 2005 is installed by default,

By default, the remote connection and SA user login function are not enabled during installation.

Cannot Use SA User Login Solution
The solution is as follows: enter Microsoft SQL Server Management studio express and log in as a Windows verification method. Open the server Properties window (right-click the Database Server icon and choose "properties "), select "security,

Select SQL servertt and windows for server verification.

Then go to "security-login name" and set sa user attributes,
Set Password

Solution: Open "start-all programs-Microsoft SQL Server 2005-Configuration tool-SQL Server Configuration Manager", and find "SQL Server 2005 network configuration" in the pop-up window ", enable "named pipes" and "TCP/IP" under "MSSQLServer protocol", and then restart SQL Server.