Snowflake cloud connection: KeyPair authentication

[ustas55]

Good Day Professionals,

We have a cloud connection to the Snowflake and successfully used it with "regular" authentication via user/password. The Snowflake user was configured as SYSTEM account with Programming Access Token.

Recently, as of last week, Microsoft released an option of utilizing KeyPair authentication (which is preferred method). Unfortunately, the same model which was successfully refreshing via PAT authentication is failing to refresh with KeyPair account. Both accounts have identical privileges in Snowflake: members of the same role.

The error message I'm receiving states that: “Data source error: Resource Governance: This operation was canceled because there wasn't enough memory to finish running it….”

Any ideas of what could be wrong and/or what could be an issue?

Is KeyPair authentication still in preview mode?

Best Regards,

Sam

[ustas55 · Accepted solution]

Hi to all,

Quick update on this issue (Key-Pair authentication between Snowflake and Power BI).

Finally got a “solution” from the support and was able refresh the model using Key-Pair authentication.

What I ended up doing is to enable “Use legacy Snowflake connector implementation” within Preview features.

According to Microsoft support: “Currently, Implementation 2.0 consumes more memory, which the engineering team is actively working to optimize. This issue has been documented in the reference document below, and the document will be updated once the fix is implemented and released”.

Thank you.

[v-saisrao-msft]

Hi @ustas55,

Thank you for the update as your issue got resolved.

[arinjayjain1013]

Hi @ustas55 

I felt a new challenge using the implementation 2.0 from couple of days, Now it says :

queries are blocked by the following error:

<View_NAME>

ADBC: [Snowflake] Post "<Snowflake_server_url>:443/session/v1/login-request?requestId=***********************&request_guid=**************&warehouse=<warehouse_name>": context deadline exceeded (Client.Timeout exceeded while awaiting headers)"

[v-saisrao-msft]

Hi @ustas55,

Could you please confirm if the issue has been resolved after raising the support ticket?

Thank you.

[ustas55]

Hi,

Unfortunatelly, not yet. Support is still woring on my ticket, and I was told that ".... we're still waiting an update from our team.".....

[arinjayjain1013]

Hi everyone, 

I noticed for snowflake connector in Power BI, I see a 3rd option for KeyPair authentication. Wanted to check if it in preview mode or open for all, as I do not see a official message from Power BI. Any updates regarding the same will be helpful.
If you have any official communication, please share the link.

[arinjayjain1013]

Hi everyone, 

I noticed for snowflake connector in Power BI, I see a 3rd option for KeyPair authentication. Wanted to check if it in preview mode or open for all, as I do not see a official message from Power BI. Any updates regarding the same will be helpful.
If you have any official communication, please share the link.

[diabb8899]

Hi @arinjayjain1013 ,

I supposed it has been quietly released in Sep but they have not updated the Roadmap tracker's status yet.

Fabric GPS - Microsoft Fabric Roadmap Tracker

I would caution that you test it in your non-prod env first before doing the transition to your prod env.

The keypair auth seems to consume more resources (CPU and memory) than the normal username / password auth method and if your job is complex , it will fail.

[diabb8899]

Hi ustas55 ,

I am also facing similar errors (resource related as well) with our dataflow (gen 1) using the key pair auth, but runs fine when we switched back to username / password auth.

Something we noticed is that with the key pair auth , there are additional "junk queries" in Snowflake , e.g. SHOW queries like SHOW PRIMARY KEYS, SHOW COLUMNS ,etc...) instead of just a SELECT statement.

Do check your SNOWFLAKE query history on the account , it might be why it is using more memory than usual.

[ustas55]

Thank you diabb8899,

Yes, that's exactly what I see in the Snowflake as well; junk queries with “Show Primary Keys”, “Show Columns”, and “Show Parameters like ODBC”. I guess it’s all part of the query building/validation activities passed from Power BI (model) to the Snowflake. But I would not expect those extra queries, which generate metadata, would increase memory size from 3 Gg to 20 Gb.

[diabb8899]

Hi @ustas55 , contact MS Support and it is apparently a known and on-going issue with the Snowflake connector.

Replies from MS Support:

Feedback from the Product Team regarding this issue. Key-pair authentication is supported only with implementation 2.0. If the user does not explicitly specify an implementation, the connector defaults to 1.0 with username/password and automatically switches to 2.0 when key-pair authentication is selected.

In general, the Snowflake connector using implementation 2.0 has the following characteristics:

The overall load time is typically faster using Implementation="2.0", but the memory consumption can also be higher.

https://learn.microsoft.com/en-us/power-query/connectors/snowflake#known-issues-in-snowflake-connect... 

There still doesn't seem to have any resolution to the issue yet, I am still pending MS Suport on that.

[ustas55]

Hi @diabb8899,

Same story here, waiting on support. I have a ticket open, which has been escalated already.

Regarding connector, I did try both options mplementation options, but no success.

[diabb8899]

Hi @ustas55 , 

yeah... I guess we have no choice but wait for the support to provide a resolution on this.

I have suggested to them if they can downgrade to Implemention 1.0 for the keypair auth since that is the working implementation that runs with the username and password.

[anilgavhane]

@ustas55 

KeyPair authentication for Snowflake is supported in Power BI, but it may behave differently than PAT. The memory error likely stems from query execution context or resource settings under the KeyPair identity.

Quick Fixes:

• Compare session parameters between PAT and KeyPair logins.
• Check Snowflake warehouse size and scaling policies.
• Use logging to inspect query performance.
• Ensure private key format and JWT setup are correct.

Let me know if you want help testing with smaller queries or reviewing session settings.

[ustas55]

Hi Anil,

Both account (PAT and KeyPair) have identical security profiles, assigned to the same withing the Snowflake, so the Warehouse size and/or session/query parameters (in my opinion) should be the same.

[tayloramy]

Hi @ustas55, 

Can you raise this as a microsoft ticket so that the support team can have it logged as a defect? Bugs don't get fixed unless they are reported. 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution. 

[ustas55]

Hi Taylor,

Yes, we do have a ticket open, but not much of the traction on it as of yet.

Once I have some updates from the support, I will update this thread.

[mohitkumawat]

Hi @ustas55 ,

Error  "Resource Governance" usually denotes that a memory threshold was passed during the processing/refresh of your data, and we never see that associated with authentication as much as how big the data is or how complicated the query is.

Solved: Error: Resource Governing - Microsoft Fabric Community

Go back to use PAT/User/Password method temporarily for testing in order to ensure the model still updates successfully. This removes the problem to entirely be in our Key Pair auth setup.

[ustas55]

Thank you Mohit. 

that's exactly what we did, switch the model to use PAT user and model refreshed successfully. I assume that keypair authentication in the cloud is not fully functional?