Clarification - ACS Reporting

This is an entry I have been meaning to write for a couple of weeks now.  But I have been really busy and I finally found some free time to write to my hearts content.

In a prior blog entry, I provided steps on importing the ACS Reports after you install the ACS Collector component in your Management Group.  What was not clear were the options for where to install the ACS Reports. 

So, in saying that, I want to provide some clarification around the deployment of ACS Reports for Operations Manager 2007.  This recommendation is based off of my extensive testing in coming up with the procedures to move the ACS database to a new SQL Server (in support of the next version of the Backup/Restore Guide for Operations Manager).  As you may or may not know, the curent release of the Operations Manager 2007 Deployment Guide provides guidance in deploying ACS Reports under two scenarios:

  • From the SQL Server hosting the OperationsManagerAC database (with SQL Server 2005 Reporting Services installed) and accessed via the browser.
  • From the server hosting SQL Server Reporting Services and Operations Manager 2007 Reporting Services and accessed via the Operations Console.

Now, if you deployed Operations Manager 2007 under the Single Server/Single Management Group scenario, you can successfully access the ACS Reports from the Operations Console.  However, if you have deployed Operations Manager 2007 under the Multiple Server/Single Management Group scenario, where OperationsManagerAC database is hosted on a dedicated SQL 2005 based-computer and a separate SQL Server 2005 based-computer is hosting Operations Manager Reporting components (Report Services and the Data Warehouse) and ACS Reports (as an example scenario), you won't be able to successfully view the ACS Reports via the Operations Console. 

This occurs due to how security has been implemented for executing reports via the Operations Console.  When you execute a report, it runs under the security context of the Operations Manager Data Reader account that you define during setup of OpsMgr Reporting.  The database user "Domain\OpsMgr Data Reader" has a role membership in the OperationsManagerDW database of "OpsMgrReader" and this database role is granted "select" rights to many views, tables, stored procedures, etc. in the OperationsManagerDW database.  The security mode developed for reports in Operations Manager is not compatible at this time with the ACS Reports.  When you attempt to view an ACS Report in the Operations Console, you will receive an error "An error has occurred during report processing.  Cannot create a connection to data source "dataSource1."  On the SQL Server 2005 based-computer hosting the OperationsManagerAC database, you will see an event written in the Application Log "Login failed for user NT AUTHORITY\ANONYMOUS LOGON.(CLIENT:  <IP Address of client>."   The Operations Manager Reporting security model is not designed to prompt for security credentials when attempting to run a report.  Therefore you cannot configure the ACS DBAudit datasource to connect using "credentials supplied by the user running the report" and "Use as Windows credentials when connecting to the data source" (as one example for a possible work-around).  I spent many hours working on this, so I can assure you that every possible configuration was tested and discussed with the developers.

Even if you were to modify the connection settings of the DBAudit datasource for the ACS Reports (which you set to use Windows Integrated Security after you install the reports) to store the OpsMgr Data Reader account on the report server, this is not secure nor recommended. 

So after reviewing all of this, I wanted to make sure that you understood the recommended way to deploy ACS Reports under two configurations:

  1. On the same server hosting the ACS Database
  2. On the SQL Server 2005 based-computer hosting Reporting Services for SQL and Operations Manager (which could also be or not be hosting the Data Warehouse database). 

This gives you the ability to ensure:

  1. Data in the ACS database is secure
  2. You can appropriately delegate permission to who can view/run reports against the ACS database
  3. Can successfully run the ACS reports and any custom ACS reports you develop

I have opened a bug with the Product Group so that we update the Deployment Guide to provide you with the right guidance.  Also know that the developers are going to look into this further, so that we find a way to run the ACS reports via the Operations Console using a secure, supported method.