SCCM 2012 SSRS Error – DataSource Reverts

070213_1034_SCCM2012SSR1.png

I was mucking around in my test lab tonight and managed to break SSRS (again) on my SCCM 2012 installation again. I was getting the following error.

  • “An error occurred during client rendering.
    • An error has occurred during report processing. (rsProcessingAborted)
      • Cannot impersonate user for data source ‘AutoGen__5C6358F2_4BB6_4a1b_A16E_8D96795D8602_’. (rsErrorImpersonatingUser)
        • Log on failed. (rsLogonFailed)
          • For more information about this error navigate to the report server on the local server machine, or enable remote errors ”

 

I worked out that I could go into the Report via the Reporting Website, create a new Datasource, and then it would work fine again….until, SCCM reverts the permissions back every 10 minutes to what is stored and encrypted in the SSRS Database. I then thought uninstalling the Reporting Services point and re-installing SSRS would do the trick. But it didn’t change anything. As soon as the Reporting Services point was back up, I started getting the same issues.

To get it to work, here are the steps I used…

  • I recreated the account that the Reporting Services Point was using to connect to the Reporting Server. (Not sure if this needs to be done, or whether the next step fixed it completely….)
  • I then changed the following registry key HKLM\SOFTWARE\Microsoft\SMS\SRSRP\SRSInitializeState key
    on the Site Server to a value of 0. This re-imports the all of the Reports again.

Check the SRSRP.log file to see what is happening under the hood.

Looks like something is going to happen?

Sure enough, all the reports start to rebuild. This also seems to recrate the DataSet / DataSource for the connection which seems to get rid of that error!

Assigning Users to ConfigMgr ReportUsers group in SCCM 2012

070113_0438_AssigningUs1.png

I have found that delegating permissions so that specific users are only allowed to view reports in SCCM 2012 can be a little tricky. I wanted to be able to add an Active Directory group to the ConfigMgr ReportUsers group in SCCM 2012. Then these users could simply view certain reports but not be able to build, create, edit or manage those reports and also not have access to the ConfigMgr Console. I think that’s a reasonable request. After all, one of the benefits of combining the reporting services point with SSRS in 2012 is being able to view the reports through the web console. Here is a little rundown of the pain that I’ve been through troubleshooting this issue.

I tried setting the permissions via the Web Console. I thought I could set the permissions and then those permissions would propagate down to the lower folders. This is the default behaviour after all.

Unfortunately, the permissions didn’t propagate. Not only did they not propagate, but I found that if I manually went through and set the permissions on the sub folders, within 10 minutes or so, those permissions would revert back to default. This mad me sad, and angry. But mostly sad.

So I thought I better do a little fishing, I decided to check the SRSRP.log file on the ConfigMgr server to see if could find out what was going on. I found this.

It turns out that SCCM is checking every 10 minutes or so, to see if the permissions are the same with what is in SCCM. If the permissions have changed in the Web Console, SCCM promptly changes them back.

So then, how do we assign user to the ConfigMgr Report user group?

In order to get it working here are the steps that I needed to follow. First, create the group that you would like to delegate the privileges to in Active Directory Users and Groups. Fill that group with the users you would like to delegate access to.

In the SCCM console, navigate to Administration > Security > Security Roles and COPY the Read-Only Analyst role.

You will now need to go through each individual permission and make sure run report is the only permission assigned. This will take a long time. The other option is to just associate the Read-Only Analyst role. This might give more permissions than you would like to give however. That’s up to you.

Now in the SCCM console, navigate to Administration > Security > Administrative Users. Right click Administrative Users and click Add User or Group.

 

Fill out the wizard. Leave the Collections and Security Scope as default.

Now go back to your web browser to check that the permissions have applied. It might take up to 10 minutes to resync. You can check the log file if you like. CMtrace.exe is a tail log viewer so it will update in real time.

You should see your group listed with the rights ConfigMgr Report Users. Now your users can view reports without breaking anything! Woohoo!

You can find more information on Reporting Services in SCCM 2012, here > http://technet.microsoft.com/en-us/library/gg682105.aspx

 

 

 

 

 

 

 

 

Uninstalling & Reinstalling SQL Server Reporting Services 2012

1251398_76320527

​I had some issues with uninstalling and reinstalling Microsoft SQL Server 2012 Reporting Services in my SCCM Lab environment. It turns out there are a few steps not mentioned in the microsoft documentation that you need to do in order to do a fresh install, without carrying any configuration settings over from the previous install. Here are a few dot points on what I had to do.

  • Remove the Reporting Services Point from SCCM
  • Appwiz.cpl > Microsoft SQL Server 2012 > Remove > Remove Reporting Services
  • Manually delete the ReportServer Database and the Temp Report Server Database
  • Delete the C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER) folder.
  • Reboot
  • Reinstall SQL Server Reporting Services and select install only during the installation.
  • Open up Reporting Services Configuration Manager and configure your accounts.
  • Make sure that the Websites are set in Reporting Services Configuration Manager or you won’t see a Sql Instance when you try to create the Reporting Services point in SCCM. http://social.technet.microsoft.com/Forums/en-US/4479e73e-8a19-4c7e-9418-b36770656b9b/sccm-2012-reporting-point-server-instance-blank