Menu
I have built a report that uses a SharePoint list as its data source. The data source is set to use Windows Authentication (integrated security) in SSRS. It runs just fine in SSRS/BIDS, but when deployed to the Report Manager environment, I receive an error:
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset 'ListData'. (rsErrorExecutingCommand)
An error occurred when accessing the specified SharePoint list. The connection string might not be valid. Verify that the connection string is correct. (rsSPDataProviderError)
The request failed with HTTP status 401: Unauthorized.
I have deployed both the report object and the data source to the environment from BIDS. I checked the Properties to confirm that integrated security was set on the Report Manager end as well, so I am not sure as to why it's not passing the credentials properly to the source.
Any ideas/suggestions?
205
Next drill down and see if SharePoint can connect Or still get the 401 Unauthorized error. Connect directly to SharePoint Web Server by configuring a host file entry on the machine running Metalogix Content Matrix to bypass the Network Load Balancer.
Since your browser's cache stores local copies of web content and resources, it's possible that a change to the live version of your application is conflicting with the cached version already on your device, causing a 401 Unauthorized Error. Try clearing your browser's cache to see if that fixes the issue.
The HyperText Transfer Protocol (HTTP) 401 Unauthorized response status code indicates that the client request has not been completed because it lacks valid authentication credentials for the requested resource.
The service account for SSRS will not help you. It is good to have a specific service account to run the SSRS service, but that is not what gets used to authenticate. It is also good to set up an execution account on the server using the reporting services configuration tool which helps with running unattended reports, but again that's not your issue.
Kerberos is one option, yes, but if you aren't using it already it's a big effort for a small issue.
Sharepoint list datasource will only accept integrated security connections, so what you need to do in the datasource is to store a windows user as credentials in the report server.
I usually create a user called Reportuser (e.g. reportuser@[domain].com). Create this user on your domain, make sure it has access to SharePoint.
In BIDS/visual studio in the properties for the datasource for your report, under the credentials tab, click the radio button next to "Use Windows Authentication (integrated security)". Upload the datasource to the report manager website. ( You've done this part).
Navigate to the Report manager website, and the properties of the uploaded datasource.
Under the section starting with "Connect using":
Check the "Credentials stored securely in the report server" option
Enter the username and password like this (where domain is replaced with the domain of your network): [email protected] password
Important part: Tick the "Use as Windows credentials when connecting to the data source"
Test the connection and will work - I have just tested it.
145
Check these cases:
use the second option in Connect Using section for datasource. Check attached Image.
Check whether you have configured all the web.config entries correctly. You can trace this type of error by attaching the w3wp process.
36
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
