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:

Linked server impersonation

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!

Leave a Reply

Your email address will not be published. Required fields are marked *