Report Server Web Portal - Connection Error for Sharepoint Data Source

[niyati_61]

Hello @d_gosbell ,

I have a report saved in the Report Server Web Portal (I uploaded it in the Portal directly). Now, in this report I have a Sharepoint data source. I want this report's data to be refreshed when the Excel File saved in Sharepoint (Data source) refreshes.

1) I clicked - report in the Web Portal - Data Source section. I put in my own system (Active Directory) credentials to check. I even tried using the Admin credentials, but I keep getting this error? (See images) I entered the creds correctly though, so not sure.

P.S - Power BI Report Server (Jan 2022) via SQL Server with SA is installed on another laptop at my company (Refered as the Test Server). I'm using my own system currently where I work on Report Server Desktop, then upload the report directly on the web portal. I wanted to check how data would be refreshed in report server.

   

2) Also, if my data source in PBI is an excel file saved on my local desktop, I cannot schedule a data refresh?

Eg: If there is an update in my Excel File, how will that change be reflected in the PBI file saved in report server?

3) Last question, when I try to Save As in PBI Desktop for Report Server, so it gets directly saved and updated in the Web Portal. Here it requests for the address of the web portal, when I enter the exact web portal URL, I get this error? No other reason is given? 

Because of this I have to upload my PBI Report directly through the Web Portal. Issue is though, if I want to edit and save my report again, those changes don't reflect in the web portal. So, if I were to save my report directly here in the desktop app, those changes would come up in the PBI File saved in the portal right?

I tried to open (as in edit though PBI desktop option) on the portal, but I got an unexpected error there and it did not open in PBI Desktop to make edits. Is there a workaround to this?

Really appreciate your help. Thank you in advance!

[d_gosbell · Accepted solution]

---

@niyati_61 wrote:

1) I clicked - report in the Web Portal - Data Source section. I put in my own system (Active Directory) credentials to check. I even tried using the Admin credentials, but I keep getting this error? (See images) I entered the creds correctly though, so not sure.

---

This is because Windows credentials do not work with Sharepoint online data sources, you need to use OAuth2 authentication to connect to Sharepoint Online and Report Server does not support that authentication method. If you check the list of supported data sources for Report Server here https://docs.microsoft.com/en-us/power-bi/report-server/data-sources you will see that Sharepoint online is not supported for scheduled refresh. The only way to refresh from Sharepoint Online is to refresh in Desktop and re-upload to the server.

---

@niyati_61 wrote:

2) Also, if my data source in PBI is an excel file saved on my local desktop, I cannot schedule a data refresh?

Eg: If there is an update in my Excel File, how will that change be reflected in the PBI file saved in report server?

---

This is correct. This is because the data refresh happens on the report server which is usually on a separate machine so it blocks any references to local files (paths begining with a drive letter). You need to change the data source in Power BI Desktop to point to a network share using a UNC path. These usually look something like the following   \\server\sharename\folder\file.xlsx 

---

@niyati_61 wrote:

3) Last question, when I try to Save As in PBI Desktop for Report Server, so it gets directly saved and updated in the Web Portal. Here it requests for the address of the web portal, when I enter the exact web portal URL, I get this error? No other reason is given? 

Because of this I have to upload my PBI Report directly through the Web Portal. Issue is though, if I want to edit and save my report again, those changes don't reflect in the web portal. So, if I were to save my report directly here in the desktop app, those changes would come up in the PBI File saved in the portal right?

I tried to open (as in edit though PBI desktop option) on the portal, but I got an unexpected error there and it did not open in PBI Desktop to make edits. Is there a workaround to this?

---

I'm not sure what this issue is, but it is not an expected error and not one I've seen. You should check the report server log files at C:\Program Files\Microsoft Power BI Report Server\PBIRS\LogFiles specifically the RSPortal* and ReportServerService* files. If you do not see anything in there that gives you a hint you may need to raise a support ticket with Microsoft.