AR_H_USER populating as NULL for Microsoft SQL Server source (Log Stream) and Kafka target

[chadbaldwin]

We have a task that is replicating data to Kafka using a Microsoft SQL Server source (reading from a Log Stream file, which was populated by a Microsoft SQL Server task, not CDC).

We want to apply a global rule that filters out all changes committed by a specific user.

I started off with testing this against one table instead of a global rule filter, however, the filter was not working.

So, then I switched it over to be a simple Add Column Transformation where I am mapping `$AR_H_USER` to a new column named `QLIK_AR_H_USER`.

When looking at the messages being sent to Kafka, the new `QLIK_AR_H_USER` column is showing as null.

I thought maybe I had done something incorrectly, so I added another Transformation column which mapped `$AR_H_OPERATION` to a new column named `QLIK_AR_H_OPERATION`. After stopping the task and reloading the target, I reviewed the messages sent to Kafka and I properly see `QLIK_AR_H_OPERATION` getting populated with appropriate values, yet `QLIK_AR_H_USER` is still null.

I also made sure to test both with implicit transactions as well as explicit transactions, neither made any difference.

Our server is running version 2024.11.0.577 and the source Microsoft SQL Server is running SQL Server 2022.

I can't seem to find anyone else complaining about this issue. The only thing I could find is that for the May 2021 release this was a known issue that was "fixed":
Qlik Replicate Release notes - May 2021 Initial Re... - Qlik Community - 1981408

[john_wang]

Hello @chadbaldwin ,

The behavior cannot be reproduced in my lab environment and works as expected.

I am running Qlik Replicate 2025.5 with SQL Server 2019 as the source, using LogStream. The global transform rule defined in the child task (LogStream → Kafka) is configured as follows:

Add column for %.% column 'USER' data type STRING(50) and sub type Regular with $AR_H_USER

I got the message like:

If the issue still occurs in your environment, I recommend opening a support ticket and providing the following information:

1. Diagnostics packages from both the parent and child tasks

2. Child task logs with source_capture/target_apply set to Verbose, after reproducing the issue

3. The decrypted verbose log file from the child task attached to the ticket

Our support team will be happy to further investigate and assist you with this issue.

Hope this helps.

John.

[chadbaldwin · Accepted solution]

After working with Qlik support, it turns out the issue is due to how SQL Server stores SIDs in `sys.server_principals`.

When a transaction is committed, a SID is saved to the execution log to record which account committed the transaction.

In order to resolve the SID back to a human readable username, Qlik Replicate looks it up in `sys.server_principals`.

The problem is that SQL Server only stores SIDs in `sys.server_principals` for accounts that authenticate with SQL Authentication, or if you are using Windows Authentication, then SQL Server only stores the SID for the domain group and any domain users that have been explicitly granted a logon.

This means if you only grant a domain group permission, then only the SID is stored for that group in `sys.server_principals`, and Qlik Replicate is unable to resolve the SID back to a username. The only way around this is to use `SUSER_SNAME(SID)`, but this can cause potential latency (according to Qlik Support). However, from what I've read, SQL Server should be caching those locally anyway.

In other words, if you only grant a domain group a logon, `AR_H_USER` will be NULL. But if you use...

CREATE LOGIN [yourdomain\username] FROM WINDOWS;
CREATE USER [yourdomain\username] FOR LOGIN [yourdomain\username];

Then this will force SQL Server to store a row in `sys.server_principals` for Qlik Replicate to use for resolving SID to username.

So to sum it up...if you want `AR_H_USER` to be populated, either use SQL Authentication, or explicitly grant a logon for the domain user you want to track.

Personally, I would like to get confirmation from Qlik that they have tried/tested using `SUSER_SNAME(SID)` and measured the latency to see if it's actually an issue. Because if it's not, then it could be a game-changer to have the username attached to every event.

[john_wang]

Thank you for the update @chadbaldwin !

[chadbaldwin]

No problem! I just went ahead and re-wrote it to provide more detail after further testing on our end and working with Qlik.