System Center Operations Manager Doesn't Show Any Reports

Here's an interesting issue I came across the other day that I thought you all would be interested in hearing about.  A customer using Operations Manager 2007 (SCOM2007) called in saying that the reporting section of the console didn't show any reports.  We also saw the following event in the Operations Manager event log:

==========

Event Type: Error
Event Source: Health Service Modules
Event Category: Data Warehouse
Event ID: 31569
Description: Report deployment process failed to request management pack list from Data
Warehouse. The operation will be retried.
Exception 'SqlException': Login failed for user '<user_account>'.

One or more workflows were affected by this.

Workflow name: Microsoft.SystemCenter.DataWarehouse.Deployment.Report
Instance name: <InstanceName>
Instance ID: {9B3B6302-76A0-D0CA-C806-BC4465A41E83}
Management group: <Group>

For more information, see Help and Support Center at
https://go.microsoft.com/fwlink/events.asp.

==========

After investigating, we saw in the error message that the user account was listed as <useraccount>, instead of <domain>\<useraccount>, indicating that we are using SQL Authentication for data warehouse access.

The solution here was to configure OpsMgr to use Windows Authentication for the data warehouse workflows.  To do this, we created a "blank" SQL Server authentication account by following these steps:

1. Open the console, click on the "Administration" tab and select "Run-As Profiles" in the tree.

2. Double click "Data Warehouse SQL Server Authentication Account" profile and switch to the "Run-As Accounts" tab. Check the list in the tab - most likely it is empty. The list must contain one entry for each of your Management Servers including RMS.

3. In order to add missing entry to the list, we followed these steps:

a. Close the dialog (opened in step #2 if still open).

b. Right-click "Run As Accounts" in the tree and select "Create Run As Account" menu item.

c. Click "Next"

d. Choose "Simple Authentication" in type, set "Display Name" to "Data Warehouse SQL Server Authentication Account".

e. Click "Next"

f. Put a single space in account name, password and confirm password fields and click "Create".

g. Switch to "Run As Profiles" and double-click "Data Warehouse SQL Server Authentication Account" profile.

h. Switch to Run-As Accounts tab, click "New".

i. Select the Run-As Account you just created in the list of the accounts.

j. Select your management server in the list of servers and click "Ok"

k. Repeat g-j above for each management server.

l. Repeat the above steps for the Reporting SDK SQL Server Authentication account.

Now this was kind of a unique case in that most of the time the Simple Authentication accounts will already exist and the user has just entered windows credentials for them.  In this case, you just need to modify them to use an empty space for username/password.

Hope this helps!

Jimmy Harper