Post reply

Linked Server Issue - Oracle connection

  • September 2, 2011 at 5:28 am

    #247370

    Afternoon all,

    I have configured Oracle 11g client on our SQL servers and configured a linked server to connect to our oracle server ORACLE_SRV.

    If I test the connection within the oracle tools - the test is successful

    If I test the Linked server using the 'Test Connection' option in SSMS - the test is successful.

    If however I expand the linked server down to the default catalogue and try to expand the table list I get the following error:

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

    Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_SRV". (.Net SqlClient Data Provider)

    Does anyone know why I am getting an access denied issue?

    I have verified the connection and password are correct.

    _____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

  • September 2, 2011 at 5:46 am

    #1376374

    I wrote an article on this topic some months ago.

    There's a lot of permissions you have to grant to the SQL Server service user or interactive user in order to let the linked server work.

    You can find it here:

    http://www.sqlservercentral.com/articles/Linked+Servers/73794/

    Hope this helps

    Gianluca

    -- Gianluca Sartori

  • September 2, 2011 at 6:05 am

    #1376388

    thanks Gianluca - I'll take a look and let you know how I get on

    _____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

  • September 2, 2011 at 7:19 am

    #1376415

    Gianluca,

    Thanks for the post. FYI the only change I needed to make was to the set 'Allow Inprocess' provider option for the OraOLEDB.Oracle provider under linked servers.

    Thanks again.

    _____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

  • September 2, 2011 at 9:23 am

    #1376508

    Glad you sorted it out.

    -- Gianluca Sartori

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply