Issue during session renewal after 4+ hours

[rondelward-pf]

We're running into an intermittent issue during the renewal process and I'm wondering if you've seen this before.

SnowflakeException: Renew session failed. Message: Authentication token has expired. The user must authenticate again.

Full stack trace

at Snowflake.Client.SnowflakeClient.QueryRawResponseAsync(String sql, Object sqlParams, Boolean describeOnly)
at Snowflake.Client.SnowflakeClient.QueryInternalAsync(String sql, Object sqlParams, Boolean describeOnly)
at Snowflake.Client.SnowflakeClient.RenewSessionAsync()
SnowflakeException: Renew session failed. Message: Authentication token has expired. The user must authenticate again.

We're using a transient SnowFlakeClient so it should be renewed each time. However it seems like something in the background may be maintaining the session and running into issues when the connection goes idle after some time.

Is the library setting the CLIENT_SESSION_KEEP_ALIVE property? Or is there some other recommended way of getting a new client to recreate the underlying session perhaps? Seems related to this issue perhaps: https://github.com/snowflakedb/snowflake-connector-net/issues/37

The client is created and provides some additional session info:

services.AddTransient((c)=> new SnowflakeClient(
                new AuthInfo(snowflakeConfig.User, snowflakeConfig.Password, snowflakeConfig.Account),
                new SessionInfo
                {
                    Role = snowflakeConfig.Session.Role,
                    Database = snowflakeConfig.Session.Database,
                    Schema = snowflakeConfig.Session.Schema,
                    Warehouse = snowflakeConfig.Session.Warehouse
                }));

Could this constructor have something to do with the renewal issues perhaps? Any recommendations for working around this issue?

[fixer-m]

@rondelward-pf,

Hello Rondel!

Thank you for detailed bug report. Let me explain how SF session gets expired first.

There are two token types which Snowflake API uses for authentication: session token - expires in 1 hour and master token - expires in 4 hours by default. When session token expires client receives exception with code 390112 and calls method SnowflakeClient.RenewSessionAsync() which uses master token to receive new session token. However in your case I believe master token has expired (390114), which should happen only if your session was idle for 4 hours.

Then there is a CLIENT_SESSION_KEEP_ALIVE property which Snowflake sends to API clients, it is false by default. It tells API client whether they should keep session alive or not. So ideally every Snowflake client should respect this option. For example, Python connector spins up a separate "heartbeat" thread which periodically sends some queries to Snowflake to keep session alive. As well as it allows to override this property allowing developer to control it on the client side. What's interesting - official .NET Snowflake Connector don't respect this option - it kinda doesn't need to, because it creates new session for every single request, so it's really hard to come up with some use case when session may expire with this approach. Snowflake.Client is another story - it tries to re-use existing session if possible.

Back to your issue. This is really weird - if client is transient it shouldn't happen at all.

1. Can you please confirm that SnowflakeException that you are getting has 390114 code (= master token has expired) and is not 390112 (= session token expired)?
2. Can you please ensure that SnowflakeClient is transient and it is a new instance every time? After making a query you can output SessionId property (SnowflakeClient.SnowflakeSession.SessionId) - it should be different for different instances of SnowflakeClient.

BTW - You can see all your sessions in Snowflake UI: AccountAdmin role > Account > Sessions, look for sessions that has Client Driver = .NET_Snowflake.Client. It can help to understand what's happening.

[rondelward-pf]

@fixer-m You are correct. This was an issue with our dependency registration that was holding onto the SnowflakeClient longer than we thought. We basically had a long lived instance of another class that was reusing the client over an extended period of time. I was able to verify we were getting the same sessionID each time showing that it was the same instance.

I think we should be all set now. Thanks for the quick response! Very handy library 🎉

[fixer-m]

@rondelward-pf That's great! Thanks!