September 6, 2011 at 2:09 pm
I was having this problem on a development SQL server. Permissions were all correct, and i was able to manage the services remotely using the windows services management console, and the sql service configuration manager, but i was unable to view or modify the service status through the management studio.
The server I'm working on is going to replace an existing server, so it's currently running with a temporary hostname that isn't in DNS. This means I've had to connect to it using the IP address. I found that by adding the server to my hosts file and connecting by name instead of IP, i now get the green arrow and I can manage the instance through SSMS. Not sure why that makes a difference, but it worked for me!
September 7, 2011 at 1:54 am
DiggityShmoo (9/6/2011)
I was having this problem on a development SQL server. Permissions were all correct, and i was able to manage the services remotely using the windows services management console, and the sql service configuration manager, but i was unable to view or modify the service status through the management studio.The server I'm working on is going to replace an existing server, so it's currently running with a temporary hostname that isn't in DNS. This means I've had to connect to it using the IP address. I found that by adding the server to my hosts file and connecting by name instead of IP, i now get the green arrow and I can manage the instance through SSMS. Not sure why that makes a difference, but it worked for me!
interesting. I'll give that a go later.
Adam Zacks-------------------------------------------Be Nice, Or Leave
September 7, 2011 at 3:21 am
Schadenfreude-Mei (9/7/2011)
DiggityShmoo (9/6/2011)
I was having this problem on a development SQL server. Permissions were all correct, and i was able to manage the services remotely using the windows services management console, and the sql service configuration manager, but i was unable to view or modify the service status through the management studio.The server I'm working on is going to replace an existing server, so it's currently running with a temporary hostname that isn't in DNS. This means I've had to connect to it using the IP address. I found that by adding the server to my hosts file and connecting by name instead of IP, i now get the green arrow and I can manage the instance through SSMS. Not sure why that makes a difference, but it worked for me!
interesting. I'll give that a go later.
Sorry, no dice 🙁
Adam Zacks-------------------------------------------Be Nice, Or Leave
September 8, 2011 at 10:31 am
I'm not sure if this is the correct answer; I've got the same lack of "server status" display on half of our servers, but haven't had time to pursue it myself.
According to BoL, WMI is used for the "Windows service level" monitoring and control for SQL Server. This is completely outside the SQL Server connection and permissions. There is a separate way to grant permissions for "remote access" to a server via WMI; check with your Windows sysadmin, and this is also documented in BoL (look under WMI).
SQL Agent can also define Alerts based on WMI, and SSIS has WMI query capabilities as well. SMO and PowerShell also use WMI for some of their server management functions. So its worth checking out.
David Lathrop
DBA
WA Dept of Health
January 14, 2013 at 1:18 pm
I know that this thread has gone stale without replies for quite some time. However, I was having this same problem and I figured it out. This may or may not resolve the problem that the orginal poster had but here is what I had to do.
1. Open Control Panel and then double click the Windows Firewall icon to open the Windows Firewall control panel.
2. In the upper left, click Allow a program through Windows Firewall. This will open up the Windows Firewall Settings window.
3. Select the Exceptions tab, if it isn't already selected. Scroll down and make sure that Windows Management Instrumentation (WMI) is checked.
4. Click OK or Apply.
That's all I had to do. Hope this is helpful.
January 15, 2013 at 8:18 am
Erik Edvalson (1/14/2013)
I know that this thread has gone stale without replies for quite some time. However, I was having this same problem and I figured it out. This may or may not resolve the problem that the orginal poster had but here is what I had to do.1. Open Control Panel and then double click the Windows Firewall icon to open the Windows Firewall control panel.
2. In the upper left, click Allow a program through Windows Firewall. This will open up the Windows Firewall Settings window.
3. Select the Exceptions tab, if it isn't already selected. Scroll down and make sure that Windows Management Instrumentation (WMI) is checked.
4. Click OK or Apply.
That's all I had to do. Hope this is helpful.
Thanks for this. We have since upgraded to ss2k8r2 and dont have 2k5 clusters anymore, so i cant test the solution. I am almost certain that the FW would have been off on all of the servers. And i have just checked my desktop pc to confirm, my FW is also off.
If the FW's were all off, would 'Exceptions' still be needed?
TIA,
Adam
Adam Zacks-------------------------------------------Be Nice, Or Leave
January 16, 2013 at 7:14 am
I have this issue about once every 4 months. I pursued all of the permission issues and other things listed above. What I finally did was log into the SQL Server and restart the WMI service. This instantly fixed it. I see that your issue is probably more complicated than that, but several other people posted on this thread with the same issue and their fix might be that simple.
February 13, 2013 at 3:34 am
It is interesting because I have exactly the same problem as the original poster. I don't see a server state for all the machines to which i don't have Windows access but for all the machines to which I can log on (via RDP) I see the green indicator.
I can however view all the services running on the remote server when the green indicator is visible. Likewise, where I don't have Windows account access to the remote machine, I can't view the running services using the services snap-in!
In my case the problem lies with my inability to access the Windows Server with my Windows Domain-based account in order to view the services.
February 22, 2013 at 10:20 am
I have opened SSMS icon properties and checked "Run this program as an administrator" checkbox on "Compatibility" pannel.
Now when I run SSMS from this icon I can see server and Server agent status (green or red) and start, stop, restart etc. options are available in the menu.
January 20, 2015 at 6:05 pm
I found this thread via google seach and the way I finally fixed it was adding myself to the administrators group on the windows servers that is hosting the SQL server...
March 11, 2015 at 3:35 pm
Saw this thread from google as well. I have this happen occasionally in SQL 2012 SSMS, not really in earlier Management studios unless one of the myriad of more common causes like permissions, service account issues, firewall, duplicate SPN, etc pop up. What I've noticed is if you have a bunch of servers in Registered Servers, and start connecting in Object Explorer before SSMS has had a chance to poll all of your registered servers, you can get the white circle (restarting SSMS seems to help).
It seems to be more pronounced if you have servers with high latency or across domains, where it can cause the registering to hang and then any time you refresh a server in Object explorer they go white even if they had green arrow before. Try waiting until everything you have registered 'goes green', that includes Analysis servers, before dropping on into Object explorer.
MCSA SQL 2014
March 17, 2015 at 2:14 am
Hi,
I can only see these green arrow, if I start the SSMS within the right domain.
I have two domains, and if I start SSMS just for DOMAIN1, all green arrows for the serves from DOMAIN2 are missing.
I start SSMS like this:
runas /netonly /user:YOURDOMAIN\DOMAINUSER "C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\Ssms.exe"
How do you start SSMS, and is your PC in the same domain?
Kind Regards,
Andreas
March 17, 2015 at 7:00 am
I haven't read through the 8 pages of responses on this thread but the most recent question seems to indicate there might still be a "problem" understanding why this all happens.
Not seeing the Green Arrow isn't a fault... it's actually a feature to help prevent accidents. If you hold down the SHIFT key and then click on your SSMS icon, you'll see several additional selections that you normally wouldn't see including the "Run as Administrator" selection. If you click on that, the Green Arrows and the related "Start, Stop, and Pause" selections will also be available.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 17, 2015 at 7:57 am
Jeff, I am the original poster and wanted to say thank you for your post.
Again I have read all responses that have occurred over the last 3 years! But i dont believe that anyone has come up with your solution.
I cant validate it cause we dont have the same configuration anymore but wanted to say thanks.
Adam.
Adam Zacks-------------------------------------------Be Nice, Or Leave
March 17, 2015 at 9:21 am
You bet, Adam. Thank you for the feedback. Sorry I've missed this post over the last 3 years.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 61 through 75 (of 94 total)
You must be logged in to reply to this topic. Login to reply