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

Question

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.

Answer 1

I've opened a support ticket for this with Microsoft and they confirmed that it is currently not possible to use a service principal to authenticate on a database when you use an on-premises data gateway in a private network. This, despite the fact that the option is available when setting up the connection. Guess it's back to user authentication...