PBI Report Builder connection to Snowflake

[ustas55]

Good Day Power BI Professionals.

I'm trying to establish connection from Report Builder to Snowflake via "standard" Snowflake connector using Snowflake authentication but running into "Invalid credentials" error: "Invalid credentials. (Session ID: 1f1d371b-1bcc-4785-aa69-588109e9f0f8, Region: us)"

The connection I'm using has been successfully created as a cloud connection in the service. And I successfully connecting/refreshing semantic models using very same connection.

However, Report Builder does not want to cooperate.

Per documentation, this functionality should work.... but it doesn't.

Any advice/recommendations/pointers?

Best Regards,

Sam

[Akash_Varuna]

Hi @ustas55Could you check this post maybe this might help
 https://community.fabric.microsoft.com/t5/Desktop/Report-Builder-connect-to-snowflake-via-proxy/m-p/...

[ustas55]

Thank you Akash,

Yes, I did see that post. I would assume if Microsoft deployed features that are publicly available, and not in preview, the functionality should work. Also, my biggest concern is availability of deploying ODBC connections to the cloud/service. How that functionality is achievable?

Regards,

Sam

[v-achippa]

Hi @ustas55,

Thank you for reaching out to Microsoft fabric community forum.

Thank you for the response, I completely understand your concern. Here the discrepancy is from a known limitation, i.e Power BI Report Builder currently does not support using cloud connections like the semantic models do.

So even though Snowflake connection works perfectly in the service, but the report builder needs a separate ODBC based connection and it cannot reuse the cloud connection automatically.

Use an ODBC driver like Snowflake ODBC with your credentials in the report builder.

When you publish to the service, configure the credentials again in the dataset settings for the paginated report, this is separate from semantic model credentials. These credentials are stored securely and allow the service to connect to Snowflake. No gateway is required unless your Snowflake environment is private.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa

[ustas55]

Thank you Anjan,

I did try via ODBC route. I have created local ODBC connection which I'm using on desktop. However, when report published to the service, I have no options to overwrite connection property nor data source credentials. For credential I see :admin has granted access, credentials are not required”.

I am trying to avoid on-prem data gateway connections. Unless I am missing something fundamentally with this functionality and configuration.

Regards,

Sam

[v-achippa · Accepted solution]

Hi @ustas55,

Thank you for the update and I really appreciate your detailed explanation. As I already mentioned Power BI Paginated Reports(Report Builder) do not support cloud connections like semantic models do. Even though your Snowflake cloud connection works in service, it cannot be reused or recognized by paginated reports.
While the Report Builder can connect to Snowflake via an ODBC driver locally, once published to the service, ODBC based data sources are always treated as on-premises.

This is like by design and currently the only supported way to use Snowflake with paginated reports in the service is:

• Create a local ODBC DSN for snowflake and use it in Report Builder to build your report.
• Publish the report to service.
• Then configure an On-premises Data Gateway and map it to your Snowflake data source.

You will be able to manage credentials and refresh the report without any issues.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa

[ustas55]

Thank you Anjan,

I did create ODBC connection on the On-premises Data Gateway and successfully published/executed RDL report in the service.

Best Regards,

Sam