Automating PBI workspace creating and Publishing reports using service principal

[Anonymous]

Hello all,

I am utilizing a service principal (AD-Azure app) in Azure DevOps to programmatically create a PBI workspace, assign a capacity to it, and then publish a report into the created workspace. My data source is in Databricks and the connection type is DirectQuery. The service principal has been added as an admin to the workspace and also has been given access to the Databricks. However, when trying to open reports in the workspace, it doesn't load and gives the following error "The data source Extension is missing credentials. Ask the dataset owner <My_service_principal> to update the credentials."

Note: the Dataset Credential section is grayed out for me because the service principal is the admin and should remain as the admin.

please advise what can be done to avoid that error.

[tackytechtom]

Hi @Anonymous ,

This is an interesting one! 

I have done something similar for an Azure SQL Database and wrote a blog article about it here:

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

There, we needed to fetch an access token from https://login.microsoftonline.com/ for the resource https://database.windows.net/ and then assign the access token by updating the data source. 

It could be that you need to do something similar. In the case of databricks you might be able to do this 

with the resource = https://<databricks-instance>.azuredatabricks.net/ but I am not sure as I have never tried it. Also, be aware that the access token will most likely expire, which provides some challenges especially since you are using direct query. We did it in import mode and updated the token always right before we triggered a refresh. To avoid this issue, you might need to update the credentials with a PAT from databricks instead of an access token from AD.

These are my thoughts on it. Please, keep me in the loop here, in case you come up with a solution yourself or if one of the ideas I posted worked out. I am really interested in how you handle this, too 🙂

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