Menu Close

Configuring Run As Accounts and Profiles in OpsMgr – A SQL Management Pack Example

Using RunAs accounts and profiles is an often poorly understood area of OpsMgr.  As I began to investigate setting this up for the SQL MP, I quickly realized how little I understood it.  Chatting with my PFE peers, I found that while everyone felt they had a “big picture” idea of how to configure it, nobody I talked to really understood all the options and impact.

This post will be a primer on the basics of using RunAs accounts and profiles, and an in depth example of applying them to the SQL Management pack under different typical scenarios.

 

Why do we have RunAs accounts?

 

We use RunAs accounts when we need a Management Pack workflow to run under the credentials of a different account than the default agent action account. 

Generally, the situation is that the default agent action account does not have enough rights and privileges to perform the action that must be taken in the management pack.  This is typical under two scenarios:

  • The application being monitored has its own security model and access list, and does not necessarily share the rights of the Operating System (e.g. SQL Server)
  • The default agent action account is running under a low-priv domain user account, and does not have enough rights for typical management pack operations (rare)

MOST customers I work with use Local System as the default agent action account.  This is also what I always recommend, unless a company has a VERY specific and well thought out reason they can’t do this.  Local System is a low maintenance account on each agent that generally has local administrative rights to that operating system only.  Another alternative is to use a domain user account as the default agent action account, and place this account in the Local Administrators group on the operating system.

In SOME cases, the application being monitored does not allow Local System (or a local admin/domain user account) to have any, or enough rights to the application.  When this happens, we need to use a RunAs account and profile.

 

The SQL Example

 

When SQL 2005 is installed by default, it places some default security to the SQL instance.  BUILTIN\Administrators (Local Administrators Group from the OS) and NT AUTHORITY\SYSTEM (Local System) are automatically added to the SQL Security access list, and provided with SA (SysAdmin) server role access to the instance.

When SQL 2008 is installed by default, it no longer places BUILTIN\Administrators in the SQL security access list.  The install of SQL 2008 now prompts the installer to give SQL a user account, or Group, to grant SA (SysAdmin) rights to.  If installing on a standalone instance, NT AUTHORITY\SYSTEM (Local System) is still granted SA rights.  If installing on a clustered instance, NT AUTHORITY\SYSTEM (Local System) is granted public rights, but not SA.

When SQL 2012 is installed by default – we no longer place BUILTIN\Administrators in the security access list, AND we restrict NT AUTHORITY\SYSTEM to “public” access.

Most savvy SQL customers would always remove the BUILTIN\Administrators login to SQL to harden the SQL security access.   Some will additionally restrict NT AUTHORITY\SYSTEM (Local System) to have public server role only, removing the SA rights.  There are pros and cons to restricting Local System from SA, that isn’t the purpose of this blog post.

 

 

 

Following are the most common scenarios I run into, in the field:

 

Scenario #1.  You use Local System as the default agent action account.

You accept the default SQL permissions, or modify them to ensure that Local System has the “SA” role to the SQL instance.  In this case – the default agent action account has full rights to the Operating System and to SQL.  No other configuration or use of Run-As accounts is necessary.  The SQL MP will discover and monitor the SQL instances.  This is not the most secure scenario, but likely the simplest to manage.

Scenario #2.  You use a Domain User account as the default action account.

This account is a member of the Local Administrators group on the server OS.  This domain user account has been delegated “SA” rights in SQL explicitly, or via group membership.  In this case – the default agent action account has full rights to the Operating System and to SQL.  No other configuration or use of Run-As accounts is necessary.  The SQL MP will discover and monitor the SQL instances.  (Hint – you might consider just using this special account as the default agent action account ONLY for your SQL servers).  This is more secure than scenario #1 above, but is more difficult to manage in some cases.

Scenario #3.  You use Local System as the default action account.

