One Drive for Business used together with SQL Server gateway

[Anonymous]

Please help - my project is simply stuck because of this.

How do I publish to the service a working Desktop model with one dataset that has two datasources:

1. SQL Server connection (I do have a working on-premises gatway)

2. And a connection to an Excel file stored in One Drive for Business

It's the second one that gives me a hard time - how do I setup a gateway datasource for this?

The One Drive for Business is the same user as Powerbi. I also don't have a problem sharing it as a "guest link"

It looks like a simple and a common scenario - what am I missing?

Thank you very much

Michael

[v-yuezhe-msft · Accepted solution]

Hi @Anonymous,

Currently, on-premises gateway allow us to combine online data sources and on-premises data source, but it doesn’t support OAuth2 method for online data sources. In your scenario, please install personal gateway and use it instead to refresh your current dataset.

Thanks,
Lydia Zhang

[Anonymous]

@Anonymous thank you very much for trying to help!

I appreciate it very much!

@v-yuezhe-msft Lydia, thanks! I've installed the personal gateway and it is working now!

Is there a estimation for OAuth2 in on-prem gateway?

I do not want to rely on personal gateway - computer is not as steady as a server

Thank you!

Michael

[v-yuezhe-msft]

Hi @Anonymous,

Team is working on supporting oAuth based connections to go thru the on-premises gateway in Power BI, for more details, please review the discussion in this idea: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13405644-combine-enterprise-gateway-and-online-data-sources.

Thanks,
Lydia Zhang

[Anonymous]

I got stuck here for a bit myself and the problem was centred around the web address of your one-drive.  The address is shows you in "Get Link" isn't the one Power BI wants.

Simpliest way, find an excel file in your One Drive.  Open it in Excel (Not excel online, desktop excel).  Click on "File" and make sure you are in the INFO section.  You should see at the top the name of the Excel file and underneath it a file path saying "One Drive".  Click on this filepath and in the menu that appears, select "Copy link to Clipboard".

This link should give you the info you need to set up your folder as a data source in your on-premise gateway data source lists.

[Anonymous]

Thanks @Anonymous,

I already use the link to the Excel file exactly as you mentioned in my Desktop model.

Works perfectly.

But my problem is with the Gateway configuration in the service.

I have tried to use this link in "File","Web","Folder" and "Sharepoint" options - neither has OAuth2 autentication.

I have tried Windows credentials but they don't work for this link.

Were you actually able to solve this? What exactly datasource setup did you choose in your gateway?

Thank you

Michael

[Anonymous]

I have to conceded that i can't get it working either when i've gone back to try this again.  My previous solutions have been to aid other staff in their projects.  I'm wondering if I've run into the same brick wall as you have here, then i've found an alternate method and just not remember.

I apologise that i can't help further.  I'm really looking forward to the day they allow us to use Gateways on some but not all data sources.