Your browser (Internet Explorer 6) is out of date. It has known security flaws and may not display all features of this and other websites. Learn how to update your browser.
X
Aside

Editing CM12 reports in the ConfigMgr console with SQL Server 2008 R2

Fellow MVP Steve Thompson has created a nice post for reporting in ConfigMgr 2012.

Read the full post here.

Presently, if you have installed ConfigMgr 2012 with SQL Server 2008 R2, and attempt to edit a report from the console, you will get an error message that states “Report Builder not available”. The problem here, ConfigMgr 2012 is designed to work out of the box with Report Builder 2.0 as explained here:

http://technet.microsoft.com/en-us/library/gg682105.aspx#BKMK_ReportBuilder

Since you have installed SQL Server 2008 R2, which ships with Report Builder 3.0, you can indeed use Report Builder 3.0, just open it directly from SSRS Report Manager.

While not supported or endorsed by Microsoft, a workaround is available to integrate RB 3.0 from the console, as follows:

You’ll need to set a registry key to name the RB 3.0 application manifest.  The key in question is “HKLM\Software\Wow6432Node\Microsoft\ConfigMgr10\AdminUI\Reporting\ReportBuilderApplicationManifestName”.  Change its value from “ReportBuilder_2_0_0_0.application” to “ReportBuilder_3_0_0_0.application” on any machine from which you want to launch RB 3.0.

Aside

Configuration Manager-Reporting Services Access Denied When Running R3 Power Management Reports

Ran into an issue at a client this week that I haven’t run into before.  When trying to access the new R3 power management reports, we were receiving an access denied.  The errors we received were the following:

An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset ‘DataSet2’. (rsErrorExecutingCommand)
For more information about this error navigate to the report server on the local server machine, or enable remote errors

An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset ‘DataSet2’. (rsErrorExecutingCommand)
The EXECUTE permission was denied on the object ‘PowerManagementGetPowerCapabilities’, database ‘SMS_XXX’, schema ‘dbo’.  

All other reports in Reporting Services worked fine.  The reason turned out to be that the new R3 reports use some stored procedures in the ConfigMgr database.  Web reports are typically just queries that use tables and views.  The new R3 reports are the first ones for ConfigMgr that are Reporting Services only reports.  Typically when locking down permissions on reports or a reporting service account, you would just assign the account DB_Reader rights in the SQL database.  This works for all the web reports but didn’t work for the R3 reports.  DB_Reader doesn’t have access to run the stored procedures that are required for the power management reports. 
 
In order for the R3 reports to run successfully, we need to add the service account to the “smsschm_users” database role. Huge thanks to Kent Agerlund and Garth Jones for providing me with the appropriate fix.

First, lets open up the database and drill down to the database roles:

image

Next we need to go into the properties of the "smsschm_users” role:

image

Next we need to add our service account to be a Role Member:

image

Select “Browse” and then select the service account you want to use.

image

Here we have our service account selected, then click “ok”.

image

Once you verified the service account is listed in the Role Members pane, then select “ok”.

image

Another way to add the necessary rights is to go into the login properties of your service account. Then go to “user mapping” and select the SMS/ConfigMgr DB and check the database role membership. This is probably the easier method (less clicks), but it’s good to know there are 2 ways to do it Smile

image

Kent Agerlund also has a post on this.  I wasn’t able to find his post when I encountered the issue and used Google, however I was told that using Bing would have resolved my issue, but I haven’t tested that. 

http://blog.coretech.dk/kea/modifying-the-dataset-execution-account-after-installing-configuraiton-manager-r3/

Aside

Configuration Manager–Configuring SQL Reporting Services (SRS)

Here is a great step by step for configuring and setting up SRS with ConfigMgr:

http://blogs.technet.com/b/mwiles/archive/2009/02/19/east-setup-of-srs-with-config-manager-r2.aspx

Here is a great article that details setting up the required permissions for SRS:

http://scug.dk/blogs/configurationmanager/archive/2009/08/13/configuring-sql-reporting-services-user-permission-for-configuration-manager-2007.aspx

Here is the SRS tag on Steve Thompson’s blog, lots of great posts here:

http://myitforum.com/cs2/blogs/sthompson/archive/tags/SSRS/default.aspx