However, the SQL team has restricted the NT AUTHORITY\SYSTEM (Local System) SQL login, and removed the “SA” right.  In this case, the Local System account has full rights to monitor the server OS, however, does not have enough rights to discover and monitor the SQL application.  In this case – we would use a Run-As account(s) to manage access for the SQL workflows only, to execute under this Run-As account.  This account(s) can be created and fully managed by the SQL team.  This is the MOST common scenario.

Scenario #4.  You use a Domain User account as the default action account.

This account is a member of the Local Administrators group on the OS.  It is used by the OpsMgr team as their agent account.  However, the SQL team has restricted or deleted the BUILTIN\AdministratorsSQL login, thereby removing the “SA” right from local admins.  The SQL team will not allow this account, which they do not control, to have any access to SQL.  In this case, the default agent action domain user account has full rights to monitor the server OS, however, does not have enough rights to discover and monitor the SQL application.  In this scenario – we would use a Run-As account(s) to manage access for the SQL workflows only, to execute under this Run-As account.  This account(s) can be created and fully managed by the SQL team.

 

So – in summary – the most common scenarios are:

  • Use a default agent action account that has Local Admin rights to the OS and SA rights to SQL
  • Use a default agent action account that has no rights to SQL and therefore configure RunAs accounts and profiles to gain access to SQL.

 

Moving forward…  in this example, we have decided we want to keep using Local System for the default agent action account, and set up a RunAs account for access to SQL.  At a very high level, this will involve the following:

  • Create a Run As account using a Domain User credential.
  • Associate this Run As account with one or more Run as profiles.

***Note:  In the next example – we will create only ONE Run As account for handling all SQL workflows.  The SQL MP guide has a very in depth (but complicated) set of instructions for setting up multiple groups and accounts and this is really way more complicated than most customers need to mess with.  This RunAs account will be granted Local Admin over the OS and SA (Sys Admin) rights to SQL.  (lower priv can be established, see SQL MP guide if you really need to go there)

 

Step 1:  Create a Run As Account

 

1.  Open the console and browse to Administration pane > Run As Configuration > Accounts.

2.  Create a Run As Account of type = Windows

3.  Give it a good display name, such a “SQL MP Monitoring Account”

4.  Type in the credentials for username, password, and domain.

5.  On the next pane – you are asked about a distribution security option.  You need to choose “Less Secure” or “More Secure”.  This REALLY warrants a good discussion.

Less Secure” just means distribute this credentials to ALL Health Services in the management group.

More Secure” just means distribute this credential ONLY to Health Services that I EXPLICITLY define.

I really don’t like the terminology we chose of “Less Secure”.  I think they were trying to stress that using “more secure” is a better way to ensure that the tightest security model is upheld.  Theoretically, someone could take an agent management machine they had access to, and hack the credential presented until they got the password.  This model has completely changed from SP1, where were distributed the credential anywhere that needed it, automatically.  This presented a risk, because a server admin who didn’t get access to the credential, could theoretically “fake” that he had an application which needed the credential by placing a dummy registry entry, having this class discovered, get the credential distributed, and start trying to hack the credential.  “The new “more secure” absolutely controls the distribution of the Run As credential, and only OpsMgr admins have access to this.

Less Secure really isn’t a valid option.  The reason for this, is that the agent, as soon as it receives a RunAs credential, performs a series of tests to make sure that we can use the Run As credential.  This includes testing for the “Log On Locally”.  If you create a Run As account, and choose “Less Secure” you will immediately get a flood of alerts from all your Domain Controllers, Exchange servers, and any other servers that restrict the Log on Locally right.  In enterprise server environments, this is very typical to remove “Domain Users” or the local “Users” group from this user right via group policy – or to deny “Log on Locally” for service accounts.  This essentially makes “Less Secure” unusable for any practical purpose.

Therefore we WILL be using “More Secure”.  Smile

Now that we have that settled, this means we need to choose the Health Services to distribute the Run As credential to.   Go ahead and finish creating the Run As Account using more secure, then open the properties of the newly created account.  There is a distribution tab:

image

 

