Power BI and Snowflake KeyPair authentication, mystery solved

Double check the connection string!

Prelude

Back in May we helped a customer in the transition from Snowflake basic authentication to stronger authentication methods.

The customer’s architecture had Azure Data Factory as the orchestration system, Snowflake as the data warehouse, and Power BI as the presentation layer, or better, the semantic model layer.

If you want to read the full story click the link below.

[embed]Snowflake Wants Stronger Authentication. Your Connectors May Not Be Ready A real-world look at ADF, Power BI & Fabric implementationmedium.com

Long story short, the only strong authentication method that Azure Data Factory supports, when defining linked services to Snowflake, is KeyPair authentication. That means creating a private key on top of which public keys are generated in order to connect to Snowflake. On the other hand, Power BI supports key pairs and Microsoft account authentication, meaning leveraging Microsoft Entra to connect to Snowflake.

After many attempts we ended up with a hybrid setup where Azure Data Factory leverages KeyPair while Power BI uses Microsoft account authentication, because we had troubles using the same KeyPair method in Power BI.

Even if it was working, we decided to get in touch with Microsoft to better understand the issue because, as per documentation, Power BI Desktop and Service support KeyPair authentication on Snowflake.

Diving in again

As I mentioned in my previous article, I’m not a Snowflake expert and I worked on this task as an accidental Snowflake developer without having much support from the customer’s Snowflake administration team. During our first contact with Microsoft engineers, we had the confirmation that KeyPair authentication should work in Power BI Desktop and Service. But at the very beginning we weren’t able to make it work, even if the exact same setup was working correctly with Azure Data Factory (if you want to read the full story click here).

You may know how this kind of troubleshooting works. Most of the time you do a lot of trials and error and at some point you completely lose track of what you’ve already tried and what you haven’t. So at some point I decided to stop trying to switch the authentication in our existing Power BI Desktop files and started from scratch with an empty Power BI Desktop file. Without doing anything different in the connection configuration, I made it work.

After that I confirmed that the key and the passphrase were correctly used by Power BI Desktop and I tried to understand what was the difference between the working Power BI Desktop file and the one that wasn’t (obviously the problem wasn’t related to key and passphrase). Randomly I noticed that the two connections were almost the same (even if giving access to the exact same set of databases), with just one exception: it was the name of the Snowflake service.

The one that was working with KeyPair authentication had a connection string made like this:

companyname_division.west-europe.azure.snowflakecomputing.com

Image showing the connection working with KeyPair in Power BI Desktop

The one that wasn’t working had the exact same connection string but with hyphen ( — ) signs instead of underscore ( _ ):

companyname-division.west-europe.azure.snowflakecomputing.com

Image showing the connection not working with KeyPair in Power BI Desktop

Again, I’m not a Snowflake expert and I didn’t understand what I was looking at because the connection string was written that way from the very beginning. The semantic model is a Power BI project in TMDL, therefore I could easily track changes and confirm that the connection string had been using minus signs for years.

So I started searching on Google for something that could explain what I was experiencing and I found official Snowflake documentation saying that names with hyphen signs and underscores can be treated interchangeably, but some features may not work correctly in some cases.

So what I did was replace the hyphen sign with underscores and everything started working smoothly, also in the older file.

Making this change made the connection work with KeyPair authentication even on the Service side.

Very stupid issue, you may think, but very difficult to catch because you have different behaviors according to the environment, for example, you have no issue with both formats in ADF and you get different experiences between Power BI Service and Power BI Desktop both leveraging Microsoft Account (I again ended up with the following summary table after a set of trials and errors).

Image showing different product/authentication combinations and if they support hyphen or not

Conclusions

All’s well that ends well. If you’re experiencing troubles working with KeyPair authentication over Snowflake in Power BI, double check the connection string and verify if you have some hyphens replacing underscores. Snowflake can treat them interchangeably but, as per documentation, some features do not support it correctly.