I have two servers. Both of the servers have two different database that will be joined together using SQL Server Agent. However, during this activity SQL Server is throwing a Log In Failed Error. The error shows:
1. The job failed. The job was invoked by User somedomain\someuser. The last step to run was step 2 [somedatabasename]. The job was requested to start at step 1 [somejobstepname].
2. Executed as user: NT SERVICE\SQLSERVERAGENT. Login failed for user 'somedomainname\servername'. [SQLSTATE 28000] [Error 18456]. The step failed.
Can anyone help me with this?
PS: One thing I forgot to mention. I can normally run a query using both the database in separate server. However whenever try to use server job agent, this connection problem is showing.