search

maikvandergaag / msft-extensions

Repository for extensions mainly used for Azure DevOps Extensions
https://msftplayground.com
MIT License
126 stars 80 forks source link

Operation returned an invalid status code 'BadRequest' #547

Open mvdwielen opened 2 months ago

mvdwielen commented 2 months ago

Describe the issue Error: 'Operation returned an invalid status code 'BadRequest' when executing Power BI Action: Publish

Extension

To Reproduce Create a PBI Service connection Assign service principal used in service connection admin role to workspace Create task in azure pipeline Publish .pbix file stored in devops git repo to workspace Run the Azure Pipeline

Expected behavior error should not occur, .pbix file should deploy successfully to workspace

Situation (please complete the following information):

Log info Please add the log file of the agent in debug mode [Add the variable system.debug with the value 'true ' to your pipeline]

2024-04-30T14:28:13.1975353Z Done processing Power BI Actions 2024-04-30T14:28:13.3212553Z ##[debug]Caught exception from task script. 2024-04-30T14:28:13.3245356Z ##[debug]Error record: 2024-04-30T14:28:13.3896405Z ##[debug]New-PowerBIReport : Operation returned an invalid status code 'BadRequest' 2024-04-30T14:28:13.3897666Z ##[debug]At D:\a_tasks\PowerBIActions_d5b7fec6-2b25-4742-905b-0dad97687df5\5.4.44\ps_modules\PowerBI\PowerBI.psm1:797 char:27 2024-04-30T14:28:13.3902134Z ##[debug]+ ... $report = New-PowerBIReport -Path $filePath -Name $fileToPublish -W ... 2024-04-30T14:28:13.3916641Z ##[debug]+ ~~~~~~~~~~~~~ 2024-04-30T14:28:13.3929138Z ##[debug] + CategoryInfo : WriteError: (Microsoft.Power...ewPowerBIReport:NewPowerBIReport) [New-PowerBIReport], Ht tpOperationException 2024-04-30T14:28:13.3942269Z ##[debug] + FullyQualifiedErrorId : Operation returned an invalid status code 'BadRequest',Microsoft.PowerBI.Commands.Report s.NewPowerBIReport 2024-04-30T14:28:13.3954655Z ##[debug] 2024-04-30T14:28:13.3976429Z ##[debug]Script stack trace: 2024-04-30T14:28:13.4012998Z ##[debug]at Publish-PowerBIFile, D:\a_tasks\PowerBIActions_d5b7fec6-2b25-4742-905b-0dad97687df5\5.4.44\ps_modules\PowerBI\PowerBI.psm1: line 797 2024-04-30T14:28:13.4026078Z ##[debug]at , D:\a_tasks\PowerBIActions_d5b7fec6-2b25-4742-905b-0dad97687df5\5.4.44\run.ps1: line 116 2024-04-30T14:28:13.4038243Z ##[debug]at , : line 1 2024-04-30T14:28:13.4058398Z ##[debug]at , : line 22 2024-04-30T14:28:13.4064351Z ##[debug]at , : line 18 2024-04-30T14:28:13.4077253Z ##[debug]at , : line 1 2024-04-30T14:28:13.4097618Z ##[debug]Exception: 2024-04-30T14:28:13.4268938Z ##[debug]Microsoft.Rest.HttpOperationException: Operation returned an invalid status code 'BadRequest' 2024-04-30T14:28:13.4287587Z ##[debug] at Microsoft.PowerBI.Api.V2.Imports.d8.MoveNext() 2024-04-30T14:28:13.4300319Z ##[debug]--- End of stack trace from previous location where exception was thrown --- 2024-04-30T14:28:13.4304520Z ##[debug] at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() 2024-04-30T14:28:13.4316326Z ##[debug] at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) 2024-04-30T14:28:13.4329021Z ##[debug] at Microsoft.PowerBI.Api.V2.Imports.d7.MoveNext() 2024-04-30T14:28:13.4342339Z ##[debug]--- End of stack trace from previous location where exception was thrown --- 2024-04-30T14:28:13.4354183Z ##[debug] at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() 2024-04-30T14:28:13.4366291Z ##[debug] at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) 2024-04-30T14:28:13.4379251Z ##[debug] at Microsoft.PowerBI.Api.V2.ImportsExtensions.PostImportWithFileInGroup(IImports operations, String groupId, Stream fileStream, String datasetDisplayName, String nameConflict, Nullable`1 skipReport) 2024-04-30T14:28:13.4391682Z ##[debug] at Microsoft.PowerBI.Common.Api.Reports.ReportsClient.PostImportForWorkspace(Guid workspaceId, String datasetDisplayName, String filePath, ImportConflictHandlerModeEnum nameConflict) 2024-04-30T14:28:13.4404422Z ##[debug] at Microsoft.PowerBI.Common.Api.Reports.ReportsClient.PostReportForWorkspace(Guid workspaceId, String reportName, String filePath, ImportConflictHandlerModeEnum nameConflict, Int32 timeout) 2024-04-30T14:28:13.4417595Z ##[debug] at Microsoft.PowerBI.Commands.Reports.NewPowerBIReport.ExecuteCmdlet() 2024-04-30T14:28:13.4430344Z ##[debug] at Microsoft.PowerBI.Commands.Common.PowerBICmdlet.ProcessRecord() 2024-04-30T14:28:13.4694875Z ##[error]Operation returned an invalid status code 'BadRequest'

