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.
\\<RemoteMachineName>\root\Microsoft\SQLServer\ComputerManagement13 - 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)
9 Comments. Leave new
I also see white circles in a cluster installation .
Reason is you should start/stop/SWITCH services with ” failover cluster manager”.
Nice to know. Thanks Pinal :)
Hello Pinal ,
Hope you are doing good and If we connect the instance of one server in another server we can see that white icon.I have observed that in my environment.If we have administrator permissions we can see green icon on instance and server agent.
I do have this problem in two of our servers. The weirdest thing is I checked several times and the WMI services status were showing started. After reading the blog I just went and restart the WMI services which fixed the issue.
We have same kind situation…..
to my knowledge it depends what permission he has in windows account….where Sql Server Launched,
Hi ,
I also checked and found facing similar issue. I followed the steps mentioned and I am able to connect to Config Manager using WMI, still the issue persists. Even tried restarting WMI service but no help.
I have created a test environment in LAN. Restarted WMI on both server (Windows Server 2012 R2, SQL Server 2014 Enterprise) and client (Windows 7, SQL Server 2014 management studio). Turned off firewall on server and client. Still the icon is white on client, however in SSMS on server it is green.
Any tips?
I found that I needed to have my account added as a local admin on the SQL Server instances to see this, however, I have not been able to do this across domains. I’m looking into if there are any firewall restrictions between the domains on our WAN, any other thoughts about that? Other than port 135, are there any other ports that need to be open in order to have this feature work?
Good to know, thanks Pinal