topic Re: Authentication token has expired. The user must authenticate again. in Qlik Replicate

Authentication token has expired. The user must authenticate again.

Sashidhar — Mon, 04 Oct 2021 14:06:30 GMT

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

Re: Authentication token has expired. The user must authenticate again.

john_wang — Sat, 09 Oct 2021 09:53:49 GMT

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.

Re: Authentication token has expired. The user must authenticate again.

Hrishikesh — Thu, 25 Nov 2021 12:01:06 GMT

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;

Re: Authentication token has expired. The user must authenticate again.

adershb — Mon, 21 Aug 2023 07:07:09 GMT

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

Re: Authentication token has expired. The user must authenticate again.

john_wang — Wed, 23 Aug 2023 04:05:30 GMT

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.