ComputerBob, a couple of questions:

Have you tried the 'less secure' option of distributing Run As credentials to see if it makes any difference? Would be interesting as a test to see if credentials simply are not being provided in the current configuration.

Also, is this rule in a stand-alone MP so we could have a look at the XML?

Nice work on the SQL backups script!

Pete,
   I had previously used the 'less secure' option of distributing the credentials, but ran into many issues with it.  We have an untrusted domain connected via a gateway server and SCOM attempted to send those domain credentials to those servers.  We immediately began getting a multitude of logon issues on every server in that domain, so I had to kill that option and force the credentials to only go to the targeted servers.

   Also, I have attached the xml file.  It's got a few other things in it like overrides for other rules, but you should be able to find the backup monitor easily.  I've attempted to strip any identifying marks as my company normally doesn't allow us to post things in forums (please let me know privately if I've left anything in that shouldn't have been).

Tommy,
   When I go into Administration\Run As Configuration and pull up the account in question and choose the Distribution tab, I only get the option to add physical servers.  Is there some special way to add the virtual nodes that I'm not seeing?  Do I have to do that in the authoring console?

OK, I learned something today that changes this whole situation.

Every server we have is using the Local System account to query the DBs for information, but only the clusters are rejecting it.  The standalone SQL servers have the local system account as SA (Very bad, I know, they are fixing it) which allows the script to run.

Apparently, I don't know how to make a monitor use a runas profile.  Anyone have any links to places that explain it in detail?

Wow, OK, with collaboration from my security team, SQL team, and some google searches, I think we finally figured it out.  AND we don't have to use a specific account to do it.

Local System by default is given certain rights when SQL 2005 is installed.  Our organization strips those rights to prevent various bad things from happening.  This was where we got tripped up thinking we needed to elevate our rights by using a certain account.  Keep reading, and you'll find that's not the case.

SQL, by default, allows various things to be considered 'Public' information.  Things like the version of SQL running, the DB names it hosts, backup information about those DBs, etc.  You don't need any special privileges to read that information from SQL.  Most (if not all) of that information is contained in the 'Master' DB in SQL, not in the individual DBs themselves.  The real problem we had was the connection string being used in the script.

The script contained the following connection logic:

  ConnString = "Server=" & strServer & ";Database=" & strDatabase & ";Integrated Security=SSPI"

 

We changed it to:

  ConnString = "Server=" & strServer & ";Database=master;Integrated Security=SSPI" 

Notice that the original connection verbiage had the script connecting to the DB being tested, which you need privs to do, while the adjusted verbiage connects only to the 'Master' DB which does not require any special privs.

This resolved our problem on ALL servers.  This adjustment will work in environments that leave the installation as the default as well as environments that 'lock down' the DBs to more closely control who can read them. 

I'm going to send my finalized script over to Stefan so he can update his blog with the new code if he wants to.

I've attached the script here as a TXT file so anyone can grab it as needed.

you would define a new RunAs Profile with the "SecureReference" element of an MP, described here

http://www.authormps.com/dnn/Default.aspx?tabid=65

(you can do this in the Authoring Console much more easily tho).

and then specify a "RunAs" attribute for the unit monitor indicating you want to use the SecureReference you have created above

http://www.authormps.com/dnn/Default.aspx?tabid=73