Failed to insert records into table in different server using sql server agent
I believe you are seeing two different errors on your history report.


You should validate the step output file on the job step(s).
You should validate the linked server permissions &amp; authentication.




Recreating the step output error

Testing with a location that does not exist

Create a jobstep that tries to access a step output folder location that does not exist

Regular T-SQL job steps might report the error but not fail the job, I used a maintenance plan to force the failure here



Start the job



Resulting error:


  Executed as user: AgentServiceAccount. Unable to open Step
  output file.  The step failed.


Testing with insufficient permissions



The same error is returned when no permissions are granted


  Executed as user: AgentServiceAccount. Unable to open Step
  output file.  The step failed.




Recreating the linked server permission error in combination with the step output error

Create a linked server



Authenticate with a non existing login



Create a job and jobstep to select from the newly created linked server



Point to a non existing output file



With the final resulting error on job execution matching yours


  Executed as user: AgentServiceAccount Login failed for user
  'nonexistinglogin'. [SQLSTATE 28000] (Error 18456)Unable to open Step
  output file.  The step failed.


Validate the job step output &amp; the linked server permissions and authentication and you should be good to go.