SQL MP Challenge: Run As Accounts & - Profiles.

Issue
As stated in this blog posting of Kevin Holman, SQL Server 2012 instances require additional attention. Otherwise SCOM can't monitor it.  Main reason here is that the Local System account (NT AUTHORITY\SYSTEM) is excluded by default from SA (Sys Admin) permissions in SQL Server 2012.

When following the earlier mentioned blog posting you'll be just fine and SCOM will monitor the SQL Server 2012 instances as well. But how about a situation where you've about 50 SQL server instances based on SQL Server 2008x and only a few more which are running SQL Server 2012? In this case you've to be more specific so that only those SQL Server 2012 are using the special (AD) account and excluding the other none SQL Server 2012 instances.

In this scenario it turns out to be a lot harder to get things right. When they aren't you end up with many Alerts all about the credentials.

Example
Suppose you create in AD a dedicated account for monitoring the SQL Server 2012 instances. And this account is used for a Run As Account (e.g. SQL Account) for discovering and monitoring SQL Server 2012 instances. In this example the account is only distributed to the health service running on the server hosting that SQL Server 2012 instance:

This Run As Account is used by two of the three SQL Run As Profiles (SQL Server Discovery Account & SQL Server Monitoring Account). For both Run As Profiles the Run As Account will be used to manage All targeted objects:

However, SCOM doesn't like this kind of configuration and soon the dreaded Alerts start flowing in:

System Center Management Health Service Credentials Not Found Alert Message:

An account specified in the Run As profile "Microsoft.SQLServer.SQLDiscoveryAccount" cannot be resolved. This condition may have occurred because the account is not configured to be distributed to this computer. To resolve this problem, you need to open the Run As profile specified below, locate the account entry as specified by its SSID, and either choose to distribute the account to this computer if appropriate, or change the setting in the profile so that the target object does not use the specified account. Note: you may use the command shell to get the Run As account display name by its SSID.

Cause
Even though all these Alerts aren't a good thing, SCOM is doing exactly as configured:

1. You created a Run As Account for monitoring the SQL Server 2012 instance;
2. This account is ONLY distributed to one or more health services, running on servers hosting SQL Server 2012 instances;
   
3. All other servers running other versions of SQL Server won't get the credentials of this Run As Account;
4. In both SQL Run As Profiles HOWEVER this account is used for All targeted objects;
5. So now ALL workflows related to the SQL MP running on ALL SQL Servers require the credentials as specified in the Run As Account;
6. But that Run As Account ISN'T distributed to all those SQL servers;
7. That's why all the Alerts start coming in...

Close but no cigar
Even though one might think to solve this issue by distributing the SQL Run As Account to ALL systems (Less secure - I want the credentials to be distributed automatically to all managed computers) it won't fly either.

Why you ask? Just keep on reading :).

Even though the Run As Account is AD based, it has only permissions on those specific SQL Server 2012 instances. Not on the other SQL Server 2008x instances. So now discovery and monitoring of the SQL Server 2012 instance will work as intended but the other SQL Server 2008x instances don't know this account at SQL instance level so it will be denied access.

Soon SCOM will raise new Alerts, related to this issue:Run As Account does not exist on the target system or does not have enough permissions

Management Group: XYZ. Script: GetSQL2008DBFilesFreeSpace.vbs : Cannot login to database [XYZ][MSSQLSERVER:XYZ]

Another Alert that might pop up is: Run As Account Cannot Log On Locally. This Alert makes sense as well since the dedicated SQL account doesn't have Log on Locally permissions on all monitored servers, except for the SQL Server 2012 computers (when they're configured properly that is).

The Run As account needs to have the "Log On Locally " right.

Solution
But this situation is to be solved when you follow these steps outlined below. I start at the very beginning of things, creating the account in AD. Reason for this is that I leave nothing out, in order to present to you the COMPLETE solution to this challenge.

1. Create an AD account, used for discovering and monitoring the SQL Server 2012 instances, e.g. _SVC_SCOM_SQL2012_Monitor;
2. This account will be only member of Domain Users. Give it a very strong password and configure the account that it doesn't has to modify the password after the first logon and that the password will never expire;
3. On the servers hosting the SQL Server 2012 instances to be monitored:
1. Make the AD account member of the Local Admin group
2. In SQL, add the AD account and give it Sys Admin permissions.
4. SCOM - Run As Accounts
1. Create a Run As Account (Windows) and use the AD account for it;
2. Set Distribution to More secure - I want to manually select the computers to which the credentials will be distributed
   
3. Select the Health Services related to the servers hosting the SQL Server 2012 instances:
   
5. SCOM - Run As Profiles
1. Modify the Run As Profile SQL Server Discovery Account
1. Select the earlier made Run As Account. At the header This Run As Account will be used to manage the following objects you select the option A selected class, group or object > Select > Object > in the Object Search screen: set the Look for box on Windows Computer. Now search for the Windows Computer(s) which are hosting the SQL Server 2012 instances. Add them one by one (Add button). When you're back in the main screen of the Run As Profile it looks like this:
   
2. Save the configuration.
6. Modify the Run As Profile SQL Server Monitoring Account
1. Follow the same steps as described in Step 5.
7. Now everything will work as intended:
1. The SQL Server 2008x instances will be monitored by the Local System account and not raise Alerts about the Run As Account for discovering and monitoring SQL Server 2012 instances;
2. The SQL Server 2012 instances will be managed by the dedicated Run As Account.
3. When Alerts do come in, recycle the Health Service cache on those systems. Now those Alerts should be gone. When they come back, go through the Steps 1 to 5 again because changes are you missed something :).