Linked Server with Oracle Database

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Working with Oracle » Linked Server with Oracle Database

Linked Server with Oracle Database

Rate Topic

Display Mode

Topic Options

Author

Message

pbyrum

Posted Tuesday, January 13, 2009 1:32 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 05, 2012 10:58 AM
Points: 101, Visits: 199

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!

Post #635800

PaulB-TheOneAndOnly

Posted Saturday, January 17, 2009 7:42 AM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:34 PM
Points: 3,016, Visits: 4,466

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.

Post #638690

pbyrum

Posted Monday, January 19, 2009 10:04 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 05, 2012 10:58 AM
Points: 101, Visits: 199

Thanks that fixed it.

Post #639417

PaulB-TheOneAndOnly

Posted Sunday, January 25, 2009 12:45 PM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:34 PM
Points: 3,016, Visits: 4,466

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.

Post #643224

Konrad Willmert

Posted Wednesday, November 02, 2011 12:51 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, November 02, 2011 12:49 PM
Points: 9, Visits: 40

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.

Post #1199570

83851263

Posted Friday, February 24, 2012 3:13 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 15, 2013 10:42 AM
Points: 1, Visits: 87

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

Post #1257273

« Prev Topic | Next Topic »

Permissions