SharePoint List data source giving HTTP 401 "Unauthorized" error

Question

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?

Answer 1

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.

Answer 2

Check these cases:

1. use the second option in Connect Using section for datasource. Check attached Image.

2. Check whether you have configured all the web.config entries correctly. You can trace this type of error by attaching the w3wp process.