Here is the scenario. I have a SharePoint 2010 site that uses Forms Based Authentication and as such it uses Claims Based Authentication. I did not enable Windows Integrated Authentication on the site. I created a Reports Library and created a very basic report there. It doesn't even pull any data. It just shows static content. I did that to eliminate any chance that the access denied was to a data source. In the real world, I did eventually have it pull real data once I solved my access denied issue.
I am running:
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Express Edition (64-bit) on Windows NT 6.1
(Build 7601: Service Pack 1) (VM)
The error I get when trying to load the page is as follows:
In text form:
Report Server has encountered a SharePoint error. (rsSharePointError)
Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
For more information about this error navigate to the report server on the local server machine, or enable remote errors.
After searching through the SharePoint log files on the server we found:
Web part failed in SetParamPanelVisibilityForParamAreaContent: Microsoft.Reporting.WebForms.ReportServerException: Report Server has encountered a SharePoint error. (rsSharePointError) ---> Microsoft.Reporting.WebForms.ReportServerException: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) ---> Microsoft.Reporting.WebForms.ReportServerException: For more information about this error navigate to the report server on the local server machine, or enable remote errors --- End of inner exception stack trace --- --- End of inner exception stack trace --- at Microsoft.Reporting.WebForms.ServerReportSoapProxy.OnSoapException
For Access Denied error, one solution put forth in this forum is
Reporting Services service account must be local admin on the reporting services server, please check if you meet this requirement. If not add the user to the server's local admin group and "reintegrate the reporting services with SharePoint"
The SSRS service account (you can get this from the SSRS Configuration tools in the Start Menu) was not in the local administrators group where Reporting Services is installed. I have made this correction. I did NOT appear to fix the issue. I even tried restarting Reporting Services from Services.msc, but no help.
Then I realized that I still need to "reintegrate the reporting services with SharePoint." I was not sure exactly what that meant, so I just tried some things. Here is what I did that I think fixed it after adding the SSRS service account user to the local admin group:
- In Central Administration| Reporting Services | Reporting Services Integration| Clicked Activate feature in all existing site collections and then the OK button.
- In Central Administration| Reporting Services |Add a Report Server to the Integration changed Server Name to a wrong value then changed back to the server name where Reporting Services is installed. Click OK after each change. When prompted for credentials I entered the farm service account and password.
- I tried the report and it worked. I’m not sure exactly which step fixed it, but it works now.
Some other things to try if it still doesn't work:
You need to be running SQL Server 2008 R2 according to this article on MSDN. It says:
"SharePoint 2010 Claims based authentication is not supported by SQL Server 2008 or SQL Server 2008 SP2 report servers. Use SQL Server 2008 R2 Reporting Services if you need to use a Claims enabled SharePoint 2010 Web application."
According to the article, you don’t have to upgrade our SQL Server to SQL Server 2008 R2, but you do need to upgrade to at least SQL Server 2008 SP1 + Cumulative Update #8. You would have to upgrade the SQL Server 2008 R2 Reporting Services add-in for SharePoint 2010 products on all associated servers.