Click “Add” and now we can add the Health Service objects we want to send this account to.  In this example – I am sending this credential to all my SQL servers.  Our only option here is to search by name.  If you have a good naming standard – this is fine.  If you don’t… this will be a bit painful initially.  Luckily, I have the term “SQL” in almost all my SQL server names, so this is easy enough for me – I type in “sql” and his search, and add all my SQL servers, one by one.

Click “OK” and you are done.

Behind the scenes – all of these Health Services are notified of a config update – they download their new config and get the new credential.

 

Step 2:  Associate the Run As Account to a Run As profile.

 

Now the fun begins!  Run As Profiles are objects that are shipped within Management Packs.  You don’t create RunAs Profiles in the UI – they will come delivered with the MP that needs them.  Each workflow or module in an MP can *optionally* attempt to load a Run As Profile (if it is associated with a Run As Account for that system).  If the Run As Profile is not associated with anything (default) then the workflow will run under the Default Agent Action Account credentials, like any other workflow.

Here is an XML example from the SQL MP:

The monitor which inspects SQL Service pack version is below:

<UnitMonitorType ID=”Microsoft.SQLServer.2008.ServicePackVersion” Accessibility=”Internal” RunAs=”SQL!Microsoft.SQLServer.SQLProbeAccount”>

Note the highlighted part in yellow.  This instructs the workflow to try and run this workflow as the “SQLProbeAccount” profile if it is associated for this HealthService.

So… Workflow tries to load a profile > Profile is associated with a Run As account > Run As account contains a credential for execution.

What you will see – if a new MonitoringHost.exe process will spin up, and execute any workflows that need to run under this credential.

Make sense?

 

Ok – let’s associate!

 

The SQL MP includes 3 Run As profiles.  These are:

  • SQL Server Discovery Account
    • Used for discoveries and discovery based datasources
    • In the XML – referenced as: “SQLDiscoveryAccount”
  • SQL Server Monitoring Account
    • Use for monitoring workflows and monitoring based datasources
    • In the XML – referenced as: “SQLProbeAccount”
  • SQL Server Default Action Account
    • Used to elevate Monitoring tasks in the console.
    • Used for when the default agent action account is extremely low priv (rare)
    • In the XML – referenced as: “SQLDefaultAccount”

There are different workflows in the SQL MP’s which will attempt to use these profiles.  What I typically recommend, is to just come up with a single RunAs account for access to SQL, and then use that same account for all profiles.

 

Let’s start with the “SQL Server Discovery Account” Profile.  (Oh how I WISH MP developers would name a PROFILE with the WORD “Profile” instead of “Account”…. but I digress)

1.  Open the Properties of this profile.  On the Run As Accounts page, click “Add”.

2.  Select your “SQL MP Monitoring Account”.

3.  Now we have to choose “All targeted objects” or “A selected class, group, or object”.  This warrants yet another deep dive discussion.  Smile

image

 

 

Normally – the MP Author should let you pick “All targeted objects” and you can be on your way.  Choosing “All Targeted Objects” means that anywhere the profile is associated with a workflow – load and use the defined account (SQL MP Monitoring Account) on all systems.

It is a best practice for all management packs with a discovered class to use a “seed” discovery.  This discovery targets ALL agents (or servers, or operating systems) and runs a lightweight registry discovery.  Then, from the instances discovered in the “seed” discovery, you can target that new class, with your more in-depth role/application based discoveries.  Then – a secondary best practice – is to NEVER load a RunAs Profile against your seed discovery.  This is covered here:  http://social.technet.microsoft.com/wiki/contents/articles/worst-practice-adding-a-run-as-profile-to-your-seed-discovery.aspx 

In previous SQL MP’s – they lacked a seed discovery, and therefore it was more complicated to configure RunAs.  However, in this version, there is a seed discovery class that does not use RunAs, so we can now choose “All Targeted Objects”  Yay!

If you have a very complex environment, you could create groups of SQL computers and target those groups or classes much more granularly here, if necessary.

 

  

