Troubleshooting Common SQL Server Inspector Issues

1. Error Locating Server/Instance Specified

Connectivity test failed. Exception: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

This setup issue only occurs in the event that you've entered a named instance in the Inspector template.

In most cases, this setup issue indicates that the Inspector was unable to locate the instance specified in the Inspector configuration template. It's important to note that if your goal is NOT to run the inspection against a named instance, you should leave this field blank. Leaving the field empty will prompt the Inspector to run the inspection using the default SQL Server instance name, MSSQLSERVER.

If you're trying to specify a named instanced in the template, it might also need to be in the form 'hostname\instance'.

2. Connection Timeout Expired

Connectivity test failed. Exception: Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - Pre-Login initialization=12345; handshake=12345;

This setup issue could indicate a range of issues, here are a few of the most common areas to check:

TCP/IP

Verify that TCP/IP is enabled for both IPv4 and IPv6 addresses for your instance under SQL Server Network Configuration in SQL Server Configuration Manager. It's also worth checking to see if inbound connections for the TCP/IP port, the SQL Server Database Engine, is listening or might be blocked by your firewall.

You should also double-check that the hostname and instance specified in the Inspector configuration template are correct for the instance you wish to inspect.

3. Error: 40 - Could not open a connection to SQL Server

Connectivity test failed. Exception: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

This error indicates a general issue connecting to the server on which your SQL Server Database Engine instance is running. To get started troubleshooting, please reference this knowledge base article from Microsoft.

4. Longest Queries Field

The Longest Queries Field in your SQL System Inspector is capped at 50 characters. Any query longer than that will have the first 50 characters captured and then ellipses will be added to the end of the string.