OData authentication to Power BI Report Server shared dataset

[danielfgodoi]

Hello everyone,

We're trying to create reports using the same dataset so we can reuse them e don't use a lot o space replicating Power BI Reports.
So, we create a datasource and a dataset using Report Builder. So far we have been able to make everything work.
The only way that we could find to create Power BI Reports using this dataset, is using OData Feed (according to PBI doc and some posts in this community).

So, I created the report using the URL http://powerbi/reports/api/v2.0/DataSets(dataset-id)/data and could get the data successfully. After I published the report to the Report Server, I could open and view the visualizations. But when I try to update the data (through update button or schedule refresh) it won't work. When I go to credentials and try to connect using the same credentials used in Power BI Desktop (while creating the report) it won't connect and give the message "Log on failed. Ensure the user name and password are correct."

Can you anyone help me understanding what am I missing? Thank you in advance!

[JNP_US]

My current Use Case:  A power BI (PBIX) report using Power BI Shared Data Source/Data Set created using Microsoft Data Builder and published on PBIRS Portal, with a Cache Refresh Plan created for the Shared Data Set on PBIRS Portal and a Scheduled Refresh setup for PBIX report on PBIRS Portal.

I want to circle back and let the thread know that this is working for me in September 2024. My org is running January 2024 Release of PBIRS. My settings/configuration look like this:

Shared Data Source (not the shared Data Set) is using SQL Server Internal Account for Authentication.

Then in the Settings of Shared Data Set on PBIRS Portal > Manage > Caching

Cache copies of this dataset and use them when available

Cache Expiration set for 6:00 AM everyday (using a Shared Cache Expiration Schedule)

Cache Refresh Plan created for a 6:01 AM refresh everyday (using a Shared Cache Refresh Schedule)

Then in the Settings of PBIX Report on PBIRS Portal > Manage > Data sources

Data Source <N>:

Type: OData (pre-selected drop-down value with non-functioning drop-down stuck at OData, which is understandable)

Connection String: As defined in Power BI Desktop while accessing the Shared Data Set in Power BI Desktop (Optimized for Report Server)

Credentials:

Authentication Type = Windows Authentication (from the drop-down on PBIRS portal)

User name: DOMAIN\Local-AD-Account

Password: <password for this local AD account>

is working for this.

One point I would like mention is: when I configured the data sources for my PBIX report, I did not Validate the connection for one of the data sources and the scheduled refresh for PBIX report failed for that data source. Once I went back to the data source settings of the PBIX and validated the connection for that data source, the refresh worked successfully.

I hope this helps someone out there!!

Regards, JP

[Anonymous]

1. Create Dataset in Report server using "New" option in RS.
2. Add your username and credentials in Settings.
3. Test Connection. 
4. Upload your report.
5. In report setting , data sources > use shared data set -> select the recently created dataset.
6. Schedule refresh.

We did it with Kerbros Authentication. OAuth is much simpler than this.

[jitendrapandey]

Has anyone found a fix to this problem?  I am running into an exact same issue while using OData feel to access Azure DevOps data.  The connection works from Power BI Desktop but doesn't when I publish this report to Power BI Report Server (on-premise) and update the same credentials on Data Source tab of the Power BI Report.

Azure DevOps connector is not support on Report Server and it is advised to use OData feed by Microsoft themselves:

Overview of sample reports using OData queries

https://docs.microsoft.com/en-us/azure/devops/report/powerbi/sample-odata-overview?view=azure-devops

Data sourced that Report Server supports (OData Feed is listed in the list available below):

https://docs.microsoft.com/en-us/power-bi/report-server/data-sources

Thanks and regards,

Jitendra Pandey

[d_gosbell]

---

@jitendrapandey wrote:

Has anyone found a fix to this problem?  I am running into an exact same issue while using OData feel to access Azure DevOps data.  The connection works from Power BI Desktop but doesn't when I publish this report to Power BI Report Server (on-premise) and update the same credentials on Data Source tab of the Power BI Report.

