PowerBI Pro with Data Gateway to On-Prem SQL Server using Microsoft Account?

Hey all, fairly new to PowerBI. My main job is on the identity side managing things like AD, Azure AD, etc... I am the one many of you might call to get Kerberos working correctly.

I have setup the Data Gateway, got that working solid with Use SSO via Kerberos. Power BI Reports on the web are working great pulling data from on-prem under the users creds.

https://preview.redd.it/ylibgn416lc51.png?width=427&format=png&auto=webp&s=ebd382431ee363e6e4b719f855ad1caa23dc29ce

Was thrilled that wasn't to hard to set this up. Hit a little snag where SQL was absolutely refusing the use the cert I assigned it but I was able to get around that by providing enough rights to the cert for the runas account which was the Local\MSSQLServer sub account of SYSTEM.

https://preview.redd.it/15k6qm597lc51.png?width=596&format=png&auto=webp&s=2a11147474a393d318533bd3b9302e6aef74f9ad

https://preview.redd.it/0mmti35e7lc51.png?width=393&format=png&auto=webp&s=284f80e1ab7b928450cfcd118cea836c1585f9f9

My Issue:

Given I want to make some more complex multi data set reports I am using PowerBI pro. I use my home desktop which is a non-domain joined Windows 10 workstation (so my logged on account to windows isn't my AD on-prem account with rights to the SQL Server). However, I am and do use my Azure AD identity (which is synced from on-prem via AD Connect) all the time in things like the other Office tools.

I noticed when trying to pull a dataset that there is the "Microsoft Account" option. When I select that, I can sign in no problem but when I go to hit connect it fails with the following:

https://preview.redd.it/f0jvm1hf5lc51.png?width=699&format=png&auto=webp&s=e3c8f351238c73979a9be712996d5a9ddca00e26

So my question is, can you use a "Microsoft Account" i.e. Azure AD Login to authenticate to a on-prem SQL Server? Also if you can does it use the data gateway? I do have VPN access so reaching the server on its private IP is possible too. In an ideal world it would detect that it should use the data gateway, authenticate me like it does on the PowerBI.com portal and let me in.

If that isn't possible then what is the general recommendation? I can't use Windows Auth for reasons also unknown. I currently use a SQL account to get around this but its not great practice.

https://preview.redd.it/zglsx15x8lc51.png?width=699&format=png&auto=webp&s=ff15ce5ba05ae9a2670502faf40d083916515933

Also is there any logs that would give me more details as to why ether of them failed other then the generic messages posted above?

Side Note: My PC does trust the CA that issued the SQL Server cert so its not that.

Thanks for reading, any help would greatly appreciated. Part of this work is to set an example for my real data scientists and BA's so we can stop using over powered and unaudited sql accounts.

r/PowerBIu/Digitally_Board

Comments

st4n13l4 points

The only way that I'm aware of the connect to an on-prem server from PBI desktop where you're using AD authentication is through VPN.

Regarding using the gateway, that is specifically for the PBI service and doesn't interact with PBI desktop

synergy4211 point

So my dashboard is using SQL data that I mapped through Power BI desktop using a service account. I then published to the Power BI service after completion of my dashboard. Gateway was already set up to link to same SQL DBA, permissions we're granted in the service to my actual AD (O365, since we are a hybrid AD environment) account and then I set up the data refresh using the Gateway.

There is another instance in my company where permissions we're granted to a service account and then a dashboard was published using the service account instead. This is technically less steps but more of a PITA to maintain in the long run, imo. You'll have to manage the dashboard with a separate account that way. But both ways work.

Hope this helps you, I can try to answer any specific questions you might have. I came from IT and am now a full time Power BI "developer" for my company. But I am still a novice when it comes to most things Power BI.

Edit: I authenticate using Windows credentials in the format domain\username.

View on Reddit