What are the user and privileges require to connect to a SQL server as source DB when using windows authentication?

[desmondchew]

I am setting up a new connection using a on premise SQL Server 2019  as source database.

The network firewall port seems open and not obstructing the test. However I encountered the following error while trying to use windows authentication

28000 NativeError: 18456 Message: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Login failed for user 'domain\ServerName01$'

Please see screenshot for the details error.

What built in account should I add into the source SQL server database so that I could get this working? I suppose we need the Qlik Replicate server to be added into the source SQL server. What are the required permission? What should be the name of the account to be added?

Any guide on this would be helpful.

Thank you.

Desmond

[SwathiPulagam · Accepted solution]

@desmondchew  You are trying to use windows authentication in replicate so you need to run the Replicate service under the service account and grant SA privileges to the Replicate service account on SQL Server.

Or You can create an SQL account and password on SQL Server with SA privileges and use SQL id on replicate with SQL authentication.

Thanks,

Swathi

[desmondchew]

Attached screenshot showing Qlik replicate windows server service using local system. I wonder how can we make known to source SQL server as that it allows Qlik to connects?

[SwathiPulagam · Accepted solution]

@desmondchew  You have to change the option to "this account" and give the windows service account username and password. As I said, you have to add the same windows username to the SQL login to grant SA.

Thanks,

Swathi