Forum Discussion
Quick Fix for OAuth2 or “Organizational Account” Authentication Support in Power BI Report Server
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
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 UTF8Write-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.
11 Replies
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 UTF8Write-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.
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.
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.
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?
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.
-
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.
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.
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.
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.
