Multiple CDC tasks failing with "An unexpected error occurred while trying to retrieve access token”

[MoeE]

Hi,

A client's CDC tasks are frequently failing intermittently with the following error after a windows patch last month. 

RetCode: SQL_ERROR  SqlState: 42000 NativeError: 105117 Message: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]An error occurred while authenticating against Managed Service Identity: 'An unexpected error occurred while trying to retrieve access token.’

The target is Azure Analytics. I've researched the error online and haven't found much indication to the issue. Has anyone seen this before? The task stops, then can be resumed again. I am also seeing these two errors every time this occurs

[TARGET_APPLY    ]E:  Failed to load data of file CDC000013BF.csv to database 

[TARGET_APPLY    ]E:  Failed to copy data to net changes table [1022505]  (bulk_apply.c:6413)

Help with this is appreciated. Thank you

Kind regards,

Mohammed

[john_wang]

Hello Mohammed, @MoeE 

Thanks for the post here.

RetCode: SQL_ERROR  SqlState: 42000 NativeError: 105117 Message: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]An error occurred while authenticating against Managed Service Identity: 'An unexpected error occurred while trying to retrieve access token.’

It appears that the issue is more likely related to the latest Windows patch or the SQL Server ODBC Driver.

The error indicates that the Managed Identity token failed to be retrieved from the Azure Instance Metadata Service (IMDS).
Possible causes include:

• A temporary Azure MSI (Managed Service Identity) outage
• Network connectivity issues between the host and IMDS

In summary, these errors reflect a Synapse client/server communication problem.
This issue should be reviewed by the user’s DBA or escalated to Microsoft if necessary.

If you would like to investigate further, we suggest the following steps:

1. Check the SQL Server ODBC Driver version 

2. Review driver timeout period — by default, the ODBC driver waits only a few seconds to retrieve the MSI token. If IMDS responds slowly, authentication may fail.

3. Set TARGET_APPLY to Verbose, reproduce the issue, and review the detailed task log files.

4. Enable ODBC tracing to capture additional diagnostic details.

Hope this helps.

John.

[ChristopheL]

Hi John,

We have exactly the same problem. You say to review the timeout period, does this mean to add for instance ConnectRetryCount=20;ConnectionTimeout=30 in the ODBC Attributes on the endpoint connection for Azure for the additional connection properties so it will retry 20 times and wait for a timeout of 30 seconds? 

Thanks in advance,
Christophe

[john_wang]

Hello @ChristopheL ,

Thanks for the update. I could only find the parameter SQL_ATTR_LOGIN_TIMEOUT (SQL_LOGIN_TIMEOUT) on the Microsoft webpage. Please check if this is helpful.

Regards,

John.

[MoeE]

Hi Christophe,

I'm curious, did the issue begin appearing after Windows patching? Is your QR instance on an Azure VM, and is your target SQL Server or Synapse?

Regards,

Mohammed

[ChristopheL]

Hi MoeE,

I think it started when we updated the ODBC driver for SQL Server on the unix server that hosts Qlik Replicate from a version 17 to a version 18, but since it's done by an infrastructure team I'm not sure about it.

I can confirm however that the sollution I posted above seems to be working. Since I implemented it everywhere yesterday I finally had a night without any issues in the Qlik Tasks.

Kind regards,

Christophe

[ChristopheL]

Hi Mohammed;

i forgot to answer, but our target is Azure Synapse indeed and QR is on a virtual machine, a linux server.

Kind regards,

Christophe

[john_wang]

Thank you so much for the feedback, @ChristopheL ! Your input is truly valuable for everyone.

[MoeE]

Hi @ChristopheL,

Thanks for the response. Is this the solution that you tried?

Or did you implement @john_wang's solution to add the "SQL_ATTR_LOGIN_TIMEOUT (SQL_LOGIN_TIMEOUT)" parameter? Or only what is mentioned in the screenshot above?

Thanks,

Mohammed

[ChristopheL]

Hi Mohammed,

yes the screenshot is the solution I applied. ConnectioTimeout and SQL_ATTR_LOGIN_TIMEOUT are said to be the same in fact.

Kind regards,

Christophe