Configure SQL Monitoring Low-Privilege environment for SCOM (Part 1)

Part of my job as Cloud Consultant to configure System Center 2012 R2 Operations Manager environments including Management Packs. The one which causes most of the headaches is the Management Pack for SQL servers. The reason is that Microsoft changed the default permissions for LocalSystem when they release SQL Server 2012. Before that, LocalSystem (other known as NT AUTHORITY\SYSTEM) had “sysadmin” rights by default after the installation of the software on all the instances. During this release MS removed the LocalSystem account from the “sysadmin” group so it does not have rights to access the database.

95% of SCOM installation uses the “LocalSystem” account as the “Agent Action Account” which means every task run by the Microsoft Monitoring Agent will use this as service account. Because of lack of the permission all the tasks which was desigened to discover or monitor SQL components will fail, because the account it uses to run has no rights to access this information. To give us a solution Microsoft created 3 RunAs accounts (Default Action Account, Discovery Account and Monitoring Account) in the SQL Management Pack for System Center R2 Operations Manager and configured the discoveries, monitors, rules and tasks to use the appropriate one when they run on the agent. Completing this the Management Pack guide contains information about the necessary permissions as well.

One of the solution is to configure low-privilege environment where the service accounts get the least permission necessary. Because it does not contain user accounts or groups with administrative privileges this solution is preferable for many customers who uses strict security policies. In this solution the configuration needs to take place at 3 different places:

  • Windows Basic Permissions (Part1)
  • SQL Instance Permission (Part 2)
  • SCOM 2012 R2 RunAs account and Profile configuration (Part 3)

I had a few occasion when I had to configure the whole SQL monitoring scenario for several servers. During this process I realised 2 things about doing this task manually:

  • It is extremely time consuming
  • There is a high possibility for configuration mistakes

Because of these I decided to automate the process and create a solution using PowerShell. In the same way and the process itself I separated the solution to 3 separate part as well. In this blog entry I would like to give you my solution for the “Windows Basic Permissions” section. This part of the process contains several smaller tasks which are responsible to configure different part of the Windows Operating System. The solution is only tested with PowerShell 4.0 or higher on Windows Server 2012 R2+ and with standalone SQL. It may work on older operating system with lower level of PowerShell, but it has not been tested. For clustered environment there are extra steps which are not included in this solution yet. The task consists of the following steps:

  • Adding specified Active Directory objects to the specified Windows local groups
  • Grant “Allow logon locally” rights to the specified Active Directory objects using Security Policy
  • Grant “READ” permission to the specified Active Directory object on the required registry entries
  • Grant the necessary permission on the required WMI classes
  • Modifying the rights on the C:\Windows\Temp folder

Because the Security Policy is mostly handled centrally by Group Policy Object from Active Directory, this part of the task has been left out from the script. Just make sure, a proper GPO is applied on the server. So, the PS package for this solution consist of 2 PS1 files for the functions of the script and 1 XML file which holds the configuration information. I added a guide as well which gives the necessary information to correctly use the package. The Part1 of the complete package is available HERE

2 thoughts on “Configure SQL Monitoring Low-Privilege environment for SCOM (Part 1)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.