Quick Fix for OAuth2 or “Organizational Account” Authentication Support in Power BI Report Server

[mvyas]

Hi Everyone,

Our team is currently using Power BI Report Server (PBIRS) and is facing the challenge that OAuth2 or “Organizational Account” authentication types are not supported for scheduled data refreshes. We're looking for a quick fix or workaround to enable authentication with SharePoint Online or other cloud-based services requiring OAuth2, without moving to Power BI Service or migrating to Azure in the immediate future.

Can anyone recommend a solution or best approach for integrating these authentication methods in PBIRS, considering our environment and requirements? We are not planning to transition to the cloud anytime soon, so we need an on-premises workaround or approach.

Any suggestions or best practices would be greatly appreciated!

Thank you in advance! @riccardomuti 

[v-kathullac]

Hi @mvyas  ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya.

[v-kathullac]

Hi @mvyas  ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya.

[v-kathullac]

Hi @mvyas  ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya.

[v-kathullac]

Hi @mvyas,

Using a data gateway is a better option please find the below points for better understanding.

• The On-premises Data Gateway is required if the Power BI Service needs to access on-premises data such as SQL Server, Oracle, or SharePoint Server.

• It allows scheduled data refreshes, DirectQuery, and live connections from cloud-based Power BI reports.

• You can control traffic flow and keep sensitive data within the firewall.

• It supports enterprise-level security and authentication, such as  OAuth2 etc...

• A single gateway can connect to multiple datasets and reports, with access managed centrally.

• It is ideal for organizations with multiple teams or shared on-premises data sources.

• It enables scheduled refreshes for files on shared network drives, especially Excel and CSV files.

• In my experience, a data gateway is almost always part of the architecture when working with legacy systems or on-premises databases.

• It is reliable, secure, and integrates well with enterprise-level reporting needs.

Regards,

Chaithanya.

[mvyas]

@v-kathullac : Thanks for the information. I agree to all this. The only concern is that data gateway works good with Power BI service and not with on prem report server. Am I correct?

[v-kathullac]

Hi @mvyas,

the answer for your question is YES here are the few detailed points.

1) Power BI Report Server does not use the Data Gateway.

2) It connects directly to on-premises data sources.

3) All data sources must be accessible from the report server machine.

4) Scheduled refresh is configured in Report Server Configuration Manager, not via Power BI Service.

Regards,

Chaithanya.

[mvyas]

Thanks @v-kathullac 
I am looking for an approach which would be a quick fix. We are not planning to move to cloud immediately and more of a gradual process as you might know.

For the approach you suggested where we "Store Files Locally via OneDrive Sync", won't it cause data redundancy and heavy on cost? Please let me know if I understood this correctly. Please share more details if I am taking a wrong approach. Below is the diagram to explain:

Let me know your thoughts and Thank you in advance! @v-kathullac 

[v-kathullac]

Hi @mvyas ,

Thank you for reaching out to Microsoft Fabric Community Forum.

I think you are already considering a solid strategy to manage the potential cost and complexity issues associated with syncing files between OneDrive and local storage I agree that the key challenges here revolve around data redundancy, increased infrastructure costs, and the added complexity of managing multiple copies of data.below are few points which might generally occurs.

• Syncing files with OneDrive locally and using Power BI can lead to data redundancy, increasing storage costs by keeping duplicate copies in both the cloud and local systems.

• Managing large datasets (TBs) across multiple locations increases infrastructure costs, including storage and network bandwidth.

• Use a hybrid approach by syncing only essential files to OneDrive and keeping others locally, gradually transitioning to cloud-based solutions as infrastructure grows.

• Consider cloud backup services like AWS S3 or Azure Blob Storage for large datasets, instead of syncing all files via OneDrive.

• Plan a gradual cloud transition, assessing which parts of your infrastructure can be shifted without significant redundancy and focusing on areas where the cost is manageable.

• Use Power BI Dataflows or Azure Data Lake to connect to cloud data sources directly instead of relying on synced files
• Automate file movement using tools like Power Automate, Azure Logic Apps, or custom scripts to push only needed files to OneDrive

Regards,

Chaithanya.

[v-kathullac]

Hi @mvyas,

Thank you for reaching out to Microsoft Fabric Community Forum.

Power BI Report Server (PBIRS) does not support OAuth2 (including "Organizational Account") authentication for scheduled data refreshes. Below are the few workaround you can try: 

1)Use Scheduled Data Export with Power Automate or PowerShell:

• Set up a Power Automate flow to export SharePoint Online List/Excel content to a OneDrive or File Share.
• Use a script or task scheduler to download the data from OneDrive to your on-prem folder.
• In Power BI Desktop (for PBIRS), use the Folder or CSV data source.
• Publish to PBIRS and schedule the refresh using a Windows credential.

2)Store Files Locally via OneDrive Sync:

• Use OneDrive sync client to sync a SharePoint document library locally.
• Point Power BI to the synced folder (e.g., C:\Users\YourName\SharePoint Docs.)
• Schedule refresh in PBIRS as it's now reading a local file (Windows auth works).

PowerShell Script to Export SharePoint List to CSV:

Install-Module -Name "PnP.PowerShell" -Force -Scope CurrentUser

# Define variables
$siteUrl = "https://yourtenant.sharepoint.com/sites/yoursite"
$listName = "YourListName"
$outputCsvPath = "C:\PBIRSData\SharePointData.csv"

# Connect to SharePoint Online
Connect-PnPOnline -Url $siteUrl -Interactive

# Get list items (you can use -PageSize and -Fields if needed)
$listItems = Get-PnPListItem -List $listName -PageSize 500

# Convert to CSV format
$data = $listItems | ForEach-Object {
$item = $_.FieldValues
[PSCustomObject]@{
ID = $item["ID"]
Title = $item["Title"]
Created = $item["Created"]
Modified = $item["Modified"]
Status = $item["Status"] # Example custom field
AssignedTo = $item["AssignedTo"] # Example custom field
}
}

# Export to CSV
$data | Export-Csv -Path $outputCsvPath -NoTypeInformation -Encoding UTF8

Write-Host "Data exported successfully to $outputCsvPath"

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,

Chaithanya.

[mvyas]

@v-kathullac Thank you for your response. Appreciate all the details you shared. One more question for you: Is using a data gateway a better option too? Would love to hear your experience and thoughts.