---

Unfortunately this is not the same issue. Power BI Report Server does not currently support doing scheduled refresh against any sources which require OAuth for authentication (like DevOps or Sharepoint Online). These sources work in Desktop (which knows how to handle OAuth) but not for scheduled refresh. The only options are to either manually refresh the data using Desktop - which is not great. Or to use a script or process to copy the data to some location that Power BI Report Server supports for scheduled refresh (like a local SQL instance or a network file share).

[jitendrapandey]

Agree it is not an exact same issue.  Please read my text as "exact same error message".

This link (https://docs.microsoft.com/en-us/power-bi/report-server/data-sources) says:

Power BI reports can connect to a number of data sources. Depending on how data is used, different data sources are available. Data can be imported or data can be queried directly using DirectQuery or a live connection to SQL Server Analysis Services. Some data sources are available in Power BI Desktop optimized for Power BI Report Server, but aren't supported when published to Power BI Report Server.

These data sources are specific to Power BI reports used within Power BI Report Server. For information about data sources supported with paginated reports (.rdl), see Data Sources Supported by Reporting Services.

List of supported data sources

Data sourceCached dataScheduled refreshLive/DirectQuery

...

...

OData Feed

Yes

Yes

No

This documentation clearly mentions that Scheduled Refreshes are supported for OData Feed and that is what I am using with-in a PBIX files (not an RDL file).  More over, the "Data sources" tab of this report, published to PBIRS, is enabled (in contrast with using a Azure DevOps Connector (Beta), where this tab was disabled on PBIRS) and it correctly lists the "Type" of the data source as OData in "Type" dropdown (a non-functional dropdown).  That makes me think that it is implied that OData is supported on PBIRS.

So, not sure where the problem lies.

[d_gosbell]

---

@jitendrapandey wrote:

These data sources are specific to Power BI reports used within Power BI Report Server. For information about data sources supported with paginated reports (.rdl), see Data Sources Supported by Reporting Services.

of the data source as OData in "Type" dropdown (a non-functional dropdown).  That makes me think that it is implied that OData is supported on PBIRS.

---

Yep, I'm more than familiar with that page, it's the one you linked in your initial response so I did not bother re-linking to it. Unfortunately it does not list the authentication types supported by PBIRS

---

@jitendrapandey wrote:

This documentation clearly mentions that Scheduled Refreshes are supported for OData Feed and that is what I am using with-in a PBIX files (not an RDL file). 

---

The problem is that OData is a protocol not a specific source. It can be implemented in different ways and the spec does not mandate any specific authentication method. You will find that OData sources that use anonymous or windows auth work fine (http basic auth may work too but I have not tried that).

Sharepoint Online Lists for example use OData as their underlying protocol, but like DevOps they use OAuth for authentication (which Power BI shows as "Organizational Accout").

However I just had a look at the DevOps documentation here https://docs.microsoft.com/en-us/azure/devops/report/powerbi/client-authentication-options?view=azur... and it talks about the fact that you can use basic authentication or a personal access token - have you tried using either of those options for authentication or are you using the "Organizational Account" option? You may find that setting up basic auth (which I think you will have to change in Desktop) might allow this to work.

[jitendrapandey]

Giving Basic Authentication a try is on my list.  And yes, as you pointed out, and as remembered from Azure DevOps Connector, it requires an Organizational Account option for authentication.  However, this option does not show-up as an available authentication method for OData.

Will let the thread know if Basic Authentication worked or not (subject to my Org Security Team being fine enable it.)

[Anonymous]

Hi Daniel,

Any luck with your issue, we are facing the exact problem.

Regards

[pbx]

I have found OData Connection Testing and Scheduled Refresh to be a bit buggy. While not sure exactly where the problem is, I have high suspiction that it does not like to send credentials over HTTP. You could try with HTTPS to see if your problem goes away.