Basically, when you were unable to connect to your SQL Server, the problem could be:
- Network problem
- SQL Server configuration issue.
- Problem with firewall
- The problem with the client driver
- Application configuration error.
- Authentication and login issue.
Step 1: network problem
You may be able to make a local connection without a working network, but this is a special case. A remote network requires a stable network. The first thing that bothers SQL connectivity issues is to make sure that the network we rely on is functional and stable. Run the following commands:
ping -a (use -4 and -6 for IPv4 and IPv6) ping -a nslookup (enter the local and remote computer name and IP address several times)
Be careful to see the discrepancy of the returned results. If you cannot program your target machine, it is more likely that either the network is broken or the target computer is not running. Perhaps the target machine is behind the firewall, and the firewall is blocking packets sent by ping. Windows Firewall does not block the ping packet (ECHO) by default. The correct DNS configuration on the network is vital for the SQL connection. An incorrect DNS record can lead to communication problems. See this link, for example, the error message "Unable to create SSPI message", Reflected DNS.
Step 2: SQL Server configuration issue
You need to make sure that the target SQL Server is up and listening on the appropriate protocols. You can use SQL Server Configuration Manager (SCM) to enable protocols on the server machine. SQL Server supports shared memory, named pipes, and TCP (and VIAs, which require special hardware and are rarely used). For remote connection, NP and / or TCP protocols must be enabled. After enabling protocols in SCM, be sure to restart SQL Server.
You can open the errorlog file to see if the server is listening on a protocol successfully. The location of the errorlog file is usually located under:% ProgramFile% Microsoft SQL Server / MSSQLxx.xxx / MSSQL / Log If the target SQL instance is a named instance, you also need to make sure that the SQL browser is running on the target computer. If you cannot access the remote SQL Server, contact your administrator to make sure that this is all happening.
Step 3: problem with the firewall
The firewall on the SQL Server machine (or somewhere between the client and the server) can block the SQL connection request. An easy way to isolate if this is a firewall problem is to disable the firewall for a short time if you can. The long-term solution is an exception for SQL Server and SQL Browser.
For the NP protocol, verify that file sharing is listed on the firewall exception list. Both file sharing and NP use the SMB protocol under it. For the TCP protocol, you need to put the TCP port on which SQL Server is listening on an exception. For SQL Browser, set UDP port 1434 as an exception. Meanwhile, you can also add sqlservr.exe and sqlbrowser.exe to the exception, but this is not recommended. IPSec between machines that we don’t trust can also block some packets. Note that a firewall should never be a problem for local connections.
Step 4: Problem with the client driver
At this point, you can test your connection with some tools. Tests must be performed on the client machine.
First attempt: telnet You should be able to telnet to the TCP port of the SQL server if TCP is enabled. Otherwise, return to steps 1-3. Then use SQL Server, SQLCMD and SQL Management Studio to test sql connections. If you do not have these tools, download SQL Express from Microsoft and you can get these tools for free.
OSQL (the one that ships with SQL Server 2000) uses MDAC. OSQL (the one that ships with SQL Server 2005 and 2008) uses SNAC ODBC. SQLCMD (shipped with SQL Server 2005 and 2008) uses SNAC OLEDB. SQL Management Studio (shipped with SQL Server 2005 and 2008) uses SQLClient.
Using the Possilbe Command: osql -E -SYour_target_machine \ Your_instance for Windows Auth osql -Uyour_user -SYour_target_machine \ Your_instance for SQL Auth
Also applies SQLCMD. Alternatively, you can use "-Stcp: Your_target_machine, Tcp_port" for TCP, "-Snp: Your_target_machine \ Your_instance" for NP, and "-Slpc: Your_target_machine \ Your_instance" for shared memory. You would know if it is not suitable for all protocols or only some specific prokotory.
At this point, you should no longer see a general error message, such as error 26 and error 40. If you are using NP and you still see error 40 (Named Pipes provider: Could not open a connection to SQL Server), try following steps: a) Share the file on the server. b) Run "net view \ your_target_machine" and "net use \ your_target_machine \ your_share" (you can also try using the network adapter from Windows Explorer) If you get a failure in b), it is very likely that you have a problem with OS configuration / a network that is not specific to SQL Server. Please search the Internet first to resolve this issue.
You can try connecting using Windows authentication and SQL authentication. If the tests with all the tools failed, there is a good chance that steps 1-3 were not installed correctly, unless the failure is related to connecting to the system, you can look at step 6.
If you succeed with some tools, but not with other tools, this is probably a driver problem. You can ask a question on our forum and tell us the details.
You can also use "\ windows \ system32 \ odbcad32.exe" (which comes with Windows) to test the connection by adding a new DSN for various drivers, but only for ODBC.
Step 5: problem with the application
If you manage to follow steps 1-4, but still see the application crash, this is probably the configuration problem in your application. Think about a few possible problems here. a) Is your application running under the same account with the account that you checked in step 4? If not, you can try testing in step 4 under this account or, if possible, change your working service account. b) What SQL driver does your application use? c) What is the connection string? Is the connection string compatible with your driver? Please check http://www.connectionstrings.com/ for reference.
Step 6: Authentication and Login Issues This is probably the hardest part for SQL connectivity issues. This is often related to the configuration on your network, your OS, and your SQL Server database. There is no simple solution for this, and we must solve it in each case. There are already several blogs in sql_protocols that talk about some special cases, and you can check to see if they have any of them. Also, keep in mind: a) If you use SQL auth, mixed authentication must be enabled. Check this page for reference http://msdn.microsoft.com/en-us/library/ms188670.aspx b) Make sure your account has permission to access the database that you used during login (" Start Directory "in OLEDB). c) Check the event log on your system, see if there is any additional information
Link: http://blogs.msdn.com/b/sql_protocols/archive/2008/04/30/steps-to-troubleshoot-connectivity-issues.aspx
Sorry for this text wall, I hope something from here helps you solve your problem!
Sincerely.