Linked Server with Oracle Database

  • pbyrum

    SSCrazy

    Points: 2785

    I have a 64-bit SQL Server 2005 on Windows 2003 Server Standard Edition server. I have the Oracle 11g client installed, the 64 bit version. I also have an ODBC connection to the Oracle DB and that works. I have a Data Link (UDL) to access the Oracle table and that works. I also have the correct user name and password. The TNSNames.ora is good. The linked server is created successfully, and I can see where you can select Tables and Views in the Catalogs. So, everything looks good. However, when I select to view the tables, I get the following error:

    The OLE DB Provider "OraOLEDB.Oracle" for linked server "PBLA" reported an error. Access denied. Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "OraOLEDB.Oracle" for linked server "PBLA". (MS SQL Server Error 7399)

    Any suggestions?

    Thank you!

  • PaulB-TheOneAndOnly

    SSC Guru

    Points: 66773

    Some people has fixed the issue by unchecking the "Allow inprocess" option

    On SQL Server Management Studio

    Server Objects --> Linked Servers --> Providers --> MSDAORA, right click select properties

    It wouldn't hurt to try.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • pbyrum

    SSCrazy

    Points: 2785

    Thanks that fixed it.

  • PaulB-TheOneAndOnly

    SSC Guru

    Points: 66773

    Glad it worked out 😉

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • Konrad Willmert

    Old Hand

    Points: 351

    Just in case someone else needs help: I had this same problem but was using the Oracle OLE DB Provider "OraOLEDB.Oracle". I resolved the issue by turning "Allow inprocess" on.

  • 83851263

    SSC Enthusiast

    Points: 148

    Hi guys

    This site is first link then you trying to resolve error

    General access denied error The OLE DB provider "OraOLEDB.Oracle" for linked server reported an error. Access denied

    If You can't set ''Allow in process" option on provider for some reason try this

    test code

    begin tran

    exec ('select 1 from dual') at ORACLELINK

    commit

    with domain account you get error (but with sql account thee is no error!!!!)

    Msg 7399, Level 16, State 1, Line 2

    The OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLELINK" reported an error. Access denied.

    Msg 7391, Level 16, State 2, Line 2

    The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLELINK" was unable to begin a distributed transaction.

    take trace on your distrebuted transaction (or test code above) with

    events OLE DB *

    if you find error

    <hresult>-2147024891</hresult>

    <inputs>

    <punkTransactionCoord>0x0000000029C11760</punkTransactionCoord>

    <isoLevel>4096</isoLevel>

    <isoFlags>0</isoFlags>

    <pOtherOptions>0x0000000000000000</pOtherOptions>

    </inputs>

    OLE DB eroor codes

    0x80070005 -2147024891 General access denied error

    0x80004002 -2147467262 No such interface supported

    The solution is here

    http://blogs.msdn.com/b/dataaccesstechnologies/archive/2010/08/19/permissions-needed-to-set-up-linked-server-with-out-of-process-provider.aspx

  • aranda_sergio

    SSC Journeyman

    Points: 89

    Thank you Issue resolve Allow In Process:

    Issue:

    The OLE DB provider "OraOLEDB.Oracle" for linked server "TEST" reported an error. Access denied.

    Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "OraOLEDB.Oracle" for linked server "xxx". (Microsoft SQL Server, Error: 7399)

  • lup328

    SSC-Addicted

    Points: 477

    I am having similar issues with my SQL 2014 with WinServer 2012.

    When I do as suggested, this is the error I get:

    Msg 7399, Level 16, State 1, Line 3

    The OLE DB provider "OraOLEDB.Oracle" for linked server "xxxxx" reported an error. The provider reported an unexpected catastrophic failure.

    Msg 7330, Level 16, State 2, Line 3

    Cannot fetch a row from OLE DB provider "OraOLEDB.Oracle" for linked server "xxxxx".

    Any suggestion on what to do next?

  • Hany Helmy

    SSChampion

    Points: 13435

    @lup328; did you resolve your issue? as I have the same, please share the solution.

    Thanx

    Hany

  • paultormey

    Mr or Mrs. 500

    Points: 560

    Hi Hany,

    Did you try the suggested solution:

    Enable "Allow inprocess" option in the Oracle Provider for OLE DB.

    That resolves the issue.:-)

  • Hany Helmy

    SSChampion

    Points: 13435

    "Allow inprocess" option is already Enabled.

  • zorzi.z

    SSC Journeyman

    Points: 83

    If it helps anyone: just to confirm:

    SSMS 2014 - enabling "Allow inprocess" did the job for me: before I was not able to query through OraOLE... provider.

    btw: The option was not checked by default.

Viewing 12 posts - 1 through 12 (of 12 total)

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