Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Sashidhar
Contributor
Contributor

Authentication token has expired. The user must authenticate again.

Hi,

Replication task is throwing below message but the task is not errored out.If any one have idea please let me know  how to correct this?

Error Code: 1022502
Error details: Failed to start bulk apply transaction Failed to execute bulk update statement.
RetCode: SQL_ERROR SqlState: 25000 NativeError: 11120 Message: [Snowflake][ODBC] (11120) Outstanding transactions during disconnect.
RetCode: SQL_ERROR SqlState: 08001 NativeError: 390114 Message: Authentication token has expired. The user must authenticate again.

Sashidhar

Labels (1)
1 Solution

Accepted Solutions
john_wang
Support
Support

Hello @Sashidhar ,

In general this message means the snowflake connection expired due to inactivity. By default, the connection lasts 4 hours. From other support ticket I'd like to suggest:

1- Add 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;

 

2- Add following registry keys on the Replication Server

HKEY_LOCAL_MACHINE/SOFTWARE/Snowflake/Driver/CLIENT_SESSION_KEEP_ALIVE
HKEY_LOCAL_MACHINE/SOFTWARE/Snowflake/Driver/CLIENT_SESSION_KEEP_ALIVE_HEARTBEAT_FREQUENCY
HKEY_LOCAL_MACHINE/SOFTWARE/Snowflake/Driver/ABORT_DETACHED_QUERY

Values are true, 900 and true respectively

 

3- And did the following to the user

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

Hope this helps.

Regards,

John.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!

View solution in original post

4 Replies
john_wang
Support
Support

Hello @Sashidhar ,

In general this message means the snowflake connection expired due to inactivity. By default, the connection lasts 4 hours. From other support ticket I'd like to suggest:

1- Add 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;

 

2- Add following registry keys on the Replication Server

HKEY_LOCAL_MACHINE/SOFTWARE/Snowflake/Driver/CLIENT_SESSION_KEEP_ALIVE
HKEY_LOCAL_MACHINE/SOFTWARE/Snowflake/Driver/CLIENT_SESSION_KEEP_ALIVE_HEARTBEAT_FREQUENCY
HKEY_LOCAL_MACHINE/SOFTWARE/Snowflake/Driver/ABORT_DETACHED_QUERY

Values are true, 900 and true respectively

 

3- And did the following to the user

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

Hope this helps.

Regards,

John.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
Hrishikesh
Contributor III
Contributor III

Shouldn't Replicate do this setting at each session level through the ODBC driver?

Recently I have noticed the same issue in Qlik Compose too.

Previously we were told to set: 

CLIENT_SESSION_KEEP_ALIVE=false;

 

adershb
Partner - Contributor III
Partner - Contributor III

Hi @john_wang , 

We had this error while the task full load was going on. And the table is in error state. It was a single table task. 

03190811: 2023-08-18T06:39:56 [TARGET_LOAD ]E: RetCode: SQL_SUCCESS SqlState: 08S01 NativeError: 104 Message: [Microsoft][ODBC Driver 18 for SQL Server]TCP Provider: Error code 0x68 [1022502] (sqlserver_endpoint_imp.c:5528)
03190811: 2023-08-18T06:39:57 [TARGET_LOAD ]E: Begin bulk failed (sqlserver_endpoint_imp.c:4268)

Could you please check and advise is it due to the keepalive mentioned above. Our target endpoint is Microsoft Azure SQL Database and source is Oracle. 

We can see on target end-point advanced settings there is an option called "Additional ODBC connection properties:" currently no values used here. Our replicate version is 2022.11.0.765.

 

Regards,

Adersh

john_wang
Support
Support

Hello @adershb ,

We noticed you opened a dedicated article TCP Provider: Error code 0x68, let's trace the issue in the new article.

Best Regards,

John.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!