SQL Server Configuration Manager not display services

Question

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.

Answer 1

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.

The steps needed to accomplish this are detailed in the Microsoft Support Article:

Some or all SQL Server 2005 services are not listed in SQL Server Configuration Manager, or you receive a "No SQL Server 2005 components were found" error message when you perform operations in SQL Server 2005 Surface Area Configuration.

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 <server_name> sdshow <service_name>
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 <server_name> sdset <service_name> <concatenated_SDDL_string>
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)

Answer 2

Just make sure you are opening the right SQL Server configuration manager software, if you're opening the wrong SQL Config Manager on your machine it will not show the SQL server service, because you are looking at a viewer and not the actual config manager.

I had the same problem with SQL 2014 and when browsing again for the Config Manager software on my SQL machine, I surprisingly found out there are multiple executables of Config Manager, so I just needed to open the correct one for SQL 2014 and the right Config manager displayed everything as expected

Answer 3

I stumbled into the same scenario. I restarted the WMI service (Windows Management Instrumentation) and it worked!

I generally restart this service whenever I face any issues opening Configuration Manager.