image             image

 

Over the years I have written many articles dealing with RunAs accounts.  Specifically, the most common need is for monitoring with the SQL MP.  I have explained the issues and configurations in detail here:  Configuring Run As Accounts and Profiles in OpsMgr – A SQL Management Pack Example

 

Later, I wrote an automation solution to script the biggest pain point of RunAs accounts:  distributing them, here:  Automating Run As Account Distribution – Finally!  Then – took it a step further, and built this automation into a management pack here:  Update-  Automating Run As Account distribution dynamically

 

Now – I want to show a different approach to configuring monitoring for the SQL MP, which might make life a lot simpler for SCOM admins, and SQL teams.

 

What if I told you – there was a way to not have to mess with RunAs accounts and the SQL MP at all?  No creating the accounts, no distributing them, no associating them with the profiles – none of that?    Interested?   Then read on.

 

The big challenge in SQL monitoring is that the SCOM agent runs as LocalSystem for the default agent action account.  However, LocalSystem does not have full rights to SQL server, and should not ever be granted the SysAdmin role in SQL.  This is because the LocalSystem account is quite easy to impersonate to anyone who already has admin rights to the OS.

We can solve this challenge, by introducing Service SID’s.  SQL already uses Service Security Identifiers (SID’s) to grant access for the service running SQL server, to the SQL instance.  You can read more about that here:  https://support.microsoft.com/en-us/kb/2620201

Service SID’s were introduced in Windows Server 2008 and later.

 

We can do the same thing for the SCOM Healthservice.  This idea was brought to me by a fellow MS consultant – Ralph Kyttle.  He pointed out, this is exactly how OMS works to gather data about SQL server.  We have an article describing this recommended configuration here:  https://support.microsoft.com/en-us/kb/2667175

 

Essentially – this can be accomplished in two steps:

  1. Enable the HealthService to be able to use a service SID.
  2. Create a login for the HealthService SID to be able to access SQL server.

 

That’s it!

This creates a login in SQL, and allows the SCOM agent to be able to monitor SQL server, without having to maintain another credential, deal with password changes, and removes the security concern of a compromised RunAs account being able to access every SQL server in the company!  No more configuration, no more credential distribution.

 

I even wrote a Management Pack to make setting this initial configuration up much simpler. 

*** Updated 7-13-2016 for MP version 6.7.2.0

 

Let me demonstrate:

 

First, we need to ensure that all SCOM agents, where SQL is discovered – have the service SID enabled.  I wrote a monitor to detect when this is not configured, and targeted the SQL SEED classes.  For each SQL version, there is an Addendum MP which shows the SEED classes:

 

image

 

image

 

This monitor will show a warning state when the Service SID is not configured for any agent where we discover an instance of a SQL SEED class.

 

 

The monitor has a script recovery action, which is disabled by default.  You can enable this and it will automatically configure this as soon as SQL is detected, and will restart the agent.

 

image

 

Alternatively – I wrote two tasks you can run – the second one configures the service SID, but will wait for the next reboot (or service restart) before this actually becomes active.  The first task configures the service AND then restarts the agent Healthservice.  You can multi-select items in this view and run against multiple agents, making this one-time configuration easy.

 

image

 

Here is what it looks like in action:

 

image

 

So – once that is complete – we can create the login for SQL.

In the Addendum MP for each SQL version – there is a state view for the DB engine.  If you switch to this view, or any Database Engine view – you will see two new tasks show up which will create a SQL login for the HealthService.  One creates the login and assigns it the SysAdmin role to the instance.  The other creates the login and configures the login for Low Priv mode.  You just need to choose whichever you want to use for your organization.

 

image

 

If you run this task, and don’t have rights to the SQL server – you will get this:

 

image

 

Have your SQL team run the task and provide a credential to the task that will be able to create a login and assign the necessary SysAdmin role to the service:

 

image

 

Voila!

 

image

 

What this actually does – is create this login on the SQL server and set it to SysAdmin role:

 

image

 

All of these activities are logged for audit in the Task Status view:

 

image

 

To further assist with this configuration, I added two additional monitors:

 

The first monitor turns unhealthy if we cannot connect to SQL at all:

image

 

The second monitor turns unhealthy if we CAN connect to SQL, but we detect that the login for “NT ServiceHealthService” does not have the “SysAdmin” role.  You should use this monitor if you are granting the SysAdmin role to the Healthservice, and you should disable it if you are using Lowest Priv.  It specifically checks to see if the Healthservice login is configured as a SysAdmin:

image

 

None of the monitors generate alerts by default, to limit adding noise to SCOM.  If you want alerting you can enable that in the MP and configure it as you wish.

 

***NOTE:  These monitors run every 4 hours by default, so after making the changes to add the NT ServiceHealthservice to SQL, it will take that long before the monitors change state, unless you bounce the health service on the agent to speed that up.  So be aware.

 

Now – as new SQL servers are added over time – the Service SID can automatically be configured using the recovery, and the SQL team will just need to add the HealthService login as part of their build configuration, or run this task one time for each new SQL server to enable it for monitoring.

 

I find this to be much simpler than dealing with RunAs accounts, and it appears to be a more secure solution as well.  I welcome any feedback on this approach, or for my Management Pack Addendum.

 

I have included my SQL RunAs Addendum MP’s to be available below:

  • Version 6.6.4.0 – Original release of the addendum MP’s.
  • Version 6.7.2.0 – Update
    • Added support for SQL 2016
    • Added additional monitors to check for ability to connect to SQL and sysadmin role check
    • Removed any alerting by default.
    • Added new task to configure Healthservice login for LOW PRIV to SQL
    • Added folders, and state views to ease configuration and running tasks

 

 

https://gallery.technet.microsoft.com/SQL-Server-RunAs-Addendum-0c183c32