Scheduled refresh randomly failing every day recently with Data Source Error

[Gris]

Hi,

I have couple PBI reports with data source from one sharepoint site, and they were refreshed successfully in past years until recently. The scheduled refresh randomly failed on multiple reports with below error message:

Data source errorDataSource.Error: SharePoint: Request failed: Unable to connect to the remote server. Microsoft.Data.Mashup.ErrorCode = 10122. Microsoft.Data.Mashup.ValueError.DataSourceKind = SharePoint.

The error is not happening on every refresh, around 80% of refreshes are successful; The error is not happening for every report with the same datasource, 80% of the reports are still fine, not sure what caused this issue, quite annoying. 

Thanks in Advance

[grazitti_sapna · Accepted solution]

Hi @Gris,

This issue is due to unavilability of Sharepoint site, this could be because of one of the reasons below:-

1. SharePoint throttling (MOST COMMON) - When there are multiple refresh requests or there are large/many API calls, sharepoint starts limiting requests which results in refresh failure as the request either get's timeout or is blocked by share point
2. Too many parallel refreshes from Power BI, make sure you have some time gap for other reports and they are not hitting sharepoint at the same time
3. if you are using gateway - check gateway stability 

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[SharmilaBrisca · Accepted solution]

Hi @Gris,

Error code 10122 in Power BI's Mashup engine means the connection to the remote server timed out or was refused - not a permissions error. The intermittent, ~20% failure rate pattern is the classic signature of OAuth access token expiry mid-refresh combined with transient SharePoint Online throttling.

OAuth access tokens are valid for around 60–90 minutes. When Power BI Service connects to SharePoint Online, it obtains a token at the start of the refresh. If the data load runs long or the token isn't renewed in time, the connection drops — which is exactly why it fails randomly and not every time. 

Try the below steps:

Fix 1: Re-authenticate the Data Source Credentials 

This is the most common fix for your exact symptom.

1. Go to app.powerbi.com → Workspace → Datasets
2. Click the ⋮ menu → Settings on each affected dataset
3. Go to Data source credentials
4. Click Edit credentials
5. Set Authentication Method to OAuth2 and sign in again
6. Save and trigger a manual refresh

This resolves the issue when the OAuth refresh token used by Power BI Service has expired or gone stale — Power BI will then use a fresh token going forward. Microsoft Fabric Community

Fix 2: Switch to a Service Principal (Permanent Fix)

For SharePoint Online, configure an App Registration with a Client Secret or Certificate and authenticate using a Service Principal. Service Principals do not expire like user tokens, are not tied to individual user accounts, and offer far more stable scheduled refreshes. 

Steps:

1. Register an app in Azure Entra ID (Azure AD) with SharePoint permissions (Sites.Read.All)
2. Grant admin consent
3. In Power BI dataset settings, update credentials to use the Service Principal (Client ID + Secret)

This eliminates the problem permanently.

Fix 3: If Using an On-Premises Gateway — Update & Re-enter Credentials

Make sure the account used in the Gateway is an Owner (not just Member) on the SharePoint Site — this has resolved intermittent refresh failures for multiple organizations. Microsoft Fabric Community

Also:

• Update your gateway to the latest version
• Go to Settings → Manage connections and gateways → Edit the SharePoint data source → re-enter OAuth credentials
  
  

Fix 4: Switch SharePoint Connector URL Format

If using the SharePoint Folder connector, try switching to the Web connector instead, using the direct file URL format: https://yourorg.sharepoint.com/sites/<sitename>/Shared Documents/<filename>.xlsx

Also clear cached permissions first via: File → Options and Settings → Data Source Settings → Global permissions → Clear permissions for the SharePoint source. 

Fix 5: Stagger Your Scheduled Refresh Times

Since the failure is intermittent and affects only some reports, SharePoint Online may be throttling simultaneous requests from multiple reports hitting the same site. Spread your refresh schedules by 15–30 minutes across reports to avoid simultaneous connections to the same SharePoint site.

Why Only 20% Fail / Only Some Reports?

This is because:

• Reports with smaller datasets complete before the token window closes → succeed
• Reports with larger or more complex queries run longer → token lapses → fail
• If a data load runs over the OAuth token window, Power BI reports a connection failure — and this applies specifically to SharePoint Online as a data source

Hope this resolves your problem

Thanks and Regards

GainInsights Solution

Trusted Microsoft Partner - https://gain-insights.com/partnerships/microsoft/
www.gain-insights.com

[Gris]

Thanks Sharmila Brisca for your detail instructions! I applied several of them and will monitor to see if i can get less failures.

[grazitti_sapna · Accepted solution]

Hi @Gris,

This issue is due to unavilability of Sharepoint site, this could be because of one of the reasons below:-

1. SharePoint throttling (MOST COMMON) - When there are multiple refresh requests or there are large/many API calls, sharepoint starts limiting requests which results in refresh failure as the request either get's timeout or is blocked by share point
2. Too many parallel refreshes from Power BI, make sure you have some time gap for other reports and they are not hitting sharepoint at the same time
3. if you are using gateway - check gateway stability 

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[Gris]

Thanks Grazitti for your solution! I tried to give more time gap on scheduled refresh time for my reports, will see if that's helpful.