SQL Server-steps to check SQL connection errors

Post a translation FAQ (if I can write this level ...), This step is almost applicable to the most common SQL connection problems. FAQ: Check for SQL connection errors. Q: I have encountered many SQL connection errors. Is there a wizard to solve the connection problems? A: basically, when you cannot connect to your SQL Server database, the problem may be as follows:
1) Network Problems
2) SQL Server configuration problems
3) Firewall Problems
4) Client driver problems
5) application configuration problems
6) authentication and login steps 1: Network Problems
A stable network is necessary for remote connection. So the first thing we need to do is check the connectivity of SQL to confirm that the network we depend on is stable and workable. Run the following command:
Ping-a <your target machine> (use-4 and-6 for IPv4 and IPv6 respectively)
Ping-a <your remote IP address>
Nslookup (Please enter your local and remote machine name and IP address multiple times) If you cannot ping your target machine, it is probably because the network is faulty or the target machine is not running. It is likely that a network fault or the target machine is not running. However, the firewall may also block the ping packet because the target machine is behind the firewall. Correct DNS settings in the network are also very important for SQL connection. Incorrect DNS registration may also cause various connection problems. Step 2: SQL Server configuration problems
You need to confirm that the target SQL Server is running and listens in the appropriate protocol. You can use SQL Server Configuration Manager (SCM) to allow protocols on the Server side. For remote connections, NP and/or TCP protocols must be allowed. After you allow the Protocol in SCM, confirm to restart the SQL Server service. You can open the error log file to check whether the service is listening on the appropriate port. The location of the error Log is % ProgramFile % Microsoft SQL Server/MSSQLxx. xxx/MSSQL/Log by default. Step 3: Firewall problems for the NP protocol, please confirm that the file is shared in the firewall's exception list. Both file sharing and NP use the SMB protocol.
For TCP, You must place the TCP port listened by SQL Server in the exception list.
For SQL Browser, place UDP port 1434 in the exception list.
At the same time, you can add sqlservr.exeand sqlbrowser.exe to the exception list. However, IPSEC between machines we do not trust may cause some packets to be blocked. Note that the firewall does not cause local connections (local connections do not pass through the firewall ). Step 4: client driver problems
In this phase, you can use some to test your connection. As a confirmation, the test must be performed on the client machine. First try:
Telnet <your target machine> <TCP port>
You should be able to telnet to the TCP port of the target machine SQL Server if TCP is set to allow. If not, check again in the first three steps. Then, use OSQL, SQLCMD, and SQL Management Studio to test the SQL connection. If you do not have these tools, download SQL Express from the official Microsoft website for free. OSQL (included with SQL Server 2000) uses MDAC.
OSQL (included with SQL Server 2005 and 2008) uses SNAC ODBC.
SQLCMD (included with SQL Server 2005 and 2008) uses SNAC OLEDB.
SQL Management Studio (included with SQL Server 2005 and 2008) uses SQLClient. The command is roughly as follows:
Osql-E-SYour_target_machine \ Your_instance (for Windows verification)
Osql-Uyour_user-SYour_target_machine \ Your_instance (for SQL verification) SQLCMD can also be applied in this way. You can also use "-Stcp: Your_target_machine, Tcp_port" for TCP, "-Snp: Your_target_machine \ Your_instance" for NP, and "-Slpc: your_target_machine \ Your_instance ". You need to know that only some protocols fail or all protocols fail. At this stage, you should no longer see common ERROR messages such as ERROR 26 and ERROR 40. If you use NP and still see error 40 (Named Pipes Provider: cocould not open a connection to SQL Server), perform the following steps:
A) open a file sharing on your server
B) Run "net view \ your_target_machine" and "net use \ your_target_machine \ your_share" (you can also try ing network drives in Windows Explorer)
If you get an error in step 2, you may have an operating system/network failure, not an SQL Server-specific error. It is very likely that you did not set well in the first three steps, otherwise it may be a logon issue, so you can jump directly to Step 6. If you can successfully connect to some tools but fail to use other tools, it is likely to be a driver problem. You can also use "\ windows \ system32 \ odbcad32.exe" (embedded in Windows) to test the connection by adding a new DSN for different drivers, but this is only valid for ODBC. Step 5: Application Problems
If the previous four steps are successful but you still see an error in your application, it is likely that this is caused by connection issues in your application. Consider the following possible issues:
A) are your applications connected using the same account as step 4? If not, you may need to change a working service account to perform Step 4 testing.
B) What SQL driver does your application use?
C) What is your connection string? Is the connection string compatible with your driver? For more information, see http://www.connectionstrings.com/ 6: certification and renewal.
This may be the most complex part of the SQL connection problem. This may be related to your network, operating system, and SQL Server database. There is no simple solution for this problem, and you need to analyze it in detail. Remember:
A) if you use SQL verification, you must first allow hybrid verification. Check http://msdn.microsoft.com/en-us/library/ms188670.aspx.pdf for more information.
B) check that your Logon account has sufficient access permissions for the database you are using.
C) Check the error log for more details. If you have any questions about the above steps, please send them to the newsgroup.
For error correction, the most important thing for us is a) accurate error message B) connecting strings to other related resources
Http://support.microsoft.com/kb/888228
Http://blogs.msdn.com/ SQL _protocols/archive/2005/12/19/505372.aspx