Power BI Service believes on-premise Sharepoint list is cloud based - won't work with data gateway

[Anonymous]

We connect to our on-premise Sharepoint via on-premise Office 365 accounts, not Windows accounts. When registering to Power BI with our corporate emails, the Microsoft account made isn't connected to the on-premise Office 365 account.

So this happens:

I make a report in Power BI Desktop and connect to a Sharepoint list. The only way to connect is using 'Microsoft Account' (also referred to as Organizational Account). Even though I am signed into Power BI, I have to click sign-in, and I am signed into this on-premisis Office 365 account via SSO (Windows Single Sign On).

When I publish this report to Power BI Service, I do not have the Microsoft Account option (I believe because I am already signed in to MS account when logging in to Power BI Service?). It appears Power BI Service believes my on-premise data source is cloud based. The credentials throw an error and it will not allow me to use a data gateway (personal or enterprise) for cloud based data.

Note there is no way for me to connect to Sharepoint in Power BI Desktop via Windows Account, at least using any I've tried (and I've trawled through these forums looking at solutions for others). I have tried using all 3 kinds of Sharepoint data connections (Sharepoint folder, Sharepoint list, Sharepoint Online list), tried including/excluding our network domain in the username field and tried using the root URL. Access Forbidden.

Any ideas? Thanks

[GilbertQ]

Hi @Anonymous 

Would it not be possible to change the way you connect to the data?

Have you possible tried connecting via Web and not the SharePoint requirements?

[Anonymous]

No luck - Can't get web to work, I can get Odata to but only if authentication type is set to Microsoft Account. Again this doesn't work in Power BI Service.

I've traced it in fiddler and authentication to our Sharepoint via browser is cookie-based. Also discovered the sharepoint root/_login URL and although windows authentication is an option it doesn't work, it only allows ADFS logins.

Not seeing any way in which to get this to work - the on-premesis sharepoint site can only be accessed through our corporate VPN, it's blocked from external networks. And Power BI Service is giving me no option to use a data gateway with this Sharepoint list as it believes it's in the cloud.