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.
Best Answer
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.