2

I have two databases in different servers (server1 , server2)

server1: Login : login1 / password :123 /member of sysadmin (user user1,password:pass)
server2 : Login :login1 / password :123 /member of sysadmin (user user1,password:pass)

when I try to connect to server1's linked server from server2 , I can do it without issue in stored procedures.
but when I try to run sp in sql jobs in server2, this issue happens:

The job failed.The Job was invoked by User user1. Executed as user: Server2\Administrator. Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [SQLSTATE 28000] (Error 18452). The step failed.

(the owner of sql job is Login1)

Whats the point?Thank you for any help.

Improve this question

1 Answer 1

Reset to default
1

The job is executed as the SQL Agent account, unless you have configured a proxy. The owner of the job is irrelevant as long as the SQL Agent account can verify that the owner is valid (still exists in AD/SQL).

Verify which account is running the job, either the SQL Agent account or the Proxy account. Then verify that account has permissions into the linked server. Or create a proxy account for this job.

Improve this answer
2
  • thanks for reply. I set user account to one administrator user , the error is changed to "Executed as user: dbo. Remote access not allowed for Windows NT user activated by SETUSER. [SQLSTATE 42000] (Error 7410). The step failed."
    – SarahN
    Commented Oct 27, 2015 at 4:38
  • Is the owner of the job "sa"? How is the linked server configured for authentication? Check out this link. support.microsoft.com/en-us/kb/811031
    – Jonathan Fite
    Commented Oct 27, 2015 at 13:25

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.