How to set the CLIENT_SESSION_KEEP_ALIVE on a Snowflake connection in Qlik Replicate

[DataGuyAtHeart]

For Change jobs where Snowflake is the target, I am getting: 

RetCode: SQL_ERROR SqlState: 08001 NativeError: 390114 Message: Authentication token has expired. The user must authenticate again. [1022502] (ar_odbc_stmt.c:4614)

I believe I need a way to set sessions Keep Alive in order to avoid this.  How do I configure this for a Snowflake connection?

Thanks!

[lyka · Accepted solution]

Hello,

For the error:

RetCode: SQL_ERROR SqlState: 08001 NativeError: 390114 Message: Authentication token has expired. The user must authenticate again. [1022502] (ar_odbc_stmt.c:4614)

I suggest adding the below value to the ODBC connection string for the snowflake target endpoint: 

CLIENT_SESSION_KEEP_ALIVE=true;ABORT_DETACHED_QUERY=true;CLIENT_SESSION_KEEP_ALIVE_HEARTBEAT_FREQUENCY=900;

And the following to the user:

Please  change the setting for the snowflake user properties, NOTE that this is not set in replicate but on Snowflake end:

alter user user_name set CLIENT_SESSION_KEEP_ALIVE = TRUE;

alter user user_name set ABORT_DETACHED_QUERY = TRUE;

alter user user_name set CLIENT_SESSION_KEEP_ALIVE_HEARTBEAT_FREQUENCY = 900;

Some links for your reference:

https://docs.snowflake.net/manuals/sql-reference/parameters.html#client-session-keep-alive

https://docs.snowflake.net/manuals/user-guide/odbc-parameters.html#connection-parameters

Thanks

Lyka

[DataGuyAtHeart]

Thanks for all of the great info! I will try it today.

[DataGuyAtHeart]

This worked like a champ - thanks again @lyka 

[alla2]

@lyka  Can you give me where to add this ?

CLIENT_SESSION_KEEP_ALIVE=true;ABORT_DETACHED_QUERY=true;CLIENT_SESSION_KEEP_ALIVE_HEARTBEAT_FREQUENCY=900;

[SwathiPulagam]

Hi @alla2 ,

Please add the below mentioned value to snowflake endpoint-->Advanced-->Additional ODBC connection properties: CLIENT_SESSION_KEEP_ALIVE=true;ABORT_DETACHED_QUERY=true;CLIENT_SESSION_KEEP_ALIVE_HEARTBEAT_FREQUENCY=900;

Thanks,

Swathi

[alla2]

don't see Additional ODBC connection properties in Additional tab 

[shashi_holla]

If your endpoint is Snowflake on Google then might not see it. 

It's there for Snowflake on Azure and AWS:

Thank you,

[alla2]

Thanks Shashi, any idea how to do this for Google too ?

[SwathiPulagam · Accepted solution]

hi @alla2 ,

For Snowflake Google endpoint please follow the below steps:

Go to Snowflake endpoint-->Advanced-->Internal parameter-->add this additionalConnectionProperties and provide the below string as value: CLIENT_SESSION_KEEP_ALIVE=true;ABORT_DETACHED_QUERY=true;CLIENT_SESSION_KEEP_ALIVE_HEARTBEAT_FREQUENCY=900;

Thanks,

Swathi