Support OAuth 2.0 SSO Authentication Without Client ID/Secret in Power BI Connector for Trino

[shohamyamin]

Currently, the Power BI connector for Trino supports OAuth 2.0 authentication, but it requires users to provide both the client ID and client secret for the connection. However, many environments that use SSO for OAuth 2.0 (such as enterprise setups) do not expose the client secret to end-users. Instead, users authenticate via a web-based SSO flow, as commonly seen with tools like DBeaver, which opens a browser window for authentication without needing a client ID/secret configuration.

[dwolfeu]

This is a very good point, but alas I don't have the capacity to look into at the moment. I suspect someone with better knowledge of OAuth/SSO could make short work of it. One further point: In the current setup, whereby one populates the files oauth_config_client_id.txt etc. and then builds the .mez file, one can unzip the .mez file and see the values of said files in plaintext. That's not good.

[pichlerpa]

This would be the implementation guide for making this possible: https://learn.microsoft.com/en-us/power-query/handling-authentication#microsoft-entra-id-authentication

[sonofenoch]

Could this change get implemented?

Maybe you can clarify why it was done this way, but usually authentication is handled by the application itself. OAUTH returns a JWT which handles the authentication and authorization with the database.

The way it's set up now, it's closer to authenticating Power Bi or the connector itself, not the database.

[pichlerpa]

@tjcsilicon No, this change hasn't been implemented yet.

The current implementation was contributed by @dwolfeu, and it was likely done this way because the Client Credentials Flow is the simplest OAuth 2.0 flow to implement. You are right, in this setup, the connector authenticates directly with the database, meaning it doesn't involve any user identity or context.

I believe, what you're suggesting is to implement the Authorization Code Flow passing on the user's identity and enabling user-based access control as outlined here in the documentation, right?

[sonofenoch]

@pichlerpa From what I can tell, the connector is not authenticating with the database, it is merely authenticating directly with the IDP and passing the access token to the database. More akin to JWT authentication.

The distinction I'm making is:

1. The Authentication process doesn't start with trino, it goes directly to the client that Power BI was provided with. Hence forcing the client secret to be exposed.

2. If this even works at all in a production IDP setup I'd be surprised, that's assuming the infrastructure team even provides you with a plaintext client secret. That being said, you can't list Oauth2 as an authentication for the database you are connecting to, because authentication is handled directly by PowerBI.

3. You mentioned this https://learn.microsoft.com/en-us/power-query/handling-authentication#implementing-an-oauth-flow. They warn against using client secrets in the extension itself.

Authentication flow I am requesting should be about this:

1. PowerBI goes to user-provided uri. i.e. https://trino.myDomain.
2. Trino returns a 303 and gives authentication URI https://keycloak.myDomain/auth/realms/myRealm/protocol/openid-connect/auth?response_type=code&redirect_uri=.... and so on. This link is COMPLETELY provided by trino.
3. PowerBI opens browser and directs to authentication URI from step 2.
4. User enters username and password or whatever authentication mechanism you are using and submits
5. Keycloak accepts username and password and issues a token. JWTs keep track of expiration date so Trino will know when you need to login again and issue another 303 when sending a request.
6. present this token to trino and you're done.

Note: this is my understanding of typical Oauth flow. Might have slight errors.

I would implement this myself but do not have any experience writing powerbi connectors. Figured it would be faster to make an issue

[pichlerpa]

@tjcsilicon That's exactly the distinction between the two authentication flows I mentioned.

The key difference is that the current implementation uses hard-coded client ID and secret as credentials to request the access token whereas the other one is using the user credentials in an interactive way (via browser-based sign-in) to obtain the token. However, the authentication process is always initiated by Power BI or the connector and the IdP (Identity Provider) is always the one issuing the access token — the difference just lies in how that token is requested.

While this current approach does raise some security concerns, in setups where Power BI development is centralized and access to the credential-containing file is tightly controlled, it might be an acceptable trade-off.

That said, enabling interactive sign-in is definitely the preferred approach — not only from a security standpoint but also for supporting user-based scenarios such as row-level security or audit logging tied to specific user identities.