Steps to troubleshoot SQL connectivity issues

Steps to troubleshoot SQL connectivity issues

Rate This
  • Comments 51

We have been seeing and trying to resolve SQL connectivity issue all the time. I guess it would be helpful if we can put some guidance on how to resolve connectivity issues. Here comes a proposal based on my experience.

 

Basically, when you failed to connect to your SQL Server, the issue could be:

1) Network issue,

2) SQL Server configuration issue.

3) Firewall issue,

4) Client driver issue,

5) Application configuration issue.

6) Authentication and logon issue.

 

Usually, customers see connectivity issue in their applications, but it would be great if you can follow the steps below to eliminate issues one by one and post a question on SQL Server Data Access forum if needed.

 

 

Step 1: Network issue

You might be able to make local connection without a working network, but that's a special case. For remote connection, a stable network is required. The first thing to trouble shoot SQL connectivity issues is to make sure the network we rely on is workable and stable. Please run the following commands:

 

ping -a <your_target_machine>    (use -4 and -6 for IPv4 and IPv6 specifically)

ping -a <Your_remote_IPAddress>

nslookup (type your local and remote machine name and IP address multiple times)

 

Be careful to see any mismatch on the returned results. If you are not able to ping your target machine, it has high chance that either the network is broken or the target machine is not running. It's possible the target machine is behind a firewall and the firewall blocks the packets sent by ping, though. Windows firewall does not block ping (ECHO) packet by default. The correctness of DNS configuration on the network is vital to SQL connection. Wrong DNS entry could cause of all sorts of connectivity issue later. See this link for example, "Cannot Generate SSPI Context" error message, Poisoned DNS.

 

Step 2: SQL Server configuration issue

You need to make sure the target SQL Server is running and is listening on 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 protocols (and VIA which needs special hardware and is rarely used). For remote connection, NP and/or TCP protocols must be enabled. Once you enabled protocols in SCM, please make sure restart the SQL Server.

 

You can open errorlog file to see if the server is successfully listening on any of the protocol. The location of errorlog file is usually under:

%ProgramFile%Microsoft SQL Server/MSSQLxx.xxx/MSSQL/Log

If the target SQL instance is a named instance, you also need to make sure SQL Browser is running on the target machine. If you are not able to access the remote SQL Server, please ask your admin to make sure all these happen.

 

Step 3: Firewall issue

A firewall on the SQL Server machine (or anywhere between client and server) could block SQL connection request. An easy way to isolate if this is a firewall issue is to turn off firewall for a short time if you can. Long term solution is to put exception for SQL Server and SQL Browser.

 

For NP protocol, please make sure file sharing is in firewall exception list. Both file sharing and NP use SMB protocol underneath.

For TCP protocol, you need put the TCP port on which the SQL Server listens on into exception.

For SQL Browser, please put UDP port 1434 into exception.

Meanwhile, you can put sqlservr.exe and sqlbrowser.exe into exception as well, but this is not recommended. IPSec between machines that we are not trusted could also block some packets. Note that firewall should never be an issue for local connections.

 

 

Step 4: Client driver issue

At this stage, you can test your connection using some tools. The tests need to be done on client machine for sure.

 

First try:

telnet <your_target_machine> <TCP_Port>

You should be able to telnet to the SQL server TCP port if TCP is enabled. Otherwise, go back to check steps 1-3. Then, use OSQL, SQLCMD, and SQL Management Studio to test sql connections. If you don't have those tools, please download SQL Express from Microsoft and you can get those tools for free.

 

OSQL (the one shipped with SQL Server 2000) uses MDAC.

OSQL (the one shipped with SQL Server 2005 & 2008) uses SNAC ODBC.

SQLCMD (shipped with SQL Server 2005 & 2008) uses SNAC OLEDB.

SQL Management Studio (shipped with SQL Server 2005 & 2008) uses SQLClient.

 

Possilbe command use be:

osql -E -SYour_target_machine\Your_instance for Windows Auth

osql -Uyour_user -SYour_target_machine\Your_instance for SQL Auth

 

SQLCMD also applies here. In addition, 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 fails for all protocols or just some specific procotols.

 

At this stage, you should not see general error message such as error 26 and error 40 anymore. If you are using NP and you still see error 40 (Named Pipes Provider: Could not open a connection to SQL Server), please try the following steps:

a)       Open a file share on your server machine.

b)       Run “net view \\your_target_machine” and “net use \\your_target_machine\your_share  (You can try Map Network Drive from Windows Explorer as well)

If you get failure in b), it's very likely you have OS/Network configuration issue, which is not SQL Server specific. Please search on internet to resolve this issue first.

 

You can try connection using both Windows Authentication and SQL Authentication. If the tests with all tools failed, there is a good chance that steps 1-3 were not set correctly, unless the failure is logon-related then you can look at step 6.  

 

If you succeeds with some of the tools, but fails with other tools, it's probably a driver issue. You can post a question on our forum and give us the details.

 

You can also use “\windows\system32\odbcad32.exe” (which ships with Windows) to test connection by adding new DSN for various drivers, but that's for ODBC only.

 

 

Step 5: Application issue

If you succeed with steps 1-4 but still see failure in your application, it's likely a configuration issue in your application. Think about couple of possible issues here.

a) Is your application running under the same account with the account you did tests in step 4? If not, you might want to try testing in step 4 under that account or change to a workable service account for your application if possible.