Next – let’s examine the SQL Server Monitoring Account profile.

 

1.  Open the Properties of the SQL Server Monitoring Account profile.  On the Run As Accounts page, click “Add”.

2.  Select your “SQL MP Monitoring Account”.

3.  Now we have to choose “All targeted objects” or “A selected class, group, or object”.

Since ALL workflows that leverage the SQL Server Monitoring Account profile are targeting a class that is a SQL specific discovered class, we can use “All Targeted Objects”.

 

image

4.  Done!

 

 

Last – let’s examine the SQL Server Default Action Account profile.

 

This is a new Run As profile that showed up with Version 6.1.314.36 of the SQL MP.  A deeper analysis shows that this profile is primarily used for:

  • Tasks
  • Monitoring workflows that don’t need any special rights for the datasource used (event log, perfmon, scripts that don’t access SQL)
  • Write actions

Technically – this profile *shouldn’t* be needed.  If you didn’t associate it, we would use the default agent action account to handle all of these tasks, and this would work fine *except* for the tasks.  If you didn’t need or want to use the Tasks in the MP with elevated rights – then I’d lean toward ignoring this profile and leaving it un-associated.  If you DO need to use, and elevate the tasks provided in the MP to use a special account – then configure this profile:

 

 

Review:

 

Let’s take a break and review.

  • We need to use a Run As account whenever we need a Management Pack workflow to run under the credentials of a different account than the default agent action account.
  • The Run As account is the credential (username and password)
  • The Run As account needs to be distributed to Health Services (More Secure and a Less Secure)
  • Discoveries, Rules, Monitors (and their associated data sources) can be configured to use a Run As Profile.
  • For a Run As profile to work, it must be associated correctly with a Run As account.
  • There is a lot of flexibility in how we can associate the account to the profile.

 

Close – but no cigar?

 

So – the example above will work really well for customers, where all their SQL servers are tightly secured, and where all their SQL servers are managed by the same SQL team, in the same trusted domain, and can use the same SQL Run As Account.  This allows success for a large number of environments.

But – what if the corporate SQL team only “owns” or supports half of the SQL servers in the management group, while the other SQL servers are owned and supported by various application teams?

What if we want to monitor ALL SQL servers in the management group, but need to use different Run As accounts depending on their domain, or support team?  Maybe for some SQL servers, we don’t want to use any Run As accounts at all, and just use the default action account?  Read on:

 

Scenario #5   You use Local System as the default action account.

Contoso.com is a global IT services firm.  Their OpsMgr Management Group has about 500 SQL servers discovered, a mix of SQL 2005 and SQL 2008, with a handful of SQL 2008R2 instances.  Their corporate IT SQL team manages 400 of these SQL instances.  The remaining 100 SQL servers are not supported by the corporate SQL team, they are one-off SQL instances which are specific to applications, and managed by the application owners.

Of the 400 SQL servers supported by the corporate IT SQL team, 20 of these are in a highly secured domain disconnected from the corporate IT domain.

The goal is to develop a Run As strategy which will accommodate all SQL servers to be monitored.

All agents will be deployed as Local System for the default agent action account, as that is the standard for the Enterprise Monitoring Team.

All SQL security settings on the 400 corporate IT SQL team servers has been hardened and standardized.  The SQL security settings for the 100 application owners SQL instance  is largely unknown.  Some could be hardened, but it is assumed most will be left to default settings performed at the installation.

 

To configure the SQL MP and Run As – we will use a very similar plan to what we did above.  However, we will need to be more precise in two areas:

  • Run As account distribution
  • Use Groups for Run As profile association.

We have three unique scenarios – so we will have three Run As accounts to create:

  • Corp IT SQL Run As account
  • Corp IT SQL Run As account for the high security domain
  • Non-Corp IT SQL team (Application owners) Run As account

