Have you ever needed to create a SQL Server Agent job that hits a linked server? Getting this to work properly had caused me to pull out some of whatever hair I had left. I kept getting the following error:
Executed as user: NT SERVICE\SQLSERVERAGENT. Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [SQLSTATE 28000] (Error 18456). The step failed.
Luckily, I stumbled onto a solution.
Essentially, you need to map the NT Service\SQLSERVERAGENT user to the remote user on your linked server entry. Setting Job or Step to run as a user impersonating a valid remote user will not get the job done.
In order for this to work correctly you need to go to your linked server properties screen:
Add an entry for “NT Service\SQLSERVERAGENT”, and enter the appropriate Remote User and Remote Password.
Run your SQL Agent job, and everything should work as expected!