SQL Connectivity troubleshooting checklist

  • Article

Hello all,

During daily operations with SQL Instances, DBA/Application teams frequently encounter SQL Connectivity issues while connecting from the applications. In this  blog, I am covering few checklist steps to troubleshoot SQL Connectivity issues.

When a client connects to SQL Server, it goes through authentication and authorization stages. If the connectivity issue is because of authorization, client will be able to establish a physical connection with SQL Server, but the issue would be while authorizing with SQL Server like Login errors because of permission issues etc.  If the issue is because authorization, SQL Server Error log is the starting point for further troubleshooting.

Very important step in addressing SQL Connectivity issues is isolating which component is causing the issue: Broadly, we can classify the the connectivity issues in below categories:

  1. Network issue,
  2. SQL Server configuration issue.
  3. Firewall issue,
  4. Client driver issue,
  5. Application configuration issue.
  6. Authentication and logon issue.

Some of the common error messages thrown by the application while connecting to SQL Server are:

Error 1:

Test connection failed because of an error in initializing provider. [DBNETLIB] [ConnectionOpen (Connect ()).]SQL Server does not exist or access denied.

 

Error 2:

[Microsoft] [SQL Server Native Client 11.0]SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

[Microsoft] [SQL Server Native Client 11.0] Login timeout expired

[Microsoft] [SQL Server Native Client 11.0] A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information, see SQL Server Books Online.

 

Error 3:

An error has occurred while establishing a connection to the server. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 5)

An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 1326)

 

Error 4:

[Microsoft] [SQL Server Native Client 10.0] TCP Provider: An existing connection was forcibly closed by the remote host.

CHECKLIST STEPS:

1.Get the details of the servers involved in the connectivity.

  • How many servers are involved?
  • Is the application a web based/thick client application?
  • Is the client connecting to application server which then connects to SQL Server?
  • Is the connectivity issue happening only on one client box or multiple clients are not able to connect as well?
  • Collect server names and IP addresses of all the servers involved.

2. Check if the server is SQL Server is reachable. Simple test is to perform network ping to check if the server is reachable.

Ping <SQLServername> or Ping <IP address of SQL Server> If Ping itself fails to locate the SQL Server box, this indicates that client is not able to locate SQL Server machine itself and we need Network team assistance to debug this further.

3.Check if the server is SQL Server is reachable. Simple test is to perform network ping to check if the server is reachable.

 

4.Local connectivity on SQL Server box works on Shared memory protocol. Next step is to check the protocols enabled on SQL Server and ensure that TCP/IP and Named Pipes protocols is enabled for remote connections:

         

 

5. To check if the client is able to reach the SQL Server port or not: We can use telnet tool to test if the SQL Server port is listening to incoming connections.

For example: if the SQL Server Host name is: SQLprod and the port on which SQL is listening is: 1433
telnet sqlprod 1433
If the client box is not able to reach the port on SQL is listening on, we get the error as mentioned below:

 

6. If the telnet fails as well, this is a clear indication that some device (Firewall) is blocking the port. To verify if the port is blocked or not, we can use the tool portqueryUI.
PortqueryUI tool can be downloaded from: https://www.microsoft.com/enin/download/details.aspx?id=24009

If you see the output as Filtered, this indicates that the port is blocked.
For example, if the SQL hostname is aoindia and SQL Port is 1433:

Next step is check with Windows/networking team to allow the port. Ensure that the Inbound and outbound rules are created to allow the connection for SQL port at operating System level.

 

7. Once the port is allowed, run the PortqueryUI program again and ensure that the status shows as LISTENING.

Telnet tool can be used only to check if the TCP port is blocked or not. But the PortQueryUI tool can be used for both TCP and UDP to check if port is blocked.

 

8. Collect the connection string details of the application. If the application is web based, web.config file will generally have the connection string details. Gather the following details: a. Data Source b. Initial Catalog c. Integrated Security d. Provider
For example:
Provider=SQLNCLI10; Server=myServerName\theInstanceName;Database=myDataBase; Trusted_Connection=yes;
Few important details to be studied from connection string are: Type of authentication used (Windows/SQL), provider used (OLE DB or ODBC), Data Source field.

 

9. To isolate if the issue is caused by application or not, simple test would be to connect to the SQL instance using SQL management studio from the client box. But in most scenario’s, SSMS tool may not be installed on the client machines. In this case, we can create a universal data link tool (UDL) to test the connectivity.
From the client machine, create a text document and rename the extension as .udl.
For example: test.udl
Based on the provider used by the application (Microsoft OLE DB for SQL Server), or SQL native Client provider, select the appropriate provider list in udl file and click on next.

Enter the SQL Server name and default database and click on test connection:

If the connection succeeds through udl, this indicates that some connection string configuration is incorrectly formed in the application.

Try to simulate the connectivity issue using the same settings mentioned in the connection string and check if you are able to reproduce the issue or not.
If the connectivity works from UDL, then check with application team if the connection string can be replaced with the one created by udl. [Open the udl file in notepad to get the dynamically generated connection string]

 

10.  If the issue is client provider specific, check if different provider can be used to connect to SQL. From udl file, select different providers of SQL and check connectivity works or not.
For example, if the issue is specific to Microsoft OLE DB for SQL Server, check if SQL native client can be used.

 

11. If the application connection string is using data source name (commonly known as DSN), then create a user DSN using ODBC Data Source Administrator tool (odbcad32) and check the connectivity.

To launch ODBC administrator tool in 32-bit mode: C:\Windows\SysWOW64\odbcad32.exe

 

12. On the client machine: at the below registry location, Last SQL Server connect entry is stored: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client

Clear the entries listed for lastConnect entry for the SQL Server in question and delete these entries. [Ensure that the registry backup is taken before making any modifications]

 

13. Check if there are client side aliases present using SQL Server Client network utility:

To launch the tool:

In alias tab, check if there are any orphaned alias entries. Take the backup of the aliases present and delete the entries if there are any aliases created for the SQL Server instance we are troubleshooting.

To launch the SQL Client config tool in 32-bit mode: C:\Windows\SysWOW64\cliconfg.exe

 

14. Check if you are able to test the connectivity by forcing the protocols like TCP and Named pipes.

By forcing the protocol, if the connectivity works, check with the application team if the connection string can be modified to force the protocol in the data source field.

To check if the connection established with SQL is going over TCP/named pipes: execute the below query:

select session_id, net_transport from sys.dm_exec_connections

If no modifications can be done on connection string and if the connectivity works by forcing a protocol, check if a client side alias can be created.
Example: If the connectivity works only on Named pipes protocol, create a named pipe alias using SQL Server client network utility:

 

15.  Check if you are able to connect to the instance by forcing the SQL port.  For example:

If the connectivity works while forcing the port number, this indicates an issue with SQL Browser. SQL Browser is responsible to resolve the Instance name with port registered on SQL Server. Then the option is to check if SQL UDP browser port 1434 is blocked or not and troubleshoot browser issues

 

16.  Check if the connectivity works by specifying the IP address of the SQL Server instead of the hostname. For example:

If the IP address of instance hosting SQL is 10.16.17.18 and 5223 is the port on which SQL is listening on:


If the connectivity works by hardcoding the IP address in the Server name, then as a temporary resolution, create a host file entry with the Server name and the IP address mapping to avoid DNS name resolution and involve the DNS team for name resolution issues.

 

17. Even after trying out the above steps, if the connectivity issue is still not resolved, next step is to collect the Network traces using Network Monitor tool.

 

Hope the above steps mentioned will help you in troubleshooting SQL Connectivity issues.

Happy troubleshooting!

Please share your feedback, questions and/or suggestions.

 

Thanks,

Don Castelino | Premier Field Engineer | Microsoft

 

Disclaimer: All posts are provided AS IS with no warranties and confer no rights. Additionally, views expressed here are my own and not those of my employer, Microsoft.