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 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].
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?
I have searched the internet and some experts suggested that this is due to my user ID (i.e. \kanchan.barua) and server agent user ID (i.e. \SQLSERVERAGENT) being two different user ID. Hence I tried to change from \SQLSERVERAGENT to \kanchan.barua. But it is also showing an error. The error reads:
WMI Provider Error. Access is denied. [0x80070005].
How can I solve this error?
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.