OraOLEDB.Oracle Linked Server

Question

Have an Issue trying to call a SQL View which calls a Oracle Linked server via OPENQUERY.  This is all done from inside access.  The issue is that when I remove the user from the local administrator group on the SQL server.  I get the following error:

ODBC call failed.  
[Microsoft][ODBC SQL Server Driver][SQL Server] Cannot create and instance of OLE DB provider "OraOLEDB.Oracle" for linked server .... (#7302)

The issue is that I can't leave this user in the local Administrator group and I need to use Windows Authentication.  It doesn't matter if the account is a sysadmin on SQL, you still get the error unless the user is in the local windows Administrator group.

Answer 1

Hi Richard,

Please first follow the solution in this blog:

http://www.sqlcoffee.com/Troubleshooting091.htm

We found that a new release of the Oracle client was available (win64_11gR2_client or 11.2). Once we installed the new version and enabled the option "Allow in process", the linked server worked correctly. We were able to expand the Tables container on the linked server.

If the problem still occurs,

There are two ways to trace the error:

• In Query Analyzer, run the following code to turn on trace flag 7300:DBCC Traceon(7300).
• Capture the "OLEDB Errors" event that is located in the "Errors and Warnings" event category in SQL Profiler. The error message format is the following:Interface::Method failed with hex-error code.

The error number of 7302 indicates that the MSDAORA.dll file may be registered correctly. You can try to register it or reinstall the MDAC. For more information, please refer to this KB article:How to set up and troubleshoot a linked server to an Oracle database in SQL Server.

Also, you can follow this article to overcome the dll related to issue step by step:http://www.sqldbadiaries.com/2010/12/27/cannot-create-an-instance-of-ole-db-provider-oraoledb-oracle-for-linked-server/.  I find a thread about this issue which may be helpful to you:http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/5c57848b-d61e-4920-abad-4ba9eb1ceac5/.

TechNet Subscriber Support

If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

---

Iric Wen
TechNet Community Support

Answer 2

Very nice Iric, it turned out to be a COM issue where MSDAINITIALIZE only had permissions set for local Administrators.

This did the trick:

http://blogs.msdn.com/b/dataaccesstechnologies/archive/2011/09/28/troubleshooting-cannot-create-an-instance-of-ole-db-provider.aspx