Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Config: Window Server 2008 R2 Enterprise SP1 / SQL Server 2008 R2 Enterprise (RTM)

I have searched through Google using many different words and phrases. I know this was a common issue to occur with SQL 2005 and most mention doing a command line to re-register the mof file and a few other things.

I'm getting the same issue similar to what everyone else receives: I can only see SSIS service within SSCM.

  • I have checked Wmimgmt.msc permissions and I am an in local Administrator group and that group has Remote Enable. Reference
  • I have enabled WMI trace log (feature in Server 2008 Event Viewer) and see the calls being made to the WMI provider, but no errors showing. Just information messages.
  • I have tried calling the services information through PowerShell WMI command (here) and it only returns SSIS service as well. No error is returned when I execute the $mc.Services | Select Name, DisplayName it just returns SSIS service info and that's it.
  • I've gone through the GP policies and don't see any settings that would restrict me having access to WMI providers.
  • I have permission to "..\Microsoft SQL Server\100\Shared\sqlmgmprovider.dll" file.

I'm officially stumped now. I also checked Microsoft Connect and did not see any thing for this issue. SP1 for SQL Server 2008 R2 has not been applied but I don't see anything in that documentation that would have any effect on this issue.

share|improve this question
    
Copuld this be related to a change to the default discretionary access control list for the sql server services? –  jl01 Aug 3 '11 at 17:16
    
Administrator's group is there, which I am apart of. –  Shawn Melton Aug 3 '11 at 18:27
    
@Shawn Are you able to use another computer with SSCM to remotely connect to this server? Does this behave differently that connecting locally? technet.microsoft.com/en-us/library/ms190622.aspx –  Matt M Aug 30 '11 at 16:46
    
connecting remotely in that manner is not available in my environment. –  Shawn Melton Aug 30 '11 at 17:02

1 Answer 1

up vote 1 down vote accepted

I know of one other thing that you can try. It affects SQL Server 2005 specifically, but might also be useful in your scenario. Essentially, the wmiprvse.exe executable runs under the context of NETWORK SERVICE. SSCM loads the Sqlmgmprovider.dll into this executable under the same security context. Ensure that NETWORK SERVICE has READ permissions to the SQL Services in question.

This link will provide the steps needed to accomplish this. http://support.microsoft.com/kb/941823

To do this in a standalone environment is a bit trickier. Let's try this with SQL Browser first.

  1. Open cmd.exe as an administrator.
  2. Type the following command: sc sdshow
  3. Copy the string returned to your favorite notepad clone, we will need this string to revert back to prior configuration if this does not work. This is our SDDL string. The next step will be our concatenated SDDL string.
  4. Add the following to the end of the string: (A;;CCLCSWRPWPDTLOCRRC;;;NS)
  5. Type the following command: sc sdset
  6. Open SSCM and see if SQL Browser displays as expected.

The two commands run should look something like this:

sc \my-server sdshow SQLBrowser

sc \my-server sdset SQLBrowser D:(A;;CCLCSWRPWPDTLOCRRC;;;SY)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;BA)(A;;CCLCSWLOCRRC;;;AU)(A;;CCLCSWRPWPDTLOCRRC;;;PU)(A;;CCLCSWRPWPDTLOCRRC;;;NS)

I hope this can possibly help,

Matt

share|improve this answer
    
what if the server is standalone, not on a domain? –  Shawn Melton Aug 30 '11 at 19:52
    
@Shawn Edited my answer to provide steps for doing this in a standalone environment. –  Matt M Aug 30 '11 at 20:57
    
I was actually looking at that before I left work. I checked a server with same OS, although SQL 2005, that was not having the issue. Comparing the two servers I found "SU" SID had been removed. Got home and tested it on my lab VM, removed the SU and as soon as it applied all but two services were left in SSCM (Integration and Report). As soon as I added it back, it worked. I would imagine me just adding "NS" would work as well. Thanks. –  Shawn Melton Aug 31 '11 at 0:29
    
I am glad I could help. This functionality, while easy to configure in an AD environment, is a pain in the butt to do in a standalone setup. You could have also used Security Templates. I wish that Microsoft would make this less of a pain to do. –  Matt M Aug 31 '11 at 12:21
    
@Shawn I am curious as to how this may have happened. Is it possible that this computer was once on a domain that had a group policy that was removing the "SU" SID (Service Logon User) from those services, and was not gracefully removed from the domain? –  Matt M Aug 31 '11 at 13:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.