Dataset based on dataflow missing credentials when published by service principal

[alexschindler]

Hi all

I use a service principal (AAD app registration) to publish a PBI dataset with TabularEditor command line tool to workspace1 using [1]. The dataset is based on a dataflow in workspace2. The dataflow gets data through a gateway from an on-premise SQL database.

Publishing works fine, but the dataset is not able to refresh. If I click on the refresh button I get the error "at least one data source is missing credentials" as in [2].

If I take over the dataset with my personal user refreshing works fine. However, I want to be able to refresh through the service principal.

I'm very glad if somebody has insight into this. Thanks a lot!

Some more information:

- The service principal is admin on workspace1, workspace2

- The service principal is also admin on the gateway and was added to the relevant data sources. (Though in my understanding that should not be relevant because the data is persisted in the dataflow.)

- "Allow service principals to use Power BI APIs" is enabled

- "Allow service principals to use read-only admin APIs" is enabled

- I tried looking at the dataset's connection details using the REST API. The connection details show a gateway ID (as in [3]) even though the dataset itself is not based on a gateway. If I look up the details of the gateway I get [4]. I understood it such that this gateway is some techincal placeholder for the service principal's credentials - but I'm not sure if they are set wrong because of the "useEndUserOAuth2Credentials=False" part.

[1] .\TabularEditor.exe "<my-datamodel>\database.json" -D "powerbi://api.powerbi.com/v1.0/myorg/<my-workspace>;User ID=app:<my-application-id>@<my-tenant-id>;Password=<my-password>" "<my-dataset-name>" -O -C -P -V -E -W -R
-M

[2]

[3] 

[4]

[ibarrau]

Hi. That's a weird issue. I'm not familiarized with the Tabular Editor feature to publish, but if you can publish from PowerBi Desktop and it doesn't ask you for the credentials again, then it sounds like there is an issue with the feature.

If Power Bi Desktop works, you can try the Rest API to build your own publish script. You can read more about that in here: https://learn.microsoft.com/en-us/rest/api/power-bi/imports/post-import-in-group

You can also use libraries to help your code like SimplePBI for python or Power Bi Cmdlets for powershell.

I hope that helps,

[alexschindler]

Thanks for answering ibarrau!

I'm trying to do everything more code-based since I want to have an automated way to publish datasets, so I am trying to avoid using PBI Desktop for this.

I managed to use the REST API to publish reports, but directly publishing datasets did not work for me. In the repository my dataset is in directory structure (exported from Tabular Editor).

I can publish the model through the TabularEditor command line tool (which maybe converts the model under the hood and then also uses the REST API underneath).

For either SimplePBI or PBI-commandlets I did not see a way to publish datasets in directory structure.

My problem might have to do with how the service principal's credentials are propagated.

[ibarrau]

Hi. I have used simplepbi for this. There is a request at imports category that can read a pbix from the local file system and publish to the workspace you want. This is the doc of the method.

I'm not sure whtat Tabular Editor is doing. I have suggested about Power Bi Desktop Publish just to check if it works to understand if it's something about the command line or if something about the source/dataset/report.

I hope that helps,

[alexschindler]

Hi ibarrau

Thanks for this.
However, I am not using pbix files for the data model. I would like to do version control on the data model - this is why I use directory structure (with the json files) such that everything is text-based. So before I can publish the dataset needs to be re-converted. TabularEditor does the conversion and then pushes to the REST API.

Since publishing actually works I'm under the impression that the problem is not really with the publishing part - unless there is some problem with how the service principal's credentials are propagated - but rather with some settings on the PBI Service part.

Thanks.

[ibarrau]

Oh. I didn't realize about the version control directory. That might be a problem. Do you have premium/dedicated capacity? I'm wondering if Tabular Editor is really using the API or just using the XMLA endpoint to set the new changes there. Tricky. Just in case I can tell you that I use repo + pbix files. Two people can't edit at the same time but you can keep development history.

I'm not sure you would be able to fix this. It's a weird underground the way of the publish and files. It looks like no pbix no keeping credentials 😞

Regards,

[alexschindler]

Thanks, but I still think it has to do with the service principal and not primarily with the way of publishing the dataset (or maybe with the combination). If I use the TabularEditor command line tool to publish with my own user rather than the service principal, the credentials are stored fine and the access to the data source (dataflow) is working.

We use premium per user.

And yeah, I know we could work with the PBIX files, but I would prefer if we could also do branching and merge two different edits. I'm hoping that this will work with the directory/JSON based structure.