Log In failed for user error 18456

2

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.

sql-server
sql
login
asked on Server Fault Mar 15, 2014 by Sadat Mainuddin

2 Answers

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

answered on Server Fault Apr 26, 2015 by Revanth Kumar • edited Apr 27, 2015 by Andrew Schulman
0

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/

answered on Server Fault Jul 14, 2014 by AZ Chad

User contributions licensed under CC BY-SA 3.0