Configuring SCOM 2012 - Part 2

At this stage, there will still be a few permissions here and there that aren't quite right for SCOM, so I wanted to sort these out next.  Firstly, the SCOM server will need access to the Reporting web service on the SQL server, and the firewall config won't currently allow this. 
To ensure I can lock down the rule reasonably well I wanted to see what process was listening on port 80 so ran a netstat -ano | findstr ":80" from the command prompt.

Now many admins will see the PID 4 in the above screenshot and instantly know the answer, but if you're ever looking to tie a port to a process and the PID isn't 4, then the default task manager view actually lists PID's, so the above step with a quick reference in task manager is a very quick way of doing it.

So with the process identified as system, I added a new inbound rule to the Windows firewall to permit inbound connections on port 80, from Machines on 10.0.0.0/24 to the System process.

Next, I noticed I was seeing the error "Logon failed for user (OM Action Account) Reason: Failed to open the explicitly specified database. [CLIENT: Local IP]" occur 8 times every 15 Minutes in the applicaton log of the SQL server.  (Whoever puts together the code for these errors, actually mentioning the name of the "explicitly specified" database might make them a little more useful :).

As a test I decided to give the SCOMAA account db_datareader and db_datawriter over the OperationsManagerDW DB.  After this, the errors dropped to 6 every 15 Minutes.  OK, so what is probably happening here is there are 4 databases that something using the SCOM Action Account is trying to access every 15 Minutes, the 8 errors are due to each database being retried once.  Now the database level permissions should be correct for core SCOM functionality, they were set by the SCOM installer.  But the SCOM agent is installed on this server - that will be using the SCOM Action Account, and having installed the SQL Management Pack, it's probably trying to do some health checking of databases it wasn't given access to during the install.  Now, I had a bit of a hunt around to try and find out what permissions the SCOM Action Account needs for proper functionality of the SQL Server Management Pack, but (as far as I could see) this isn't something that's clearly documented  The closest thing I could find was the SQLServerMPGuide.doc available with the Management Pack Download.  The problem with this is it only covers configuration for low-privilege environments, which doesn't support clustering or mirroring, and requires setting up three different service accounts, and using run-as profiles in SCOM.  That's fairly overkill for this lab environment, and the amount of granular permissions I've now been configuring, is making me consider the possibility that, even in a production environment having three separate accounts with 10 different permissions each (and therefore 10 opportunities for misconfiguration) is probably less secure than having a single account with more access that has credentials accessible or changeable only to a select few, and is closely monitored for unexpected behaviour... Anyway, I digress.  I decided to use the above document as a template to base the permissions I would assign the SCOMAA account, translating the requirements of the many accounts into requirements for one account and subtracting permissions I knew would already be assigned due to (primarily) the SCOMAA user already being a local admin.

In SQL Server Management Studio, I right-clicked the SQL server at the root, went into properties then permissions.  Here, I granted the SCOMAA account View any definition, and View server state.

Next, I created logins for SCOMAA in OperationsManagerDW, ReportServer, ReportServerTempDB, master, model and msdb Databases (it would have already had a login for the OperationsManager Database) and made it a member of the db_owner role for each of these Databases.

By doing this on the model DB, it will ensure that any future databases get this permission by default.  After these changes the Application Event Log errors ceased.
Next, seeing as I was working on the SQL server I decided to check SCOM for any other potential issues.  It looks like the SPN has been switching between good and bad a few times.

The SQL Server Service should be able to set this itself, but seeing as I installed it to run under a domain account, in this case it won't have permissions.  I opened the SQL Database Engine account in Active Directory, went into security then advanced, and added SELF, giving it permissions to read and write servicePrincipalName.

With the correct permissions in place, I restarted the SQL Server service, and recalculated the health in SCOM.  After that I had an all-green in health explorer for the SQL server.  Next, as proper Exchange topology discovery requires agents running on Exchange servers to be allowed to act as a proxy I went into Agent Management and configured the Agents on the Exchange 2003 Back-End and Exchange 2003 Front-End to act as proxies.

Next I took a look through the remaining alerts in SCOM for anything non-Exchange related, (I'll cover fixing the Exchange related Alerts in Part 3).  By this stage I was left with one single (non-Exchange) alert.

So here we have a case of the System Center Data Access service attempting (and failing) to register a SPN against the Computer account of the SCOM Sever.  I checked the account the service was running under.  In this case SCOMDB.

I then went into ADSIEDIT, and verified that the values MSOMSdkSvc/RSMSGSCOM1 and MSOMSdkSvc/RSMSGSCOM1.msgeek.local were not present in the servicePrinciplaName attribute of the SCOM Computer object.

I then went into Security of the computer object, and gave the SCOMDB account read servicePrincipalName and write servicePrincipalName permissions,

I then restarted the System Center Data Access Service on the SCOM server, and re-checked the servicePrincipalName attribute in ADSIEDIT - the missing values had now been correctly added.

(Notice how the actual resolution and resolutions listed in the SCOM alert were nothing alike? It's fine setting SPN's manually, but that leaves open the likelihood that it will break again in the future.)