Linked server for Oracle

  • Bhavana-251907

    SSC Veteran

    Points: 265

    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

    SSCrazy Eights

    Points: 9296

    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

    SSC Veteran

    Points: 265

    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: 9296

    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: 341

    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: 341

    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

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

  • daneillen

    SSC Enthusiast

    Points: 182

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

  • codelad

    SSC Enthusiast

    Points: 101

    Thanks! me too

  • KingCobra

    Right there with Babe

    Points: 714

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

  • bswils

    Valued Member

    Points: 73

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

  • Eric Zierdt

    Ten Centuries

    Points: 1333

    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.

  • rderisala1

    SSC Veteran

    Points: 257

    Thanks. It addressed my problem

  • a.istrate

    Grasshopper

    Points: 17

    Thanks, worked like a charm !

  • john.saldanha

    SSC Enthusiast

    Points: 174

    Thanks John, I was stuck with the same issue, your recommendation got me through.

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

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