SSRS SharePoint List data provider querying a SharePoint List in Claims enabled site with Windows and Forms Based Authentication selected

  • Article

Have you tried creating a report in BIDS / SSDT / Report Builder 3.0 using the Reporting Services(SSRS) SharePoint List extension against a SharePoint list in a Claims based SharePoint site?

The Claims authentication has both Windows and Forms based authentication enabled.

The unique property for the SSRS SharePoint list provider is, it only accepts windows based credentials. So when you use either one of Windows Credentials, Stored credentials (Use as Windows credentials), Prompt for credentials option and execute a query against the SharePoint list provider, visual studio (BIDS / SSDT) would throw the below error.

 

TITLE: Microsoft Visual Studio
------------------------------

Server was unable to process request. ---> Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))

------------------------------
ADDITIONAL INFORMATION:

Server was unable to process request. ---> Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) (System.Web.Services)

------------------------------
BUTTONS:

OK
------------------------------

The point here is, when you get this exception, don’t be surprised. The reason is, A SharePoint web app under any claims authentication type will simply tell you it is in forms authentication mode if you query the AuthenticationMode property.

This behavior is by design as the SSRS client tools like BIDS, SSDT, Report Builder 3.0 are all claims agnostic.

 

Having said that the immediate questions is, then how can I consume SharePoint list provider under these circumstances. For that, I’m listing out 3 options and please feel free to use one of them.

1. Temporarily disable Forms based authentication for the specific Claims enabled SharePoint site and design, test your reports.

2. Deploy the report to the Claims enabled site and execute the report directly from the Document library.

3. Extend the SharePoint web application in a different zone with just Windows authentication selected and use this site for testing.

More on Claims Authentication and Reporting Services can be found here @ https://msdn.microsoft.com/en-us/library/ff487970(v=SQL.105).aspx

HTH!

Selva.

[All the posts are AS-IS without any warranty]