May 30, 2002 at 4:07 am
Here's an odd one. I have a scheduled job which runs a batch file to build a Cognos cube. This has been running fine until our infrastructure was upgraded to Win2000, then it suddenly started to fail. The build works when I run the batch file, but when SQL Server Agent runs the batch file the model is opened but does not build. I think this is a security issue, perhaps SQL Server Agent is causing the logon to the AS400 or the SQL Server database where the data for the build comes from to fail. Any tips for troubleshooting this would be much appreciated. I have tried using SQL Profiler to track what is going on but have only determined that the model is not logging in to the database, I get no useful information in the profile.
May 30, 2002 at 5:33 am
Does sound like permissions, which I'd say is the most common issue with Agent anyway. Runas is a useful way to test this, do "runas batchfilename" and run it under the agent account, see what happens.
Andy
May 30, 2002 at 5:34 am
How is the logon with SQL AGent defined. Is it local system account or a domain account? If the later have you tried logining in as SQL Agent and testing to make sure it's login still works?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 31, 2002 at 10:29 am
Thank you very much for your help guys, I found that the login is valid on the new system but that you have to cache the login to the AS400 under the security profile for the SQL Server Agent logon separately, whereas previously we were getting away with just logging in as the domain administrator on the build server. Your tips were very helpful in fixing this and I learned how to use runas which could be quite useful in the future. Thanks very much, Jacqui.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply