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.
Run "SQL Server Configuration Manager"
Select "SQL Server Services"
Right Click "SQL Server Agent" and choose properties
On the "log on " tab ,choose "this account".
Click "Browse" button , then Click "Advanced" button.
Click "find now" button . Now find your account in the below table and select it.
When you selected proper account (With sufficient permission) , return to "log on" tab and enter your password
Restart service and try again.
Check the below link, likely a security issue, the user that runs the job needs access on the other server...
User contributions licensed under CC BY-SA 3.0