Introduction:
In the scenario I am about to describe trusted accounts and domain accounts were used in order to allow Reporting Services to run in SharePoint Integrated mode. This was the initial installation of SSRS within the organization and needed to be done using trusted and domain accounts since SQL Server Reporting Services (SSRS) was configured in a multi-server environment without Kerberos enabled. It was our intention to provide functionality so that if a user was given access to a document library where the SSRS reports were stored, then the user should be able to run and therefore view data in the reports.
This is not by any means the highest level of security that could be used, however, since the sensitivity of the data was not an issue this configuration worked well to easily deliver reports to a large number of users. In theory this scenario sounds pretty simple, however, for some reason none of the end users were able to view any of the reports within the created report libraries. The site Administrators could view and run the reports, however, the majority of the users that would be intended to only have Read access to the report library couldn’t see the reports or run them. Administrators could even view and run the reports if they were changed to only have Read access to the report library.
Solution:
I actually struggled with this issue for awhile. It turns out that the solution was pretty simple, but before I figured it out I checked everything that I could think of from the actual configuration of SSRS to the security on the individual users, user groups, and document libraries. I still could not find anything that seemed like it should not allow users to see the reports. I then checked the versioning of the report library and this is what I found:
For some reason, when libraries were being created the "Conent Approval" was being set to "Yes" and the "Draft Item Security" was being set to "Only users who can approve items (and the author of the item)". Aha! The Administrators could see the items because they uploaded them. The other end users could not see the documents because the Administrators did not know they had an approval setup on the uploaded reports. By tweaking the options shown above (there are many different ways to do this including actually setting up an approval process) the users were then able to view the reports. Since there were not any needs to setup an official approval process at the time, I adjusted the settings on the report library as shown below:
Important: Even though "No Versioning" is selected, this does not mean that a history of report changes and updates is not kept. SSRS maintains history separate from SharePoint versioning.
