January 12, 2010 at 2:03 am
Hey all,
I built a new SQL 2005 Ent 64-bit cluster yesterday. Standard build, local admin, Windows 2003 enterprise all 64-bit. Service pack 3 for both.
And the same issue (no server state reported from 2008 client). Begining to think its a feature 😉
Adam Zacks-------------------------------------------Be Nice, Or Leave
January 12, 2010 at 2:04 am
The_SQL_DBA (1/11/2010)
Schadenfreude-Mei (1/8/2010)
pankaj.baluni (1/8/2010)
Try to re-install the latest service pack.. just uninsall and re-install the SP.On my desktop machine or the 2005 server?
Doesn't look to me that it is a service pack issue...please do not uninstall any SP's from the server yet..
Thanks..
Thanks. Wasnt going to. 😉
Adam Zacks-------------------------------------------Be Nice, Or Leave
January 12, 2010 at 3:30 am
I have had this situation in the past and every time it was a Windows security issue.
The troubleshooting you have done has in my mind proved that the target server is working OK (no need to apply a service pack) and your account has the rights it needs when running locally on the target server. (It probably has more rights than it needs, as you say you are a member of local admin which is not needed if you have the right windows rights. Also, long gone are the days when local admin rights allowed you access to everything on a Windows box...)
You should check if you have 'Logon over the Network' and 'Remote registry' rights on the target server. Also check if the Remote Registry service is running on the target. Finally you need to grant permissionas to your account or (preferably) Windows group. See http://www.windowsreference.com/security/how-to-disablerestrict-remote-registry-access-in-windows/ for how to do this.
When you have the solution, check with your Windows admin people to see if any of the things needed are controlled by Group Policy Objects (GPOs). If they have a GPO set up that enforces the configuration of any of these items, the permissions wil get reset back to the GPO values at the next GPO refresh (by default every 90 minutes). The GPO refresh can make troubleshooting harder, as you only have a small time window when your changes will be effective. In this situation you will need to get the GPO updated to include the rights you need.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
January 12, 2010 at 3:33 am
PS Also check out http://articles.techrepublic.com.com/5100-22_11-5270774.html to see if access is restricted to specific registry keys.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
January 12, 2010 at 4:34 am
I cannot say anything about SQL Server 2008 or 64/32-bit issues. But the permission required is for the user's Windows account (under which they are running SSMS) to access remotely the WMI data from the server. Membership of local admin usually provides this, so many DBAs who opt for this easy (less secure) option probably never see the problem.
To add a specific account on Server 2000 and Server 2003 go to:
Control Panel >> Administrative Tools >> Computer Management, expand Services and Applications
Select WMI Control, right-click > Properties
Select Security tab, expand Root
Select CIMV2, click Security button
Add the required account (or local or global group)
- by default it will get Enable Account: add Remote Enable
Apply, OK etc to save and close Computer Management.
I just tested this using SSMS 2005, adding my account to a Server 2000/SQL 2000 system where I had a white circle. Once I restarted SSMS to re-read the WMI (I expect there's a polling process somewhere here) I had a Green arrow. For my SQL 2005 servers it's documented as part of the build.
Fiddly, isn't it? As ever, it is probably easiest to grant the permission to a global DBA group, if you have one you routinely add to all your SQL Servers.
January 12, 2010 at 8:30 am
Thanks all for your comments and suggestions. I will give these a go over the coming days and let you know 'if' a reolve is found.
Adam Zacks-------------------------------------------Be Nice, Or Leave
January 14, 2010 at 1:56 am
Hey All,
I have had one of our senior Windows guys go over the servers involved after reading your posts and he has said that everything suggested is setup. Remote registry service is running, my user (and group) are already in local admin (yes too much access but easier then troubleshooting) and i am able to regedit to the clusters and nodes.
Also and i dont know if maybe this has been missed out and forgotton from my first post but I can connect and start/stop services (system state) on all of my 32-bit sql2k5 boxes and sql2k. The only one's i have issues with is 64-bit (and these are clusters). Once again this is from the 2k8 client, no problems with the 2k5 client.
So really i guess until someone who works at MS says 'It works and we have tested it', I'm gonna assume its a bug (or as MS would say 'a feature'.
Cheers for the advise 😉
Adam Zacks-------------------------------------------Be Nice, Or Leave
January 14, 2010 at 3:27 am
I am not any sort of senior Windows guy, and I know I said I can't comment on either SQL2K8 or 64-bit, but I do think the remote registry/ability to Stop/Start services stuff were the wrong targets, and that this is about WMI permissions.
Just to check - did you try the CIMV2 entry?
January 18, 2010 at 3:04 am
Hey Ewan, thanks for your reply. I'm not sure what that is but the fact still remains that if i power up the 2k5 client it works fine!
Something I may not have mentioned people, we have a DR box (for the primary cluster) and it is on the same version and sp as the one we have the issue with (SQL 2005 Enterprise 64-bit) and it works fine.
Key difference here is that our DR box ISNT clustered! Food for thought. 🙂
Adam Zacks-------------------------------------------Be Nice, Or Leave
June 8, 2010 at 9:19 am
I have the same problem, but it has nothing to do with being 2005, clustered, or on a 64-bit OS.
I have both physical and virtual machines exhibiting this behavior (not displaying the actual server status in SSMS). The SQL Server software ranges from 2000-2008.
Most of my servers of all three versions (2000, 2005, and 2008) appear normally. I have one 2008 server, one 2005 server, and two 2000 servers that don't display their status. The 2008 server is clustered (32-bit) but is not the only 32-bit 2008 cluster we have. The 2005 server is physical, and otherwise unremarkable from the other 2005 installations (our servers are imaged; nothing is different aside from the server names and databases on each server). One 2000 server is physical and one is virtual. They are likewise imaged and identical to our other 2000 servers.
I attempted the WMI security fix but that didn't address the problem. Our DBAs are all local admins anyway, and the administrators group is set to full privileges there and in Windows security.
The fix to use 2005 (or 2000) client tools seems to work for previous versions, but it's worth noting that it (clearly) doesn't work for the 2008 server. So as far as workarounds go, there doesn't seem to be one for me.
June 9, 2010 at 6:13 am
I'm using SSMS 2008, connecting to named default instance SQL 2008 cluster. Original registration was just the 'DNSname'. This connection worked except no green arrow.
Created an alias, Name: 'DNSname\instancename' Server: 'DNSname' Port: 1433
Then registered the server as alias name in SSMS.
now have a green arrow.
hope this helps,
ArkWare
January 27, 2011 at 5:53 am
I just solved the same problem on our servers by running (the now depreciated but still accepted) command:
netsh firewall set service RemoteAdmin enable
From a command prompt set to run as administrator
January 27, 2011 at 6:19 am
sgodrich (1/27/2011)
I just solved the same problem on our servers by running (the now depreciated but still accepted) command:netsh firewall set service RemoteAdmin enable
From a command prompt set to run as administrator
Thanks for the reply, but I think the ultimate cause behind this problem still depends on if the login you use to log on to SSMS actually have the service control (start/stop) privileges on the target sql server box.
For example, in my current working environment, my local workstation account (a normal domain user account) is sysadmin to all my sql server intances, but I can't see the service status of any sql server box; however, if I "run as" SSMS with my admin account (a domain user account that have local admin rights on all my sql server boxes), I can see all the sql server services plus all the sql agent services are up and running (or not when they stop).
Bazinga!
January 27, 2011 at 6:57 am
Sounds to me like you need to add your user account as a local admin on the server.
January 27, 2011 at 7:02 am
sgodrich (1/27/2011)
Sounds to me like you need to add your user account as a local admin on the server.
Not an expert on windows, but the same account you connect through SSMS should have the rights to control sql services on the server. Maybe a local admin is an over kill, so please refer to your windows system admin team.
Bazinga!
Viewing 15 posts - 31 through 45 (of 94 total)
You must be logged in to reply to this topic. Login to reply