Unable to access SharePoint List in SSRS Report Builder

[luvsql]

After choosing the Microsoft SharePoint List as the connection type in my data source, entering the correct URL for our SP Site, going to Credentials and setting to prompt (as there is no option for this connection type to use Windows Authentication) when I click on Test Connection I get:

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

I not only have access to this site but I'm the Site Owner.  I should never be denied access.  I don't even get to the option of selecting the list as the Build is greyed out.

I use SSRS/Report Builder all the time with SQL data but need to grab some data in SP for a much needed COVID report.  

Even if I enter my Domainname\username and password and mark as use windows it doesn't work.  Literally no options I choose in Credentials work.

SQL is version 2014.  

[#2]

@luvsql did you find a solution to this problem ? 

I'm trying to connect to sharepoint online but can't find the right connection string format

I tried https://mysharepoint.sharepoint.com/sites/SPsiteName

[#3]

Hello @hveillette ,

Try it with the connection string from

https://www.connectionstrings.com/sharepoint/

Olaf

[#4]

@olafhelper We cannot pass individual passwords in the connection strings.  It should work with Windows Authentication so passwords are not stored in the connections.

[#5]

@hveillette As with many Microsoft Services that don't work as documented, I give up and move on.  Sad really.

[#6]

@olafhelper Thanks for the hit but i Guess that Cdata is a paid third party.

Theses connections string seems to be used with this component.

I'm trying to use the Sharepoint list datasource option in Report builder or visual studio 2019 (SSRS) but nothing works

[#7]

@hveillette I think it's "by design" so customers are forced to pay $$$$ to get Power BI for all users.

[#8]

Come on really. Here we are 20 years on and Im still using access to generate Reports from Sharepoint List that contains multiple attachements (photo's) that I want in my simple 1 page Report/Record.