Refresh Dataset using SPN

[vamshikrishna20]

Hello Team, 

Hope everyone doing well !

My question is on Azure Devops and Power BI. I have around 200 reports planned as part of project scope and development goes phase wise and we have multiple environments(DEV,TEST,PROD). For the Deployment of Reports to higher environments we are using Devops pipeline using Service principal Authentication. with current pipeline and using Power shell scripts in it, we were able to deploy reports and update the dataset details to higher environment DB details and all these reports are on direct query. But we are unable to refresh the reports using this SPN. it says dataset is missing credentials . Please help us to get solution for this. Our SPN has access to database.

Thanks in Advance

[tackytechtom]

Hi @vamshikrishna20 ,

I think as part of your devops cicd and release process, you have to explicitly set the dataset credentials. What I usually do is to let a service principal (I prefer to use a different one than the deploying service principal) to take over the dataset after I deployed the changes. As part of that taking over procedure, the devops pipeline updates the credentials, using an access token fetched from https://login.microsoftonline.com/ . Note the access token will expire after some time, which is a problem, especially if you are using direct query. In import mode, you could update the access token right before you trigger a dataset refresh. If you are planning on using access tokens, you need to refresh it on a regular schedule before it expires, maybe with a runbook? Otherwise you could of course also update the credentials accessing the database with another method (i.e. sql authentication).

Here a blog post that uses the "taking over dataset and updating access token" approach.

https://www.tackytech.blog/how-to-make-a-service-principal-the-owner-of-a-power-bi-dataset/

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/