Forum Discussion
Power BI On-premises gateway cannot connect to database with service principal
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.
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.
11 Replies
- GilbertQ
Super User
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.
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)
- 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 . 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.
