One of my clients faced a strange situation. She was using SQL Server Management Studio and was not seeing SQL server and SQL Agent status. It was showing white circle. When she would right click the instance, the options of Start, Stop and Resume were disabled. She also said that she is having SysAdmin permissions in SQL Server (logging using the SA account).
This is what she meant:
I explained that it all depends on your Windows account which you are using for launching SSMS. Whether your windows account you’re using has access to the services, information on the server in question?
SQL Server Management Studio uses WMI to query for information about the status of the services. So I asked to check below.
- If it’s a local machine, then make sure you are using “Run as administrator” option.
- Make sure “Windows Management Instrumentation” service is running under services.msc on the server.
- Make sure that the firewall is not blocking WMI communication. Run WMI query to make sure we are able to query services. Below are the steps.
- Start > Run > WBEMTest
- Click on Connect and provide below (change Remote Machine Name and ComputerManagement<Number> based on SQL version.
- Click Connect
- Click on Query
- Type Select * from SQLService and Apply. You should see the results.
If you have a problem with above that its confirmed WMI issue.
- Make sure that you are able to open SQL Server Configuration Manager, which uses WMI.
In her case, we found that service was disabled, which was causing this behavior.
Have you faced a similar issue? What was the solution you discovered?
Reference: Pinal Dave (https://blog.sqlauthority.com)