Linked server for Oracle

Question

Linked server for Oracle

Bhavana-251907

Old Hand

Points: 325
More actions
April 9, 2008 at 10:21 am

#205250

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

Viewing 15 posts - 1 through 15 (of 19 total)

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

Piotr.Rodak SSCrazy Eights Points: 9376 More actions · Answer 1

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 Old Hand Points: 325 More actions · Answer 2

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 SSCrazy Eights Points: 9376 More actions · Answer 3

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 Old Hand Points: 371 More actions · Answer 4

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 Old Hand Points: 371 More actions · Answer 5

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 Valued Member Points: 65 More actions · Answer 6

Thank you for sharing this info John. This solution fixed the same issue I had.

daneillen SSC Veteran Points: 283 More actions · Answer 7

Thank you, this fixed the problem I too was having.

codelad SSC Enthusiast Points: 101 More actions · Answer 8

codelad

SSC Enthusiast

Points: 101

May 1, 2011 at 9:09 am

#1318186

Thanks! me too

KingCobra SSC Eights! Points: 824 More actions · Answer 9

Thanks, it worked for me too!!! Oh what a relief 😀

bswils Valued Member Points: 73 More actions · Answer 10

Absolutely golden! Thanks for the tip; you have rescued me from hours of frustration. 🙂

Eric Zierdt Ten Centuries Points: 1333 More actions · Answer 11

John Morrelles-204026 (7/1/2008)
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.

Ah, that was my issue; I added the option after creating the linked server...after deleting it and re-adding it, my problem went away.

thanks for the great catch.