Skip to content

0200 Consistent Network Issue

Jump to bottom
Malcolm Stewart edited this page Sep 28, 2020 · 18 revisions

0200 Consistent Network Issue

These errors are consistent and repeatable every time. They point to a configuration issue of some sort, such as SQL Server does not have TCP enabled or a firewall is blocking the connection. They do not include any text regarding SSPI or user errors, which would be an authentication error. Troubleshooting will be focusing on remote TCP connections as this is the most common connection type for most data centers, but many of the techniques can also be applied to Named Pipes.

0200.1 Typical Error Messages

  • Communication link failure.
  • General Network Error.
  • The specified network name is unreachable.
  • SQL Server does not exist or access denied. (this can also be an authentication error)

0200.2 Moving Parts

The initial goal is to try to isolate which of the moving parts is causing the problem.

Moving Parts

0200.3 Pre-Work

Please perform the initial data collection and narrowing steps: 0100 Initial Data Collection and Scoping Questions. This will help get a macro perspective of the scope of an issue, such as whether the issue affects multiple computers or just one, or whether only those computers in a specific data center are facing issues. This can help focus the troubleshooting steps. It will also make you prepared for discussing the issue with Microsoft Support should you choose to do so.

0200.4 Isolation Shortcuts

These steps will help to divide the troubleshooting into a focused area:

  • Client: Application, connection string, driver (lacking TLS 1.2), SQL alias (64/32 bit), HOSTS file, bad DNS suffix (full name connects, short name fails).
  • SQL Server: Database engine, enabled protocols, firewall.
  • Network: DNS alias, gateway, router, firewall.

0200.4.1 Can you connect to the SQL Server locally using SQL Server Management Studio and TCP? e.g. tcp:SQLPROD01.CONTOSO.COM,1433
Note: tcp must be lower case when added before the server name.
YES = SQL is okay. Problem area = firewall, network, or client.
NO = Try section 0200.5.1 SQL Server Service.

0200.4.2 Can you connect to the SQL Server port from the client machine using TELNET? e.g. TELNET SQLPROD01.CONTOSO.COM 1433
YES = Problem with the driver/provider, security/SSL, SQL Alias, or application.
NO = Problem with hosts file, network, or firewall, assuming 0200.4.1 works.

  • If TELENET is not available as a command, add it as a Windows Feature. This does not require a reboot.

0200.4.3 Assuming 0200.4.2 works, can you connect to the server using a UDL file?
YES = the issue is in the application, likely a bad connection string, or in the provider used by the application if different than used in the UDL file.
NO = continue with 0200.5.4 Client.

0200.4.4 Can other clients connect to the SQL Server?
YES = Problem with firewall, network, TLS 1.2, or client.
NO = Problem with firewall or server.

0200.4.5 Can the client connect to other servers?
YES = Problem with firewall, network, TLS 1.2, or server.
NO = Problem with firewall, network, or TLS 1.2.

0200.5 Basic Isolation

Use the following isolation steps to move backward from the SQL Server machine to try to see what is failing.

0200.5.1 SQL Server Service Validate the service process (sqlserver.exe) is running in Task Manager. If you have multiple instances installed, you can check via SQL Server configuration Manager or services.msc. If it is not, try starting the instance. If it is a mirrored or clustered configuration, make sure you are on the primary/active node. Use the cluster manager for fail-over clusters or always-on clusters to make sure the resources are on-line. Validate whether the Application Event log, cluster logs, or the SQL Server ERRORLOG file indicates a fatal error that is actionable. If you cannot start the service, the Microsoft database engine team can assist.

Validate the expected protocols are enabled in the SQL Server Configuration Manager and in the SQL Server ERRORLOG file (see sample below). If not, enable them and restart the SQL Server. TCP should always be enabled if remote connections are allowed.

2013-11-20 09:42:03.90 Server Server is listening on [ 'any' <ipv6> 1433]. 2013-11-20 09:42:03.90 Server Server is listening on [ 'any' <ipv4> 1433]. 2013-11-20 09:42:03.94 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\KJ ]. 2013-11-20 09:42:03.94 Server Server named pipe provider is ready to accept connection on [ \\.\pipe\MSSQL$KJ\sql\query ].

Clone this wiki locally