June 12, 2007 at 11:57 am
We have SQL Server 2005 running. It has a nightly JOB running which comprises of several steps one of which gets executed only ONCE a MONTH (determined by the flags in the table). Everytime after that job gets executed (which runs for about 15 minutes) the NEXT STEP (sometimes several consecutive steps) fails to run with the message:
SQLServer Error: 18456, Login failed for user 'US\roamer'. [SQLSTATE 28000]
Any idea/clue why this is happening and any proposed solutions?
Thanks,
Tushar
June 12, 2007 at 12:17 pm
What does that step do?
June 12, 2007 at 12:26 pm
It executes bunch of stored procedure within the database. But at one point it executes xp_cmdshell with 'BCP' statement with username and password required to access the file in a particular folder.
June 12, 2007 at 1:11 pm
If you look in the SQL Error log there should be some more details. Like the Severity and the State. If you look up 18456 in Books Online it will tell you why it threw that error (this is suppresed back to the calling connection).
June 15, 2007 at 4:58 am
Does the account under which the jobs runs have enough permissions to the folder. Also is it connecting to a remote server to access the folder. If so what account your sql services run.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 11, 2007 at 4:12 pm
It seems like I have figured out the problem. We had the Auto_Close dboption set to TRUE. So for every step of the SQL Agent, the db connection has to be reestablished. I had turned that option to off (Which is default setting anyways) and the problem has gone away!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply