Troubleshoot connectivity issues in SQL Server

Applies to:   SQL Server
Original KB number:   4009936

There are various causes for connectivity issues in SQL Server. This article series helps you troubleshoot the most common SQL Server connectivity issues and describes the tools and methods you can use for troubleshooting.

Note

If multiple SQL Server instances in your environment are impacted, or the issue is intermittent, it usually indicates Windows policy or networking issues.

Preparation

Before you start troubleshooting, check Recommended prerequisites and checklist for troubleshooting connectivity issues for logs that you should collect to assist with troubleshooting. There's also a list of quick actions to avoid common connectivity errors when working with SQL Server.

Common connectivity issues

Use the following list to navigate to the appropriate article page for detailed troubleshooting steps for your scenario:

• A network-related or instance-specific error occurred while establishing a connection to SQL Server

• Can't generate SSPI context

• Login failed for user which covers the following login errors:

  • "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'"

  • "Login failed for user '(null)'"

  • "Login failed for user (empty)"

  • "Login failed for user '<username>'"

  • "Login failed for user '<domain>\<username>'"

• Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

• An existing connection was forcibly closed by the remote host

• Token-based server access validation failed with an infrastructure error. Check for previous errors

• The certificate chain was issued by an authority that is not trusted

Tools and methods to troubleshoot connectivity issues

The following sections provide details for various tools and procedures to troubleshoot different connectivity errors:

Configure Windows Firewall to work with SQL Server

Configure firewalls to work with SQL Server describes how to configure Windows Firewall for successful connections to instances.

Test an OLEDB or ODBC connection to SQL Server

• If you want to quickly test a connection from a client machine to a SQL Server instance, you can configure a UDL file. For more information, see Test connections to SQL Server by using a Universal Data Link (UDL) file.

• You can also use the ODBC Data Sources (64-bit) or ODBC Data Sources (32-bit) Administrator to configure and test an ODBC connection to SQL Server. For more information, see ODBC Data Source Administrator DSN options.

Discover if a port is blocked with PortQryUI

You can use the PortQryUI tool (a graphical user interface (GUI) port scanner) to help troubleshoot connectivity issues. For more information, see Use PortQryUI tool with SQL Server.

Find which ports SQL Server listening on

This article Check whether SQL Server is listening on dynamic or static ports provides steps to identify which ports SQL Server is listening on.

Get a detailed diagnostic report with SQLCheck

Microsoft product support engineers frequently use SQLCheck, which is a tool they built to diagnose causes of connection failures. You can follow the instructions to download and collect the SQLCheck report for in-depth analysis.

Use Kerberos Configuration Manager

To identify and resolve Service Principal Name (SPN) issues, you can use the Kerberos Configuration Manager. For more information, see Use Kerberos Configuration Manager to fix SPN issues.

Collect and analyze a network trace with SQLTrace and SQLNAUI

In many cases, a network trace might be the most effective tool to investigate network failures. You can follow the steps in Collect network traces on the client and server to investigate failures. Then, you can analyze the network trace using the SQL Network Analyzer UI SQLNAUI.

See also

CSS SQL Connectivity Wiki