A quick one today caused by an interesting error I received. A client dropped me a message that they were running a stored procedure fine as themselves but when they dropped it into a SQL Agent Job it started to fail. The message was pretty clear:

“Executed as user: Domain\SQLAgent. Login failed for user ‘ Domain\SQLAgent ‘. [SQLSTATE 28000] (Error 18456). The step failed.”

I like problems that are easy to solve, just check the Domain\SQLAgent account on the SQL Instance and find it’s….enabled…and has all the permissions required to run this stored procedure? Hmmm.

Then I noted that despite getting a login failure message there was no corresponding login failure message in the SQL Error Logs. Time to dig into the code and I was able to identify that the stored procedure references a linked server. That linked server was (Hooray) set up to log in to the remote server in the context of the executing account. So the login wasn’t failing locally, it was failing on the remote server. Sure enough I could see login failure messages on the remote server that matched the times the job had tried to run.

Resolving the remote login failure allowed the job to run successfully. Hopefully this post will point you to your potential error and save some time banging your head against a wall!