0

My idea is to insert records from a table into another one in different server using T-SQL.

I've made maintenance plan along with its job and found below error.

Executed as user: NT SERVICE\SQLSERVERAGENT. Login failed for user 'MANYAR\MIT-DB2$'. [SQLSTATE 28000] (Error 18456)Unable to open Step output file. The step failed.

I suspect SQLSERVERAGENT from source server might not have permission in destination server. I've tried to execute the T-SQL script in query and the scripts work well.

2
  • Are you using a linked server or one of the functions like OPENDATASOURCE? The second server will definitely need the service account SQL Agent is using added in as a login - hopefully this is an AD account?
    – Jhunter1
    Commented Aug 29, 2019 at 14:37
  • Are you using a linked server or one of the functions like OPENDATASOURCE? (Yes, I used linked server). The second server will definitely need the service account SQL Agent is using added in as a login (There's already sql agent built in login and I've changed from sql agent to my own login since it has administrator role in both AD and 2 linked-sql server
    – Michael
    Commented Aug 30, 2019 at 8:08

1 Answer 1

0

I believe you are seeing two different errors on your history report.

  1. You should validate the step output file on the job step(s).
  2. You should validate the linked server permissions & 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

enter image description here

Start the job

enter image description here

Resulting error:

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

Testing with insufficient permissions

enter image description here

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

enter image description here

Authenticate with a non existing login

enter image description here

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

enter image description here

Point to a non existing output file

enter image description here

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 & the linked server permissions and authentication and you should be good to go.

2
  • thanks Randi, unfortunately it still doesn't work. The problem occurs when insert command executed by batch job sql server agent, but it works well if the command executed using query window.
    – Michael
    Commented Aug 30, 2019 at 1:10
  • When you execute using command window, it uses your account whereas in case of batch job(sql server agent), it uses service account which is used to start sql server agent service. Please use domain account to start the service and grant this domain account access to the server where is your target and it is supposed to work. Commented Aug 2, 2021 at 12:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.