January 31, 2007 at 8:51 am
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.
February 1, 2007 at 2:23 am
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.
February 1, 2007 at 3:18 am
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.
February 1, 2007 at 3:43 am
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
February 1, 2007 at 4:24 am
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
February 1, 2007 at 4:54 am
I think you should change the logon account to a windows account. This way you will be able to access the network.
February 1, 2007 at 5:05 am
FYI - local system account will not be able to access resources outside the server.
February 1, 2007 at 5:09 am
goto control panel/administrative tools /services
select MSSQLSERVER service
select logon tab
change logon account to windows account
February 1, 2007 at 6:29 am
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
February 6, 2007 at 6:29 am
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?
February 6, 2007 at 7:27 am
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.
July 12, 2007 at 5:13 pm
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
July 13, 2007 at 2:09 am
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply