CODEWITHSUNDEEP
sql-serverreporting-servicesssrs-2012ssrs-2008-r2ssrs-tablix
HackingWiz
HackingWiz

Reputation: 69

Using Microsoft Sharepoint List as DataSource in SSRS

I have a problem connecting the SSRS to Sharepoint Online List. I can connect it through Microsoft Excel (asking for credentials, auth) and can read from the list but when I use the same URL to SSRS it doesnt work and message appears saying that "E_ACCESSDENIED". enter image description here enter image description here I search over the internet a lots of threads but without success. I tried all of the auth methods but without success. My thoughts are that my connection string in the SSRS should be different. I really done know and need your help! Thanks!

Here is the error:

===================================

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

------------------------------
Program Location:

   at Microsoft.ReportingServices.DataExtensions.SharePointList.SPRemoteConnection.ExecuteServiceRequest(ServiceRequest request)
   at Microsoft.ReportingServices.DataExtensions.SharePointList.SPRemoteConnection.GetLists()
   at Microsoft.ReportingServices.QueryDesigners.SharePoint.SharePointTables.Refresh()
   at Microsoft.ReportingServices.QueryDesigners.SharePoint.QueryDesigner.Presenter.ValidateConnection()
   at Microsoft.ReportingServices.QueryDesigners.SharePoint.SharePointQueryDesigner.InitializeQueryDesigner()
   at Microsoft.ReportingServices.QueryDesigners.InternalQueryDesignerWrapper.InitializeDesigner()
   at Microsoft.ReportingServices.QueryDesigners.InternalQueryDesignerWithGenericModeWrapper.Initialize()

===================================

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

------------------------------
Program Location:

   at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
   at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
   at Microsoft.ReportingServices.DataExtensions.SharePointList.SPListService.GetListCollection()
   at Microsoft.ReportingServices.DataExtensions.SharePointList.SPRemoteConnection.<>c__DisplayClass5_0.<GetLists>b__0(SPListService listSvc)
   at Microsoft.ReportingServices.DataExtensions.SharePointList.SPRemoteConnection.ExecuteServiceRequest(ServiceRequest request)

Best regards.

Upvotes: 2

Views: 1792

Answers (1)

Joe S
Joe S

Reputation: 346

I believe that option is only for on-premises versions, not for SharePoint Online. I do not believe you can directly connect SharePoint Online to SSRS.

We set up an SSIS package that pulls list data from SharePoint online into our on-premises database, and then query that within the SSRS report. It works pretty well for our needs.

Upvotes: 1

Related Questions