Linked server to Oracle DB not working after installing OLEdb provider ( ODAC)

• johnnyrmtl

  Hall of Fame

  Points: 3505

  More actions

  June 29, 2012 at 8:43 am

  #263094

  Hello all,

  Iève isntalled the oracle client along with data access components which isntalled the provider required to create a linked server to the Oracle DB. After configuring it it gives me this very vague message at test time.

  ------------------------------

  Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server "xxxxx". (Microsoft SQL Server, Error: 7302)

  For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4035&EvtSrc=MSSQLServer&EvtID=7302&LinkId=20476

  I've configured correctly with the connection strings and valid user but not sure what is missing.

  The same thing was done in a DEV env and it works towards the same Oracle DB

  Thanks

• johnnyrmtl

  Hall of Fame

  Points: 3505

  More actions

  June 29, 2012 at 11:45 am

  #1507241

  UPDATE: I've clicked on the provider dynamic parameter and now get this error when querying the Oracle DB.

  Msg 7399, Level 16, State 1, Line 1

  The OLE DB provider "OraOLEDB.Oracle" for linked server "xxxxxx" reported an error. Access denied.

  Msg 7301, Level 16, State 2, Line 1

  Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "OraOLEDB.Oracle" for linked server "xxxxxx".

  Thanks

• Jo Pattyn

  SSC-Dedicated

  Points: 32439

  More actions

  June 30, 2012 at 6:24 am

  #1507445

  Did you also check "In Process" option? (should be enabled)

• johnnyrmtl

  Hall of Fame

  Points: 3505

  More actions

  June 30, 2012 at 8:13 am

  #1507449

  Thanks for response... I found the fix and it was by logging in with a SQL login and NOT windows login. Not sure why but everything worked including updates towards oracle.

  I guess its a security setting but I don't want to mess up anything with a prod server though.

  J