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)
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"
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.