Reporting Services – sccm.haas.se

Error poetry

An error occurred during client rendering.
An error has occurred during report processing.
Query execution failed for dataset ‘DataSet2’.
The EXECUTE permission was denied on the object
‘PowerManagementGetPowerCapabilities’, database ‘SMS_CEN’, schema ‘dbo’.

Right, so you’re trying to view a report in Reporting Sevices that you copied from ConfigMgr and now you’re getting this. What is wrong? The SQL-account you’re using to access the Reporting Services database simply has insufficent rights. That’s what the message above is trying to tell us.

After some trial and error I gave my Reporting Services SQL-account rights as db_datareader and smsschm_users in the ConfigMgr database (SMS_<sitecode>). See screenshot below:

After that, reports work great.

/Mathias

Subscribe to reports with SQL Reporting Services

Subscribing to reports is really great to show to upper managment. How do you make that work? It’s not too hard actually. You just need to give ConfigMgr a username that has access to the database so that Reporting Services can save the information in the database. I use the same account for that as the account I specified during the installation of SQL Reporting Services. If you run into access problems, check my post above this one.

Accountsettings — Right click on your Server below Reporting Services in ConfigMgr.

/Mathias

Reporting Services configuration can be tricky…

…very tricky indeed. I had no idea how complicated it can be until today.

In this installation of SCCM 2007 R2 (in my case R3) with SP2 – Configuration options where greyed out in the “Copy Reports Wizard” as well as when you right click the Reporting Services Server icon below Reports in the SCCM-console.

According to Microsoft this can happen if you run more than two instances of SQL Reporting Services on the same machine and that’ll make ConfigMgr confused since it automatically selects the default instance of Reporting Server through WMI. I’m pretty sure this issue happens if you choose “Install but do not configure the report server” during the SQL 2008 installation instead of “Install the native mode configuration”. I have to test this in the lab but I’m guessing the SQL RPS instance never gets created if you don’t configure RPS and therefore SCCM can’t find it.

If you want to fix this, all you need to do is to open Reporting Services Configuration Manager and configure RPS manually.