By: Shawn Melton | Updated: 2011-09-08 | Comments (8) | Related: > SQL Server Configurations
Problem
When I open SQL Server Configuration Manager (SSCM) I see no services displayed (or just Integration Services). How can I resolve this quickly? Check out this tip for the solution.
Solution
So if you don't know what I am talking about, this screenshot shows what I get when trying to configure (or just see) the services in SSCM:
When what you need to see is this:
I combed the Internet looking for answers and could not find anything to fix this. If I was getting an error it may have helped point me in the direction of the issue, but I was not. As the first screenshot shows I could open SSCM fine, just not seeing all the services. This may not be the fix in your situation, but it is definitly something to verify just in case. In my situation is what the fix, hopefully it will be for you as well.
I want to first give you a few things I tried in an attempt to verify the issue. WMI is a very cool thing, but it can be difficult to determine when you start having problems with it. I was working with a Window Server 2008 R2 server so I first enabled the WMI trace log. I saw where it was executing the commands, but it did not display any errors. I had verified remote access to WMI object was set correctly, it was. I then tried the WMI commands to manage the services from PowerShell. I created an interactive PowerShell session to my server, using the local Admin account. I then executed the following code with the results shown from PowerShell Plus 4.0 (love this software):
Same results to what SSCM was displaying.
After about a week of trying different phrases in Google and Bing, I was pointed to this KB article, #941823. Although your issue may not be with SQL Server 2005, mine was with SQL 2008 R2, this could pertain to any version you are running. I do not believe, overall, SSCM has changed much since it came out with SQL 2005. As far as I know, it still uses WMI to show you information. The solution in this KB though deals with a group policy in a domain environment. I had servers both in a domain and standalone with this issue. I particularly do not like making a change on servers that don't have the issue, so no grou policy. Under the "More Information" section though I found the "sc" command (Service Controller).
The "sc.exe" utility works with the Service Controller and when working on permissions uses SDDL. This MSDN article, Security Descriptor Definition Language (SDDL), will help you understand what that is a little more. This command is most commonly seen (at least on searches I did) to add and remove services from a Window server. In my situation though I needed to adjust the permissions of the services. You do this with the following syntax: sc [ServerName] sdset ServiceName ServiceSecurityDescriptor.
The thing to note about using the "sdset", that I did not see specifically mentioned, is that it overwrites whatever is already there (it does not append). So if you want to add permissions you need to run the "sdshow" command to get the current permissions first. Then in your sdset command will be the output plus the permission you need to add. Then the reverse, leaving it out, will remove the permission.
sc sdshow MSSQLSERVER
This is the command I executed to get the current permissions on my troubled server. I also ran this on a good server, that was not having the problem. To make it more readable so I could compare it, I broke it down in Notepad. Here is a side-by-side view, with the left being the troubled server and right being the good one.
If you read through the ACE Strings MSDN article, it has the breakdown of the output. I did not want to make any changes on my production servers without first verifying in test. Looking at the screenshot you may notice that ";;;SU" exist on the right, but not the left. The "SU" is the accout SID, read here for more details on those. I took the virtual server I have on my desktop to see if I could duplicate the problem by removing the "SU" permission. As soon as I took it out, I lost the services in SSCM.
So below is the screenshot with the sdshow and then the sdset to add the "SU" permission. As soon as I did this all the services were available in SSCM again.
Next Steps
- Be sure to check out other great tips that are available on MSSQLTips.com. The best way to do this is to become a free member and receive the daily newsletter that has all the latest tips.
About the author
Shawn Melton started his IT career in 2002 and has experience working with SQL Server 2000, 2005, 2008, and 2008 R2.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2011-09-08
