SharePoint List data source giving HTTP 401 “Unauthorized” error

5
votes

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?

3
listsharepointssrs-2008reporting-services
It looks like double hop issue ( weblogs.asp.net/owscott/archive/2008/08/22/… ) – user1578107
It looks like that is what it is. The SharePoint source accepts the credentials from BIDS, but does not accept Windows Authentication credentials passed to it a second time from the Report Server. How would one go about solving this? Should I create a Service Account within SSRS to be accepted by SharePoint? – dp3
use stored credentials, or Kerberos – user1578107

3 Answers

8
votes

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.

2
votes

Check these cases:

  1. use the second option in Connect Using section for datasource. Check attached Image. 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.

0
votes

I had this very same problem and found out that the account that I was using was incorrect due to SharePoint Designer and Visual Studio using the same credentials. When I tried to deploy my reports I noticed that it failed because it was using a user without permissions. The user was not my currently logged in user. Instead it was the one that I had last used to log into SharePoint Designer. Once I logged into SharePoint Designer using a user who also had the report permissions I wanted it then was able to correctly log in to SSRS using Visual Studio. The windows credentials between those two programs are tied together.