b) Which SQL driver does your app use?

c) What's your connection string? Is the connection string compatible to your driver? Please check http://www.connectionstrings.com/ for reference.

 

 

Step 6: Authentication and logon issue

This is probably the most difficult part for sql connectivity issues. It's often related to the configuration on your network, your OS and your SQL Server database. There is no simple solution for this, and we have to solve it case by case. There are already several blogs in sql_protocols talking about some special cases and you can check them see if any of them applies to your case. Apart from that, things to 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 login account has access permission on the database you used during login ("Initial Catalog" in OLEDB).

c) Check the eventlog on your system see if there is more information

 

At last, please post question on our forum. More people could help you over there. When you post question, you can refer to this link and indicate you see failure at which step. The most important things for us to troubleshoot are a) exact error message and b) connection string.

 

 

Xinwei Hong, SQL Server Protocols
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Leave a Comment
  • Please add 5 and 5 and type the answer here:
  • Post
  • i'm using oledb connection.

    while deploying my application to client pc i can't connect to sqlserver 2005 developer ed.

    while from my pc (i have vb.net 2003) i'm connecting

    error message

    [DBNETLIB][Connection Open(Connect()).]SQL Server does not exist or access denied

  • In SQL server the default instance has a listener which listens on the fixed port which is TCP port 1433.

  • I am not able to connect to SQL 2005 DB in windows authentication mode remotly from web application. Is it required to configure kerberos auhentication?

    What could be the cause of this?

  • Hi!... I have this configuration:

    1 SQL Server 2000 Enterprise Edition mounted on a Windows 2003 Server (wich we will call Server A). SQL Server is listening trough port 1433

    1 Windows 2003 Server (wich we will call Server B)

    Both Servers are on the same network group

    What I need is to create an SQL Server System DSN to connect from B to a SQL Server Database located in A.

    When I do This i get the following error:

    [DBNETLIB]SQL Server does not exist or access denied

    [DBNETLIB]ConnectionOpen (Connect()).

    If I try to use the osql command, everytime i get the same error...

    What might be causing this????

  • Hi, I am having below error in my production server that running ASP.NET2.0 and SQL Server 2005. Can someone tell me why this error happen? Thanks.

    Source = .Net SqlClient Data Provider

    Error = A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - Not enough storage is available to complete this operation.) |    at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)

      at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

      at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

      at System.Data.SqlClient.SqlConnection.Open()

  • Thank for Help on Step 2, it seems SQL Express defaults these all to off. SO remote access doesnt work.

  • I am having occasional/rare connection problems with ADO/ADO .Net connection pooling. The connections use aliases set up for TCP/IP. Could the problems be occurring because of Kerberos Ticket Expiration with the pooled connections or could it be that SQL Server is Orphaning the pooled connections or could it be some sort of socket problem? Perhaps someone has another suggestion as to what may be wrong. I am considering using Named Pipes instead to get around Kerberos authentication.

  • In my case, the SQL application slows down to a crawl when the customer loses his internet service. When the service was restored by the provider, it started working like is should. Any thoughts . .  .

  • Harvey, it's probably Windows Authentication and/or SSL handshaking are tring to connect the DC or some network entity.

    For all other guys, some are general cases. Please ask questions in the forum so that somebody can help you troubleshooting. We need more detailed info about the failure and its circumstance!!!

    Thanks.

    Xinwei

  • I've also found that running a quick trace to catch login failures is useful. App teams generally don't believe that their app is configured wrong. They will insist that the server is down even though all connectivity tests we use work. We will start the trace and have them try to connect.  If you don't see a failure, you can absolutely point to a problem outside of MSSQL.  It sometimes takes this to get folks to check other possibilities.

  • I'm having an issue connectiont to SQL server through Access.  It only occurs during testing when we set the PC date forward on the client machine.  Has anyone come accross this? ErrorMsg:  -2147467259 Cannot generate SSPI Context

    Thanks

  • We're seeing an issue in our environment where we have a .NET 1.1 web application on a Windows 2003 server machine and SQL Server on a Windows 2000 server machine.  In the application, we use SQL logins to connect to the db.  What we are seeing is, when we put a bad password to log in, an error message is generated (login failed) in the event log which is what's expected, but then we continually see the same error message repeating for the next few hours as if the connection is still alive and retrying automatically.  Any ideas why?  Thanks

  • I am trying to connect to a MSSQL  2005 server from a Windows NT box through a system DSN.  I know 2005 prefers you use SQL Native Client to connect, however, all that is on this machine is the SQL Server driver.  How can I make this work?

  • I just installed MSSQL 2008 Server on my laptop XP SP3 for evaluation purposes. All SQL services start except for the engine. I get the following message in event viewer.

    SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

    When I research this I get lots of different things but none seems to help my issue.

    Thanks in advance for any help.

  • Please help to find the solution to problem.

    We used MSSQL 2000 without any problems with ODBC , and now we tried to use MSSQL2008. Approximately on every fifth PC we cannot set the ODBC connection with SQL server, we receive error logs :

    SQL State '1000'

    SQL Server error 10060

    SQL State '08001'

    SQL server error 17

    we tried everything, it seems that on some clients something missing, but I have no idea what is this.

    The clients MSSQL ODBC version : 03.85.1132

    with this version on one PC it is working, on the pother isn't.

    I tried to turn of the firewall : no effect.

    What to do ?

Page 1 of 4 (51 items) 1234