Power BI On-premises gateway cannot connect to database with service principal

[kvwielink]

One of our clients uses Power BI with an on-premises gateway that is running on a VM within their virtual network. The Azure SQL database which contains the report data has public network access set to disabled. Only access through private endpoints is allowed. We discovered that the authorization of the Power BI gateway connections was still configured using OAuth 2.0 using personal user credentials. We are trying to switch this to using a service principal instead.

We have created a new service principal for this purpose in MS Entra and added it to the db_datareader role on the database. However, when we are trying to configure the connection in Power BI we get the error "Unable to create connection for the following reason: Unable to connect to the data source or the connection was forcibly closed". The full error is shown in this screenshot:

To be sure the user was added correctly to the database, we temporarily switched public network access back on. When we then created a cloud connection instead of on-premises, the connection was successful so we know the principal works and the secret value is correct.

Any help as to why we are not able to configure this correctly when public access to the database is turned off would be greatly appreciated.

[kvwielink · Accepted solution]

Just to update here as well, I've contacted support and they confirmed this is currently not possible, even though the option is showing in the selection drop down when setting up the connection. It is not possible to use a service principal when connecting through an on-premises data gateway within a private network.

Thanks for the help everyone.

[lbendlin]

As the error message suggests you can open a Pro ticket at https://admin.powerplatform.microsoft.com/newsupportticket/powerbi
Otherwise you can raise an issue at https://community.fabric.microsoft.com/t5/Issues/idb-p/Issues .

[kvwielink]

Unfortunately that's not an option as I don't have a support plan available.

[GilbertQ]

Did you try the link suggested from @lbendlin 

[GilbertQ]

Hi @kvwielink 

Have you tried to log into your azure sql database by logging into the gateway virtual machine and then authenticating via the service principle to see if that works? The reason for this is to check to make sure that the gateway can access it with the service principle.

[kvwielink]

Hi Gilbert,

How would I set about doing that? I can access the VM where the gateway is running but if I try to sign in on the On-prem data gateway the only option I see is to use an email address, not a service principal.

[lbendlin]

That login has nothing to do with the operation of the gateway. It is not strictly required. (There is a bug that will prevent a new gateway version number to show up in the service until you log in, but that's it)

[kvwielink]

That's what I figured, but then I still don't understand how I'm supposed to test @GilbertQ 's comment.

[lbendlin]

On the gateway VM install SSMS or ADS, and then attempt the login to the db via SP from that tool.

[kvwielink]

Thanks for the clarification. I am able to connect from that server in SSMS using the service principal Application ID and token. The same credentials still give the same error in the Power BI Gateway connection settings

[lbendlin]

If you have a Pro license you can open a Pro ticket at https://admin.powerplatform.microsoft.com/newsupportticket/powerbi
Otherwise you can raise an issue at https://community.fabric.microsoft.com/t5/Issues/idb-p/Issues .