Stored procedure works in query analyzer but not when scheduled as ajob

Question

Post reply

Stored procedure works in query analyzer but not when scheduled as ajob

JAR

SSC-Addicted

Points: 414
More actions
November 6, 2006 at 5:20 pm

#75088

I am running a stored procedure on a SQL 2000 instance that queries a linked server (Informix UniVerse, ODBC driver). This procedure works when run from Query Analyzer, but it bombs when run as a SQL Server Agent job. The latter method yields the following in the job's history (after right-clicking and starting the job in enterprise manager->management->SQL Server Agent->Jobs):
Executed as user: NT AUTHORITY\SYSTEM. OLE DB provider 'MSDASQL' reported an error. The provider reported an unexpected catastrophic failure. [SQLSTATE 42000] (Error 7399) OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x8000ffff: The provider reported an unexpected catastrophic failure.]. [SQLSTATE 01000] (Error 7300). The step failed.
The linked server properties' security tab specifies the connection is to "Be made without using a security context".
Why does this job bomb when I can run it just fine in Query Analyzer?
Any help is much appreciated.
/Jason

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 1

- wich account is jobowner ?

- how did you setup authentication for the linked server ?

is there a mapping for the jobowner ?

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

JAR SSC-Addicted Points: 414 More actions · Answer 2

Thanks for following up,

1. sa is the job owner

2. the linked server is a Unix box, and I don't know how authentication has been set up for it. There are no entries in the remote server mappings list on the security tab of the properties pages for the linked server in Enterprise Manager, and the "Be made without using a security context" radio button is selected there.

Jason

David Weber-370796 Valued Member Points: 69 More actions · Answer 3

I think that when you run a job through SQL agent it runs with the permissions of the account that agent is run under. Go to services and check the logon account that SQL agent is set up with.

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 4

I can see the "Executed as user: NT AUTHORITY\SYSTEM".

So I guess your sqlagent is being serverd by "Local System".

You may want to change that to an account that has authority to access your other box.