Additional information Add any other context about the problem here.

maikvandergaag commented 2 months ago

Could you please include the complete log file? Are there any specific things about your report and how did you setup the power bi connection?

mvdwielen commented 2 months ago

[log.txt](https://github.com/maikvandergaag/msft-extensions/files/15166585/log.txt)

For security reasons I replaced the tenant id and name of the workspace / power bi file. The only thing which could be specific is that the report has a live connection to a tabular model deployed to the same power bi workspace.

I have setup the service connection by creating a new one via the user interface, I entered our tenant id and the client id and secret of the service principal which I made admin on the workspace to which the PBIX file is deployed.

image

image

maikvandergaag commented 2 months ago

Thanks, could you test with a simple powerbi? There are some limitations with the API

mvdwielen commented 2 months ago

@maikvandergaag a simple powerbi file works fine:

image

image

Does that mean I can't use your devops template to deploy .pbix files which have live connections?

maikvandergaag commented 2 months ago

These are the currently known limitations from Microsoft

Dataflows with service principal aren't supported. Importing a Power BI .pbix file from OneDrive isn't supported. Importing a file that has a protected sensitivity label isn't supported for service principals

mvdwielen commented 2 months ago

@maikvandergaag these limitations don't apply to my scenario, in the meantime I found out what the issue was.

We are working with a dev, acc and prod workspace, each workspace contains a dataset with a different id and each workspace has a separate service principal (spn-dev, spn-acc, spn-prod) and each SPN only has admin access to the dev, acc or prod workspace.

When deploying the PBIX from GIT to the DEV workspace using the DEV SPN it works fine, but when I deploy the PBIX to the ACC workspace with the ACC SPN the error occurs as the PBIX points to the dataset ID in the DEV workspace and the ACC SPN doesn't have permissions on that workspace so to that SPN this dataset ID doesn't exist.

The issue was solved after granting the ACC SPN admin permission to the DEV workspace as well and then I could use the Rebind Report task to point the deployed report to the dataset in the ACC workspace.

image

While this is a workaround which works I would prefer to change the dataset id in the PBIX before deployment in such I don't need to grant the ACC and PROD Service Principal access to the DEV workspace. I think it is more sophisticated if this can be done when getting the PBIX from git and before publishing it to Power BI Workspace. Do you have any ideas if this can be accomplished in an easy way?

maikvandergaag commented 2 months ago

Great that you have found the answer.

Not sure if that is possible. As only know the option that uses the API and for that you need the report to be power bi. The only thing that I could imagine is using a temporary workspace and moving the report after the rebind.

mvdwielen commented 2 months ago

@maikvandergaag ok thanks for your thoughts, one other option would be (which is a bit far fetched) is extracting the pbix file with a zip manager, replace the dataset id in the Connections file, zip the file again and rename it to pbix and deploy that file to power bi. While I expect that it will work it will introduce other unwanted risks so I will go for the scenario to grant the acc and prod SPN admin access to the DEV workspace and rebind the report after the deployment.

One other question, I noticed that the version of the MicrosoftPowerBIMgmt module in Power BI Actions is behind the latest released vesion: https://learn.microsoft.com/en-us/powershell/power-bi/overview?view=powerbi-ps

Are you planning to upgrade to the latest version?