What to try if you hit connection problems
Published 21 October 2021
SQL Monitor collects data from your monitored servers by using WMI and SQL. If you're having connection problems in SQL Monitor, you can investigate where the problem is by testing each connection type independently.
You need to run these tests on the SQL Monitor Base Monitor host machine.
Check the Monitoring Status and the SQL Monitor logs
Go to Configuration > Monitored Servers in the web interface:
- The Status column will tell you whether the connection problem is with the machine or SQL Server instance. See: Monitoring status explained for more details.
- Use the Show Log option for the machine to review the logs, which can be very revealing if you are having any connection problems. See: Viewing connection information for monitored servers.
Test the WMI Connection
If you're having connection problems in SQL Monitor, the first thing to do is to make sure that you can interrogate, via WMI, each of the machines which you wish to connect to and monitor. You can do this by hand, logging in to each machine remotely, using the credentials you provided to SQL Monitor. You can then try "remoting" to the machine in PowerShell to check that the transport for WMI that you’ve chosen (DCOM or WinRM) is working. However, it will be more efficient to test various aspects of the connection using PowerShell.
Testing WMI connections manually
If the WMI connection is over DCOM, you can test it using the Microsoft Windows Management Instrumentation tester tool (WBEMTest). You need to run these tests on the SQL Monitor Base Monitor host machine, connecting to the affected server.
Testing WMI connections using PowerShell
The following PowerShell scripts will allow you to interrogate various aspects of the WMI connection, over DCOM or WMI. The first one in the list is especially useful as a starting point and should tell you the extent of the work you need to do. If you cannot get WMI to work at all, the first port of call is the article Connecting to WMI on a Remote Computer.
Testing the SQL connection
You can test SQL connectivity using SQL Server Management Studio.
If connection fails, one of the following error messages is displayed:
- Timeout expired.
- An existing connection was forcibly closed by the remote host.
- No process is on the other end of the pipe.
- Login failed for user <x>.
A connection failure may also occur if the system administrator privileges used to access the database engine are inadvertently deleted. To resolve these issues, see: Troubleshooting Database Engine Connectivity.
Setting up a lab environment
If you run into difficulties connecting to network-based devices, it is best to create a "lab" environment, entirely unconnected to the Internet and isolated from your main network and containing servers as similar as practically possible to production. You can now temporarily relax the most stringent precautions built into firewalls, servers, WMI and WinRM, and quickly get to a point where everything is working. Then you can put back the security devices one by one until something breaks. Then fix the issue and carry on. Once you have connections and production-level security, you can then assume that the only likely source of other potential problems are firewall-related.