Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Why is SQL Server Configuration Manager Missing Services


By:   |   Last Updated: 2011-09-08   |   Comments (8)   |   Related Tips: More > 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.

The first command was to get the current output of permissions. Then I added in "(A;;CCLCSWLOCRRC;;;SU)" right before the "S:" to give the service logon user permission to see the SQL Server service. You should get [SC] SetServiceObjectSecurity SUCCESS after hitting enter. Go into SSCM and hopefully you see all your services again, as I did. You should only have to do this on the SQL Server Database Engine service, posisbly each one if you have multiple instances. You want to use the Service Name in the command, you can execute this simple PowerShell command to grab those for SQL Server: Get-Service *sql* | Select Name.
Next Steps


Last Updated: 2011-09-08


next webcast button


next tip button



About the author
MSSQLTips author Shawn Melton Shawn Melton started his IT career in 2002 and has experience working with SQL Server 2000, 2005, 2008, and 2008 R2.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, February 05, 2019 - 9:32:56 AM - NP123 Back To Top

Thanks this worked for me on SQL 2016!


Thursday, March 31, 2016 - 10:10:46 AM - Shawn Melton Back To Top

@Lupe,

I don't see why it would not since the fix I used itself is not dependent upon the version of SQL Server, but the sc command should work on Window Server 2003 and up. I am not aware of any chanages made to it in 2012 OS.


Wednesday, March 30, 2016 - 10:53:50 PM - Lupe Garcia Back To Top

 Could I try to apply this same fix for SQL 2012?

 


Sunday, October 18, 2015 - 10:52:16 AM - Afzal Back To Top

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Shared Memory Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider)

 

------------------------------

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476

 

------------------------------

Error Number: 2

Severity: 20

State: 0


Wednesday, April 08, 2015 - 8:20:49 AM - Mike DeFehr Back To Top

Thanks!  I was pretty baffled by this on a Win2008R2/SQL2012 installation, but this worked like a charm


Thursday, December 12, 2013 - 7:55:38 AM - Sagar Gulati Back To Top

Hello,

I am not able to see Anaylsis services in my SSCM, i had follow all below metion steps and completed sucessfully

SDSHOW is "D:(A;;CCLCSWRPWPDTLOCRRC;;;SY)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;BA)(A;;CCLCSWLOCRRC;;;IU)(A;;CCLCSWLOCRRC;;;SU)S:(AU;FA;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;WD)"

 

Still I am not able to see Analaysis services In SQL server configration manager, but i able to see OLAPservices in Powershell using Get-Service *sql* | Select Name.

Any Help on this is very appricated

 

Thanks,

Sagar Gulati


Tuesday, March 12, 2013 - 3:38:43 AM - hanumanthareddy Back To Top

i am unable to login to server becuse there is no sql services in sql server configuration manager, Thanks in Advances.

version: sql server 2008.

Thanks & Regards.

Hanumantha Reddy.


Thursday, September 08, 2011 - 2:38:17 AM - Abhi Back To Top

Good Troubleshooting


Learn more about SQL Server tools