Change SQL Login to AD service account for Link server

Hi,

From your explanation, I understand that you have two SQL Server instances you're trying to connect using Linked Server.

You're attempting to impersonate a "Windows Login" on the remote server.

For this configuration, the [AD/User] login has to exist on both instances, which you explain you already have in place.

You should only supply remote user/password when not doing impersonation.
If you at any point supply a username/password combo the linked server will attempt SQL Server Authentication, not AD/Windows authentication.

From the official documentation:
Create linked servers - SQL Server | Microsoft Learn

3. Select Impersonate (optional).

[...] For Windows logins, the login must be a valid login on the linked server.

To use impersonation, the configuration must meet the requirement for delegation.

Note the point about delegation.

Delegation in this context is related to Kerberos and the "double hop" issue with AD Authentication.

I have not been able to find Microsoft documentation on how to configure delegation, but here is a sqlshack article specifically on linked servers and kerberos.
How to link two SQL Server instances with Kerberos

I hope this helps you in resolving your issue.