1 Reply Latest reply: Sep 14, 2011 10:00 AM by 888236 RSS

    Can't create linked server for Oracle DB from SSMS 2008 x64 R2

    881331
      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.

      Edited by: user1378136 on Aug 25, 2011 8:35 AM
        • 1. Re: Can't create linked server for Oracle DB from SSMS 2008 x64 R2
          888236
          I had this same issue only with SSMS 2005 x86.

          I unchecked the "Allow inprocess" and I was able to connect.
          You will need the security "Be made using this security context" with the U/P that you mention
          "+same userid and pwd which I use in SQL* Plus to connect to same oracle database+"

          Hopefully you will get pass that issue. The issue I have now is that I can't do a select on
          one of the linked tables.