Forum Discussion

danielfgodoi's avatar
danielfgodoi
Advocate I
8 years ago

OData authentication to Power BI Report Server shared dataset

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!

dataset
OData
shared

9 Replies

  • pbx's avatar
    pbx
    Helper V
    8 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Daniel,

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

    Regards

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      d_gosbell
      Super User
      5 years ago
      Anonymous 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).

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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 FeedYesYesNo

         

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago
    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.

  • JNP_US's avatar
    JNP_US
    Regular Visitor
    1 year ago

    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