Authentication Error on Scheduled Job

Question

Authentication Error on Scheduled Job

Paula-196779

SSCrazy

Points: 2575
More actions
January 31, 2007 at 8:51 am

#175711

Hi all,
I have an odd problem. I have a bit of SQL which connects to a linked Oracle server, queries a view and populates a table within SQL Server. When I run this in Query Analyser it works fine. When I run it as a query against a table in Enterprise Manager it also works fine. However, when I create a job and paste the code into a step it fails with an authentication error. I am logged onto the server as sa, I have sa as a user on the linked server, I created the scheduled job as sa. I'm not sure what else to try.
It is SQL Server 2000 and Oracle 8i. (also I am only using sa as I know it has full permissions and at this stage it is only on a test server - I know this is not recommended!).
Here is the code:
SELECT * INTO nlpg_oracle
FROM OPENQUERY(SADASLINK,
'SELECT * FROM NLPG_VIEW') ROWSET_1
GO
And here is the error:
Executed as user: NT AUTHORITY\SYSTEM. OLE DB provider 'MSDAORA' reported an error. Authentication failed. [SQLSTATE 42000] (Error 7399) [SQLSTATE 01000] (Error 7312) OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.]. [SQLSTATE 01000] (Error 7300). The step failed.
If anyone has any clue on what else I can try please let me know.
Thanks,
Paula.

Viewing 13 posts - 1 through 12 (of 12 total)

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

coldfusion SSC Veteran Points: 277 More actions · Answer 1

Ok when you run a job the process runs under the context of the SQL service logon account. So you need to check if that account has the relevant permissions.

Paula-196779 SSCrazy Points: 2575 More actions · Answer 2

How do I check that? The job is owned by sa - is it not run under that?

I take it the SQL Service account is not visible under 'security - logins' in enterprise manager? I can't see anything like that in the list.

Thanks,

Paula.

Cath Trimble SSCarpal Tunnel Points: 4201 More actions · Answer 3

Hi Paula,

In Enterprise Manager browse to Management and SQL Server Agent. Right click the Agent and select Properties. The account name will be on the General tab.

Cath

Paula-196779 SSCrazy Points: 2575 More actions · Answer 4

Ok thanks for this. I checked and it just has System Account ticked. It won't allow me to change it to sa. So I'm still not sure how I check the permission on System Account.

Any thoughts?

Thanks,

Paula

coldfusion SSC Veteran Points: 277 More actions · Answer 5

I think you should change the logon account to a windows account. This way you will be able to access the network.

coldfusion SSC Veteran Points: 277 More actions · Answer 6

FYI - local system account will not be able to access resources outside the server.

coldfusion SSC Veteran Points: 277 More actions · Answer 7

goto control panel/administrative tools /services

select MSSQLSERVER service

select logon tab

change logon account to windows account

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 8

since the same query works fine with QA and EM i hope this should not be a permission issue, but it should be a configuration issue. Check the way how your linked server is configured. if its configured to use the current security context then check if ur system account has permission to oracle server also.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Paula-196779 SSCrazy Points: 2575 More actions · Answer 9

Ok, I checked the security. It is 'Be made without using a security context'. I have three local logins set up, all with remote user and passwords for the Oracle database.

Any other thoughts?

Paula-196779 SSCrazy Points: 2575 More actions · Answer 10

Ok, it's fixed. I changed the SQLSERVERAGENT login to a standard login which exists on both servers, and changed the scheduled owner on the job, and also added that same account to the security list on the SQLServer, and made it the dbowner on the database. So every login I can think of is exactly the same now and it works! Hallelujah.

Thanks for your help.

Paula.

Prasanna Kumar L R Grasshopper Points: 22 More actions · Answer 11

All.

I have the same issue with SQL Server 2000. the following query works fine when executed from Query Analyzer.

select * into #VERAZCdr

from OPENQUERY(VERAZ, 'Select *

from IPVERSE.CDR_LOG

WHERE I_IAM_T >= to_date(''2007-07-12 00:00:00'',''yyyy-mm-dd hh24:mi:ss'')

and I_IAM_T < to_date(''2007-07-13 00:00:00'',''yyyy-mm-dd hh24:mi:ss'') ')

select * from #VERAZCdr

DROP TABLE #VERAZCDR

but if I create a job or a Stored Procedure with the above query it raises an error saying

[SQLSTATE 01000] (Message 0) The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction. [SQLSTATE 42000] (Error 7391) OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b]. [SQLSTATE 01000] (Error 7300). The step failed.

I tried all the solutions posted in this Forum but no luck.

Please feel free to help me out with some solution.

Thanks

Pras

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 12

How is your linked server security configured. Whats is the account under which sql server runs.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com