We will also need to create 3 groups.  We will use these groups for the Run As profile associations, so different computers will use the correct Run As accounts.  These groups can contain Windows Computer objects, or DBEngine objects.  The Windows Computer object Hosts/Contains all other application classes, that is why you can use this one for your groups.  Alternatively – you can use Database Engine objects if you prefer…. as the DBEngine Hosts/Contains almost all the SQL classes in the MP that leverage a Run As profile.

  • Corp IT SQL Computers
  • Corp IT SQL Computers High Security
  • Non Corp IT SQL Computers

 

Now – the steps will go something like this.

1.  Create the three RunAs accounts just like we did above.  Ensure each account will have Local Administrator and SQL SysAdmin rights over the correct OS and instance.

2.  Distribute the correct Run As accounts to the appropriate Health Services.  For Non-Corp IT SQL instance, we will attempt to use the Default Agent action account to monitor SQL.  If Local System does not have enough rights to SQL, we will then distribute and associate our RunAs account, and tell the application owner to make our special Run As account a local admins and SQL admin if they want their SQL server monitored.

3.   On the Run As Profile Associations – choose “Group” instead of “Class” this time.  Select the group from the object picker.

image

 

Here is how it will look when complete:

image

 

Now – we simply can make sure the right computers/engines are members of the appropriate group.  If you want a Non-Corp SQL instance to try and use the default agent action account for SQL monitoring – just don’t distribute the Run As account, and don’t add them to the “Non-Corp IT SQL Computers” group.  Then they will have NO association, no credential, and will try local system.  If this fails to work – you can simply have a standard document for the application owner for how to configure their SQL server for proper monitoring.  This takes the burden off the OpsMgr administrator.

 

The thing to keep in mind here – is the profile association works just like overrides.  More specific wins.  So you could have the group associations just like above, but if you had a special one-off instance that needed its OWN run as account – you can associate that specific DB Engine to a very specific Run As account, and it would take priority over the conflicting group association.

 

Conclusion

 

I hope this helps provide more answers than it does questions.  This is just one example of how to use the Run As in OpsMgr 2007 and 2012.  Other strategies could be formed.  Keep in mind – usually the simplest solution is best, so don’t over-complicate things.  Make a strategy that is the easiest to maintain, while providing good security separation.

Additionally – this example above is not the “most secure” configuration…. because we assumed our Run As account would have SA rights to SQL.  That is not technically required.  The SQL MP guide does a good job of documenting the minimum instance and database rights needed to grant a Run As account so it can fully monitor SQL.  That said – the Run As account and profile configuration (the focus of this article) will not be any different if you further restrict SQL rights – Run As will work exactly the same way.

 

 

***Thanks to many conversations with Jimmy Harper, Jesse Harris, Russ Slaten, Tim McFadden, and Bonnie Feinberg for assisting me with the data provided.

3 Comments

  1. Guy_In_IT

    Hi Kevin,

    We have an issue which where using this method does not work if the database is on an smb fileshare. We get the following error which seems to occur on lots of our databases.

    ==============
    Event ID: 4001. Management Group: MGMTGROUP. Script: GetSQL2012DBFileGroupFreeSpace.vbs. Version: 7.0.7.0 : The next errors occurred:
    Error occurred during free space calculation for (database:’master’;data_space_id:1)
    Error Number: 76
    Description: Path not found
    Instance: InstanceName

    Cannot get FreeSpace of SMB shared ‘\\sharelink’ (‘\\sharelink\master.mdf’)
    ==============

    It gives us this error for every different database on each server. Do we have to revert to the old method of RunAs profiles with this? Or should your method work?

    • Kevin Holman

      I don’t know if this is a RunAs account issue, or just a MP configuration issue. Since you are using the latest SQL management pack, have you considered opening a support case with Microsoft on this?

  2. Nihil

    Hi Kevin,

    I am trying to create SQL Dashboard but it seems to be not working

    I have created Groups for database, Sql Servers i can see all the information but when i try to add groups to dashboard nothing is coming up .

    Can you please guide me?

    Thanks
    Regards
    Nihil

Leave a Reply

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