Allowing SCOM to Monitor SQL with Local System

  • [アーティクル]

Configuring SQL Monitoring with Local System

In previous versions of SQL, Local System had access to SQL (unless removed by administrators, a known best practice).  However, newer versions do NOT grant this access out-of-the-box.  To allow the SCOM agent running under Local System to monitor SQL (engine, instances, and DBs) you need to make the following changes.

  1. In SQL Server Management Studio, create a login for “NT AUTHORITY\System” on all SQL Server instances to be monitored on the agent machine, and grant the following permissions (Securables page of the Login Properties page) to the “NT AUTHORITY\System” login:
    1. VIEW ANY DATABASE
    2. VIEW ANY DEFINITION
    3. VIEW SERVER STATE
  2. Create a NT AUTHORITY\System user that maps to the NT AUTHORITY\System login in each existing user database, master, msdb, and model. By putting user in the model database, it will automatically create a NT AUTHORITY\System user in each future user-created database. You will need to manually provision the user for attached and restored databases. (Note: you can do this via the User Mappings page on the instance login created in step 1.  Simply check the DBs.)
  3. Add the NT AUTHORITY\System user on msdb to the SQLAgentReaderRole database role.
  4. Add the NT AUTHORITY\System user on msdb to the PolicyAdministratorRole database role.

Note: This won't necessarily allow you to run SQL tasks.  See the MP Guide for details on the permissions required for that.

See also https://blogs.technet.com/b/kevinholman/archive/2013/10/24/opsmgr-sql-mp-version-6-4-1-0-capabilities-and-configuration.aspx

Comments

  • Anonymous
    October 28, 2015
    Hey Nicole, excellent suggestion. I'm going to test this in my environment soon. I was wondering if you could shed some light on why the MP needs PolicyAdministratorRole on the MSDB?  My SQL DBA is asking and the MP guide doesn't explain any of the requirements.

  • Anonymous
    November 22, 2015
    Blake,   First off, I am not a SQL expert :)  However, I believe that access is required to monitor the custom User Policies.  "Members of the db_ PolicyAdministratorRole database role can perform all configuration and maintenance activities on Policy-Based Management policies and conditions. For more information, see Administer Servers by Using Policy-Based Management." see technet.microsoft.com/.../ms189121(v=sql.110).aspx and msdn.microsoft.com/.../bb510667.aspx Below are some of the items for monitoring custom user policy (examples from SQL 2012 MP, but exist in all 2008+).      <DisplayString ElementID="Microsoft.SQLServer.2012.DatabaseUserPolicyAggregateMonitor">          <Name>Database Extended Health State</Name>          <Description>Database Extended Health Aggregate State monitor</Description>        </DisplayString>        <DisplayString ElementID="Microsoft.SQLServer.2012.DatabaseErrorUserPolicyStateMonitorRollup">          <Name>Database Critical Policies</Name>          <Description>Database Health Critical Custom User Policies State monitor</Description>        </DisplayString>        <DisplayString ElementID="Microsoft.SQLServer.2012.DatabaseWarningUserPolicyStateMonitorRollup">          <Name>Database Warning Policies</Name>          <Description>Database Health Warning Custom User Policies State monitor</Description>        </DisplayString>        <DisplayString ElementID="Microsoft.SQLServer.2012.DBErrorUserPolicyStateMonitor">          <Name>Database Health Policy</Name>          <Description>Two state monitor with 'Error' critical state used particularly for reflecting state of Custom User Policies which have Database as Facet and one of the predefined error categories as Policy Category.</Description>        </DisplayString>        <DisplayString ElementID="Microsoft.SQLServer.2012.DBWarningUserPolicyStateMonitor">          <Name>Database Health Policy</Name>          <Description>Two state monitor with 'Warning' critical state used particularly for reflecting state of Custom User Policies which have Database as Facet and one of the predefined warning categories as Policy Category.</Description>        </DisplayString> There is a good chance this access isn't needed if you don't have any custom user policies, but I would ensure you talk to your DBAs and ensure they don't use custom policies now and have no plans to in the future.   Thanks, Nicole

  • Anonymous
    January 03, 2017
    Hi Nicole,Since the publication of your article, Kevin Holman has posted a way that seems preferable to yours, if only because of the reduced administrative load... https://blogs.technet.microsoft.com/kevinholman/2016/08/25/sql-mp-run-as-accounts-no-longer-required-2/Regards, Gerald

    • Anonymous
      January 04, 2017
      Gerald, yes that is an alternate method that may work better for you. It will depend on who supports SQL and what controls/restrictions they have in place.