search

microsoft / powerbi-azure-pipelines-extensions

MIT License
36 stars 14 forks source link

Data Source Connection Not Working #17

Closed himanshugarg1ext closed 1 year ago

himanshugarg1ext commented 2 years ago

Hi,

I've been trying to deploy reports from Azure DevOps in to our workspaces. We have created the relevant service connections with a Machine User. In Dev, we deploy the PBIX files kept on Azure Repos using a third Party task in our Build pipeline. We are using Directquery mode and SSO Authentication Mode to our Data Source (Azure Synapse, Dedicated Pool).

For deployment to QA and PROD, we are using "Deployment Pipeline" module but we interact with it using this "Power BI Automation Tools" in our Release Pipelines.

Now the reports are getting deployed, so there is no error in the execution logs which I can mention here.

The problem is when we try to access these reports, we are displayed this message: The Data Source is missing Credentials and cannot be accessed.

Power BI Issue

I checked, The owner of the deployed dataset is the Machine User used in Service Connection. This Machine User has Power BI Pro License, all relevant access - on the Data Source (Synapse Dedicated SQL Pool), Deployment Pipeline, The Power BI Workspace. Even though that should not matter, since the report is deployed with - "Use End-user's own credentials to access underlying data."

Anyway, as soon as I just take over this deployed report (not doing anything else), the report starts showing up data and the error is gone. I'm not able to understand this behavior.

Please help as we plan to deploy our reports in Production using automation and can't seem to find any working way for this!

I can provide more details in a call if we can facilitate it. I also have a Azure DevOps support ticket created for this: 2208290050000527.

MahirDiab commented 1 year ago

Hi, power bi deployment pipelines doesn't copy credentials as part of the deployment. see Item properties that are not copied

in case of cloud connection - no on-prem gateway is being used, credentials are saved on the user level. which mean different datasets owned by the same user, and use the same connection, share the same credentials. so when you take over the dataset, it tries to use some credentials you already have on the system , and i assume it finds one and that's why it works.

this issue happens after a "clean copy" only (first time deploying the dataset) which mean if you run the same process again and deploy the same datasets, your own user will remain the owner and all should work fine.

so depending on your exact scenario, here are some options to resolve this:

  1. after first time deployment of dataset take over with your own user and set credentials if needed
  2. using the machine user credentials - set the credentials using Power BI Rest APIs - you can see this document describing the process with the .NET SDK Configure credentials programmatically for Power BI or using Powershell see example