SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Linked server for Oracle


Linked server for Oracle

Author
Message
Bhavana-251907
Bhavana-251907
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 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
Piotr.Rodak
Piotr.Rodak
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2770 Visits: 1761
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
Bhavana-251907
Bhavana-251907
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 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
Piotr.Rodak
Piotr.Rodak
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2770 Visits: 1761
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
John Morrelles-204026
John Morrelles-204026
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 96
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.
John Morrelles-204026
John Morrelles-204026
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 96
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.
Damerz
Damerz
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 91
Thank you for sharing this info John. This solution fixed the same issue I had.
daneillen
daneillen
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 36
Thank you, this fixed the problem I too was having.
codelad
codelad
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 252
Thanks! me too
KingCobra
KingCobra
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 498
Thanks, it worked for me too!!! Oh what a relief :-D
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search