Cannot create an instance of OLE DB provider ‘OraOLEDB.Oracle’ for linked server

Last week, someone from the other team contacted me to help him out regarding an issue he was facing while creating a Linked Server to an Oracle database. He was trying to create a Linked Server to a Oracle Database from a SQL Server 2005 instance. He had the Oracle Client components installed on the server hosting the SQL Server instance. After giving all the details in the Linked Server properties when clicking on Ok, he would receive the following error message.

Since the linked server is Oracle, the first thing that I tried was to check if the Server hosting the SQL Server instance, was able to connect to the listener on the Oracle Database Server using tnsping.

tnsping successfully connected to the Oracle database from the SQL Server box. Then I moved on to check the Provider details while creating the Linked Server.

The Linked Server provider was Oracle Provider for OLE DB. This is the driver that gets installed along with the Oracle Client Components. This KB article states that Error 7302 can be a result of MSDAORA.dll not being registered correctly. In this case it had to be an issue with the dll related to Oracle Provider for OLE DB i.e.OraOleDB.dll. Before checking the dll related issues, I wanted to check if something was missing in the registry. The list of Providers in the Linked Server is populated from the Providers key in the registry. This key is located under the instance name under the below key.

The entries in the registry looked like this.

I could not find anything unusual here. Now it was confirmed that something must be wrong with the OraOleDB.dll. This dll is located under the bin directory of the folder where Oracle Client Components are installed (usually called oracle_home). When I navigated to that folder, I could not find any such files. To confirm this, I checked that directory on a *working* instance, the OraOleDB.dll was listed there.

Now my job was easy. On the server which was having issues with the Linked Server, the Oracle Client Components were either not installed correctly or it was corrupt. I copied the dll from the *working* server and pasted it under <oracle_home>\bin directory. Then I registered the OraOLEDB10.dll using regsvr32.exe.

Issue was not yet resolved. While creating the Linked Server, the same error message was displayed but the error number was different, 7303.

Error 7303 means that the username and password combination is not correct, hence the login failed for the user specified in the Linked Server. When the correct login credentials for the Oracle database were entered, the Linked Server got created successfully!

10 thoughts on “Cannot create an instance of OLE DB provider ‘OraOLEDB.Oracle’ for linked server

  1. Wil

    I’ve had more issues with Oracle drivers in the past and have had pretty much this same issue. It took me hours to find the solution-I wish this article had shown up then!

    Reply
  2. Martin Rendell

    I had the same issue. Spent hours trawling the web trying differing walkthroughs. Followed your example and my isseu was resolved in minutes. Thank you.

    Reply
  3. Neel

    I continue to have this exact same issue – though things might be a bit more complicated. I’m running 64bit Windows 2008R2 and SQL 2008 R2. I checked for the presence of the dll, and it’s sitting there all right. The Oracle Client I installed is specifically 64 bit.

    Any thoughts?

    Cheers,
    Neel

    Reply
  4. Jshop

    Pradeep,
    Your this article is extremely helpful but I have error when I try to create linked server for Oracle DB from SQL Server 2008 x64 R2.
    I have following env.

    Server OS : Windows Server 2008 R2
    SSMS: Microsoft Sql Server 2008 R2 – 10.50.1600.1 (x64)
    Oracle Client: 11g R2 x32
    Oracle ODAC: x64
    SSMS Provider(OraOLEDB.Oracle) “Allow Inprocess” Propery: Enabled

    I am unable to create the linked server to Oracle Db. The same Oracle Db I can connect via SQL Plus or by creating ODBC system DSN.

    I get following error when I try to create linked server in SSMS “Cannot create an instance of OLE DB provider “OraOLEDB.Oracle” for linked server “TRN_ORACLE_LINKED_SERVER”. (Microsoft SQL Server, Error: 7302)”

    Inside New Linked Server Form I have following things..
    General TAB:

    Provider: Oracle Provider for OLE DB

    Product name: Oracle

    Data Source: (SID or database name from tnsnames.ora file)

    Security TAB:

    Default selection is “Be made without using a security context” I tried this and then changed to “Be made using this security context(here I supply same userid and pwd which I use in SQL* Plus to connect to same oracle database)

    In the property of “OraOLEDB.Oracle” I enabled “Allow Inprocess”

    Restarted box which has SQL instance/database…

    What else am I missing ? Any help is appreciated.

    Thanks.

    Reply
  5. Pat the DBA

    Hi,

    I have the same issue (error 7302). I have a question for PradeepAdiga: have you tried to use Oracle OLEDB provider with a SQL Server service running with an account that is NOT part of the local Administrators group?

    Thanks.

    Reply
    1. PradeepAdiga Post author

      Hi Pat,
      No. I have not tried to use Oracle OLDEDB provider with SQL Server running with an account that is NOT part of the local Administrators group

      Reply
  6. Dennis

    I too am receiving the 7303 error, but my problem is that I am using an Oracle Wallet to supply login credentials. I have no problems connecting to the Oracle DB using SQL Plus. How do I tell SQL Server to use the wallet? I have tried with both SQL 2005 and 2008.

    Reply
  7. Nigel

    Thanks for the screen shot of the properties for the Oracle Provider under Linked Servers in SQL Server showing that the ‘allow inprocess’ box needs to be ticked. That has solved the issues that I was having and saved me loads of time. Many thanks.

    Reply

Leave a Reply