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
SYMPTOMS
In Microsoft SQL Server 2005, you open SQL Server Configuration Manager. In the SQL Server Configuration Manager window, you encounter one of the following problems when you click SQL Server 2005 Services:
* No service is listed.
* Some services are not listed.
When you perform operations in the SQL Server 2005 Surface Area Configuration window, you receive the following error message:
No SQL Server 2005 components were found on the specified computer. Either no components are installed, or you are not an administrator on this computer. (SQLSAC)
CAUSE
This problem occurs because the NETWORK SERVICE account has insufficient permissions to query the information about the SQL Server 2005 services.
This problem may occur after you apply some security policies to update the security settings for SQL Server 2005 services. In particular, this problem may occur after you apply a Group Policy to update the security settings.
RESOLUTION
To resolve this problem, make sure that the NETWORK SERVICE account has the READ permission to query the information about the SQL Server 2005 services. To do this, follow these steps:
1. Click Start, click Run, type mmc, and then click OK.
2. In the Console window, click Add/Remove Snap-in on the File menu.
3. In the Add/Remove Snap-in window, click Add.
4. In the Add Standalone Snap-in dialog box, click Group Policy Object Editor under Available Standalone Snap-ins, and then click Add.
5. In the Select Group Policy Object window, click Browse.
6. In the Browse for a Group Policy Object window, click Another computer, specify the location of the domain controller, and then click OK.
Note If you perform steps 1 through 5 on the domain controller, click This computer, and then click OK.
7. In the Select Group Policy Object window, click Finish.
8. In the Add Standalone Snap-ins dialog box, click Close.
9. In the Add/Remove Snap-in dialog box, click OK.
10. Expand the snap-in that you add, expand Computer Configuration, expand Windows Settings, expand Security Settings, and then click System Services.
11. In the Service Name column, double-click SQL Server (Instance_Name).
12. In the SQL Server (Instance_Name) Properties dialog box, click to select the Define this policy setting check box, select the appropriate startup mode, and then click Edit Security.
13. In the Security for SQL Server (Instance_Name) dialog box, click NETWORK SERVICE under Group or user names.
Note If the NETWORK SERVICE account is not listed, you should add it first.
14. Under Permissions for NETWORK SERVICE, click to select the Read check box in the Allow column, and then click OK.
15. Repeat steps 11 through 15 to add the READ permission to the NETWORK SERVICE account for other SQL Server 2005 services.
MORE INFORMATION
We have a good practice to avoid this problem. When you apply security policies, always make sure that the default discretionary access control list (DACL) for the SQL Server 2005 services is not changed. You can add accounts or groups to the security setting of the SQL Server 2005 services if you want the accounts or groups to have the permission to query the service information.
You can run the following command at a command prompt to extract the information about the default DACL for a SQL Server 2005 service:
sc \\[servername] sdshow SQLService_Name
When you run the SQL Server Configuration Manager tool or the SQL Server 2005 Surface Area Configuration tool, these tools internally create an instance of the ManagedComputer SQL Server Management Objects (SMO) class. These tools iterate through the services collection to obtain the information about the SQL Server 2005 services. When these tools iterate through the services collection, these tools generate the following Windows Management Instrumentation (WMI) queries:
* SELECT * FROM RegServices
* SELECT * FROM SqlService
When these tools generate the WMI queries, the SQL Server Web-Based Enterprise Management (WBEM) provider (Sqlmgmprovider.dll) is loaded into the Wmiprvse.exe process. Then, the SQL Server WBEM provider extracts and processes the information about the services of every instance of SQL Server 2005. The information is about the following SQL Server 2005 services:
* The SQL Server Database Engine service
* The SQL Server 2005 Reporting Services service
* The SQL Server 2005 FullText Search service
* The SQL Server 2005 Agent service
* The SQL Server 2005 Integration Services service
* The SQL Server 2005 Analysis Services service
Finally, the ManagedComputer SMO object returns the list of SQL Server 2005 services to these tools.
The Wmiprvse.exe process in which the Sqlmgmprovider.dll file is loaded runs under the security context of the NETWORK SERVICE account. In the Wmiprvse.exe process, a thread executes the code in the Sqlmgmprovider.dll file. When you run these tools, this thread in the Wmiprvse.exe process impersonates the security context of the user who is running these tools.
Additionally, information about some SQL Server 2005 services is still extracted under the security context of NETWORK SERVICE account. If the NETWORK SERVICE account has insufficient permissions to extract the service information, the information about the SQL Server 2005 services is not present in the result set for the WMI queries that are mentioned previously. Therefore, the ManagedComputer SMO object returns an empty list or a partial list of SQL Server 2005 services to these tools.
REFERENCES
For more information about the ManagedComputer class, visit the following Microsoft Developer Network (MSDN) Web site:
http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.wmi.managedcomputer.aspx (http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.wmi.managedcomputer.aspx)
For more information about QueryServiceConfig function, visit the following MSDN Web site:
http://msdn2.microsoft.com/en-us/library/ms684932.aspx (http://msdn2.microsoft.com/en-us/library/ms684932.aspx)
For more information about WMI, visit the following MSDN Web site:
http://msdn2.microsoft.com/en-us/library/aa394572.aspx (http://msdn2.microsoft.com/en-us/library/aa394572.aspx)
APPLIES TO
* Microsoft SQL Server 2005 Standard Edition
* Microsoft SQL Server 2005 Workgroup Edition
* Microsoft SQL Server 2005 Developer Edition
* Microsoft SQL Server 2005 Enterprise Edition
