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.

  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?

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.

  1. Run "SQL Server Configuration Manager"

  2. Select "SQL Server Services"

  3. Right Click "SQL Server Agent" and choose properties

  4. On the "log on " tab ,choose "this account".

  5. Click "Browse" button , then Click "Advanced" button.

  6. Click "find now" button . Now find your account in the below table and select it.

  7. When you selected proper account (With sufficient permission) , return to "log on" tab and enter your password

  8. 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...

http://www.sqlserverblogforum.com/2012/06/login-failed-for-sqlstate-28000-error-18456-the-step-failed/

  • 2
    Welcome to ServerFault. Whilst this may theoretically answer the question, it would be preferable to include the essential parts of the answer here, and provide the link for reference. – masegaloeh Jul 15 '14 at 1:20

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.