How to enable Gateway Connection using Direct Query for PBI Dataset and Analysis Services (preview)?

[OB]

We've created a report, combining an Excel file on-prem and a direct query connection to an existing dataset in Power BI Service, using this feature avaialble in preview.

Once published, the Power BI report requires a gateway connection per source. While the Excel gateway connection exists and is recognized, the PBI dataset gateway connection is to be created.  When clicking to create one, PBI generates the below and won't recognize my account credentials when used for the username and password fields.  

NB: the new report is published to one workspace, the dataset is in another workspace.  I have admin access to both workspaces as part of a security group, and sharing across workspaces is enabled at the tenant level for the security group that I'm a part of.  I also have read access to the underlying source of the dataset that I'm querying. 

Where else should I grant access to my account to create this gateway connection using my Windows credentials? What are the prerequisites if we were to use a another Windows account instead?

Thanks!

[OB · Accepted solution]

Thank you for the tip but we had that box checked.  However, it was a great point that led me to the fix.  

Not sure if it's like that for everyone, but in our case cloud to cloud (report to source) doesn't work with a gateway connection but only via authentication.  So here is the process:

• I mapped the gateway connection for the on-prem file,
• skipped the Optional gateway for the PBI dataset and
• clicked Apply and
• then went to Data Source Credentials,
• clicked Edit credentials and
• authenticated using OAuth2 for the PBI dataset connection. 

Then it takes a few minutes and a closing/opening of the browser tab or clicking refresh icon on the report itself to reload the page and display visuals.

[JMReece]

Even our IT department couldn't figure this out, THANK YOU SO MUCH! They literally had me rebuilding across reports/datasets instead of being able to use this functionality! 

You just saved me so many hours of work.

[OB]

Your comment made my day 🤗 Happy to know that this solution saved you time!

[giuliapiazza94]

You've saved me 😁

[OB]

Happy to hear I was of help!  It's an easy solution but not an obvious one, at least it wasn't to me 😅

[bcdobbs]

Go to Manage Gateways.

At the bottom of the gateway cluster settings tick:

At that point if you set up a folder path to the excel file on prem it all should work.

I believe the explanation is that a gateway is all or nothing.