Error While Passing Credentials from SSRS to Sharepoint Data Source

reporting-servicessharepoint-2010ssrs-2008

I am using SSRS 2008 R2 to build a report that uses a Sharepoint list as its data source, to which it is configured to connect using Windows Authentication (Integrated Security). The problem is that when the report is deployed to the Report Server, the SharePoint source rejects the credentials, returning:

An error has occurred during report processing. (rsProcessingAborted) 

Query execution failed for dataset 'ListDataset'. (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.

The Sharepoint data source accepts my credentials when I test the report locally in BIDS, but does not accept them after they 'double-hop' from the Report Server. What is the best work-around? Do I need to create a Service Account in SSRS, supply those credentials in the data source within SSRS, and grant the necessary read permissions to the Service Account via Sharepoint? Any suggestions would be greatly appreciated.

Best Answer

This is the same answer to a similar question:

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): reportuser@domain.com 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.

Related Topic