Unable to connect to Synapse Lake DB from Power BI report in PBIRS

[bryonwilliams]

This seems like it may be a limitation in the current version of Power BI Report Server (PBIRS), though I'm hoping we're just doing something wrong. If it is a limitation, I would love to see if we could get this on the product roadmap (product team!). 

In short, we have a Dataverse environment for which we have enabled Synapse Link, which creates a Lake DB database on synapse. In this style of database, SQL user authentication is not supported (the DB is exposed as a readonly replica, and thus we cannot "create user" on the DB). As a consequence, the only supported way to connect to the Synapse database is using organization/microsoft accounts. Using the PBIRS version of Power BI desktop, we are able to author such reports, work with datasources, etc. just fine. However, when it comes time to deploy to PBIRS, the only authentication methods that are available when configuring the data source are Windows Auth (i.e., on-prem windows/active directory) and Basic Auth (SQL users).

Long story short, even though you could create a SQL user and grant the sysadmin role, which would allow the user to access the lakedb database, that user does not have permission to access the underlying resources in the Azure Storage Acccount (the data lake), and thus you cannot run queries or interact with the data.

It seems that either the PBRIS platform needs to add support in data source configuration for data model data sources for Microsoft/Organizational autentication (see the screenshot below), or the Synapse team needs to figure out how to support SQL authentication on Lake DBs.

Has anyone encountered and gotten past this problem? Are we maybe missing something? 

Separately, any chance the product team is interested in picking this up and supporting MS auth?

thanks!!

[bryonwilliams]

This is unfortunate. This means that with the direction the Dynamics teams are heading with exposing data for reporting and analytics (using Synapse Link), PBIRS will be totally unusable as a reporting surface. 

I really hope the direction on this eventually changes!

[Icey · Accepted solution]

Hi @bryonwilliams ,

//Separately, any chance the product team is interested in picking this up and supporting MS auth?

No, this is by design. Power BI Report Server does not support OAuth-based authentication for model refresh.

Reference: Power BI report data sources in Power BI Report Server

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.