Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Linked server for Oracle Expand / Collapse
Author
Message
Posted Wednesday, April 9, 2008 10:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 12, 2011 12:29 PM
Points: 3, Visits: 75
Hi,

I am having this weird problem with Oracle linked server on SQL 2005 x64. I have installed Oracle client and 64bit oracle odbc drivers. If I run the below query with Windows authentication it is giving me the below error. But surprisingly if I run it as SQL Account it is running perfectly fine without any errors.

I think the error message is misleading but I can't think of a reason why the linked server would work for SQL Account and not for Windows authentication.

Any ideas?

Query:

SELECT USR_LGN_CD, USR_STS_IND
FROM OPENQUERY(cwcin11d,'SELECT * FROM ccwas_usr' )

Error:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "cwcin11d" reported an error. Access denied.
Msg 7332, Level 16, State 2, Line 1
Cannot rescan the result set from OLE DB provider "OraOLEDB.Oracle" for linked server "cwcin11d". Unknown provider error.

Thanks
Bhavana
Post #482481
Posted Wednesday, April 9, 2008 11:01 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 10, 2012 9:49 AM
Points: 800, Visits: 1,759
Open properties of the linked server and look on the Security tab. There on the bottom of dialog, there are four options related to connection credentials. If you have selected "Be made using the login's current security context" I believe Oracle will not authenticate you as most likely it does not support Windows authentication.
But this is my guess only.

HTH

Piotr


...and your only reply is slàinte mhath
Post #482496
Posted Wednesday, April 9, 2008 12:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 12, 2011 12:29 PM
Points: 3, Visits: 75
Thanks for the response. I am using a different username and password to authenticate to Oracle and it is not a sql login or windows login.

Thanks
Bhavana
Post #482538
Posted Thursday, April 10, 2008 2:39 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 10, 2012 9:49 AM
Points: 800, Visits: 1,759
Is that login set up in linked server properties? Does by any chance that login and password for Oracle match your SQL login?

Piotr


...and your only reply is slàinte mhath
Post #482834
Posted Monday, June 23, 2008 1:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 5, 2014 9:10 AM
Points: 9, Visits: 84
Similar problem with SQL 2005 x64 and Oracle 11g client library. Second error message is:

"Cannot get the column information from OLE DB provider "OraOLEDB.Oracle" for linked server "x".

And, oddly, I get an empty result set with the column headings displayed, so the column information error message seems a little uncertain.

I can connect to the Oracle server using the Oracle Provider for OLE DB in a Data Link UDL file, but that could be 32-bit. But in either case it isn't MSADORA, which was working on SQL 2000 32.
Post #522153
Posted Tuesday, July 1, 2008 1:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 5, 2014 9:10 AM
Points: 9, Visits: 84
Just to follow up, a call to Microsoft support revealed that editing the Provider Properties (right-click the provider in the providers folder) and ticking the "Allow inprocess" option corrects my problem.

Existing linked servers will not pick up the provider setting, so new linked server objects will have to be created. But the new ones work.
Post #526876
Posted Wednesday, January 28, 2009 10:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 1:09 PM
Points: 1, Visits: 72
Thank you for sharing this info John. This solution fixed the same issue I had.
Post #645089
Posted Thursday, February 26, 2009 8:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 17, 2013 1:52 PM
Points: 4, Visits: 36
Thank you, this fixed the problem I too was having.
Post #665062
Posted Sunday, May 1, 2011 9:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 13, 2014 7:17 AM
Points: 1, Visits: 165
Thanks! me too
Post #1101319
Posted Tuesday, June 28, 2011 8:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:43 AM
Points: 24, Visits: 483
Thanks, it worked for me too!!! Oh what a relief
Post #1132906
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse