Just keep that in mind, especially for bigger data feeds. It takes significantly longer when doing the refresh in Power BI opposed to doing the refresh locally. On last thing you may realize is the “Running Time”. Once you changes those settings your data refresh will work like a charm: You can provide any values here, in my case I used “random” for both, User ID and Password! Another thing you need to ensure is that “Persist Securtiy Info” is set to True so your “Password” is stored in the final connection string making Power BI think that authentication is defined correctly within the connection string and Power BI does not have to do anything. Which UserID/Password you may ask? – and the simple answer is: “It does not matter!” However, Power Pivot does not support Anonymous authentication for OData sources:Īs SSPI does not work we need to use “Basic” here and provide “User ID” and “Password”. The first thing that comes to your mind would be to simply set the authentication method to “Anonymous” which would be perfectly fine for a public data feed. The problem with Scheduled Data Refresh is that SSPI simply does not work as it cannot be resolved and Power BI cannot use any service user for your request if SSPI is defined. Sure the OData feed actually ignores this information as it is public but from an authentication point of view everything works correctly! In your local Excel/Power Pivot model this works just fine as the SSPI context can be resolved and sent to the OData feed.
The reason is that in the connection string the “Integrated Security”-property is set to SSPI by default. So you will ask yourself what could go possibly wrong here as you are just accessing a public OData feed?!?
In our case the reason is that our user is unauthorized – the actually data source that was used must not necessarily exist in the user’s data sources (Those can be found via “My Power BI”) for scheduled data refresh to work. It says that for my user no corresponding data source exists or I do not have permissions to access it. Check the user's permission to the data source or create a data source for the connection string.