Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. How to configure SQL Server 2012 to allow for System Center Advisor monitoring

How to configure SQL Server 2012 to allow for System Center Advisor monitoring

View products that this article applies to.

This article applies to the following product:
  • Microsoft SQL Server 2012

↑ Back to the top

Summary

The System Center Advisor health service runs under the Local System account. In SQL Server versions SQL Server 2008 R2 and earlier, the Local System account was enabled as a login by default and was a member of the sysadmin server role. Starting with SQL Server 2012, the Local System login is not part of the sysadmin server role anymore. As a result when you install System Center Advisor, it will not be able to monitor the SQL Server 2012 instance completely and not all rules can generate alerts.

↑ Back to the top

More Information

In order for System Center Advisor to discover and generate alerts for a SQL Server 2012 instance, you need to run the following script in the SQL Server Management Studio. You need to be logged in as a sysadmin in order for this script to work.

USE [master]
GO
/****** Add a login in SQL Server for the service SID of System Center Advisor HealthService ******/
CREATE LOGIN [NT SERVICE\HealthService] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO
/****** Add the HealthService Service SID login to the sysadmin server role ******/
ALTER SERVER ROLE [sysadmin] ADD MEMBER [NT SERVICE\HealthService]
GO

Note After executing the script to add the login, you will need to restart the HealthService for the new settings to take effect. The display name for the HealthService is "System Center Management".


Security Enhancements (Database Engine)

The following table provides more information about the products or tools that automatically check for this condition on your instance of SQL Server and on the versions of the SQL Server product against which the rule is evaluated.
Rule softwareRule titleRule descriptionProduct versions against which the rule is evaluated
System Center AdvisorSQL Server security configuration does not allow System Center Advisor to function properlySystem Center Advisor agent connects to SQL Server and checks if the login used for the current connection has sysadmin role membership. Advisor generates an alert if it determines that the login used is not a member of the sysadmin role. This alert will be generated if neither the Local System nor the HealthService SID accounts are members of the sysadmin server role. Review the details that are provided in the “Information Collected” section of the advisor alert, and follow the resolution steps discussed in this article. SQL Server 2012


↑ Back to the top

Additional information only applicable for SCOM installs

System Center Advisor can be used by installing System Center Advisor directly, or by installing System Center Operations Manager (SCOM). After SCOM is installed, open a command prompt with elevated privilege, and run this command:
sc sidtype HealthService unrestricted
This command creates an appropriate service SID for the SCOM service. Then, you open Microsoft SQL Management Studio (SSMS), connect to the SQL instance and run the following script:
USE [master] 

GO 

/****** Add a login in SQL Server for the service SID of System Center Advisor HealthService ******/ 
CREATE LOGIN [NT SERVICE\HealthService] 
FROM WINDOWS 
WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english] 

GO 

/****** Add the HealthService Service SID login to the sysadmin server role ******/ 
ALTER SERVER ROLE [sysadmin] 
ADD MEMBER [NT SERVICE\HealthService] 

GO
The script grants the service SID the required access to SQL Server. 

↑ Back to the top

Keywords: kbtshoot, kb

↑ Back to the top

Article Info
Article ID : 2667175
Revision : 1
Created on : 1/7/2017
Published on : 7/17/2014
Exists online : False
Views : 613