Reporting Services Permisson Issue - SCCM 2016

Hi Everybody

So this week I got faced with a challenge that the reports for Configuration Manager 2016 is not showing up in SCCM console but show up in the Report Server Web Browser. I followed all the related articles/blogs that could be found on the internet but none of them explained to me how to get my issue fixed.

The infrastruscture is built on two servers
Server1 - Application Server (ConfiMgr)
Server2 - SQL backed and Reporting 

 After scratching around and looking at the RBAC permissions in the SCCM console I found that the permission is not being applied from the SCCM Console to the Reporting Web Browser. The Reports show up and are run-able from the web browser but no luck in ConfigMgr console


Looking at the Folder Settings it shows a different story all together as there is no other permission besides the local admins. Not even the account used to install ConfigMgr has permission



So to start off I started off looking at the srsrp.log on the Reporting Server to see where the error is and came across this error


This pointed me in the direction that something was wrong with the permissions on the SQL just had to figure out where. As for the console that gave a slightly different error when trying to set the SQL server under the options. 

After removing the Reporting Service point and re-adding it did not resolve the issue as it seems like someone messed around with the permissions in SQL. Think they wanted to harden SQL and in the process broke the reporting part of SCCM

On the SQL server where the reporting resides on op the SQL Management Studio> Connect to the instance where the ConfigMgr DB resides> Expand database> Expand CM_<Site> >Expand Security> Expand Schema Roles> Open the SCCM_Rpt properties






 
In the properties of the SCCM_Rpt look at the Schema Owner. This user must be set to "dbo" and not to the user account used for ConfigMgr. Click on search and change it to "dbo", after changing it click on the permission option on the left and you will see that it is blank. Click on search then browse and select the smsschm_users from the list in the Explicit permission section tick Allow for Execute and Select> click Ok
 


You are now all set and ready to go. No need to restart any services as the Reporting Service point will try and apply the permissions every 10 minutes. This is also one of the reasons that if you apply permissions in the web browser for the reports they go missing. The permission is explicitly controlled from the ConfigMgr console  

When you go back to the SRSRP.log file after 10 minutes you should see this in the file


You can no go and check it in the ConfigMgr console and the reports will show up

Till next time....