none
SQL 2016 - Oracle OLEDB (ODTwithODAC122011) - Cannot Create Instance - Access Denied RRS feed

  • Question

  • Hello All,

    Any assistance is appreciated and thank you!

    Environment:

       - Host:  Windows 2016 Std.

        - SQL Server: 2016 Ent - 13.0.5479.0

    I'm engaged in a Linked Server\ Oracle ODAC battle!

    Test connect via UDL - Successful

    Test connect  OLEDB linked server - Successful

    SSMS - Execute Query

    SELECT

    *FROMOPENQUERY (EXADATA,' SELECT SYSDATE FROM DUAL')

    The following errors, are raised depending on OLEDB.Oracle provider properties.

    Msg 7399, Level 16, State 1, Line 1

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

    Msg 7350, Level 16, State 2, Line 1

    Cannot get the column information from OLE DB provider "OraOLEDB.Oracle" for linked server "EXADATA".

    OR

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


    Covi







    • Edited by MarkCovi Monday, October 7, 2019 5:10 PM
    Monday, October 7, 2019 4:51 PM

Answers

  • Update,

    • Removed SQL Server\Agent service accounts from 'LocalHost Admin Group'
    • Added SQL Server\Agent service accounts to Oracle Install Parent Folder with FULL Access

    ProcMon identified the access failures.

    Thank You for assistance offered.

    MarkCovi


    Mark Covian

    • Marked as answer by MarkCovi Thursday, November 7, 2019 3:15 PM
    Thursday, November 7, 2019 3:15 PM

All replies

  • Is the provider set to "Alloq in-process"?

    Have you set up any login-mapping?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, October 7, 2019 9:04 PM
  • Hello Erland, thanks for replying

    I have not setup Login-Mapping



    • Edited by MarkCovi Monday, October 7, 2019 10:07 PM
    Monday, October 7, 2019 10:07 PM
  • Hi MarkCovi,

    Please refer to How to get up and running with Oracle and Linked Servers.

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 8, 2019 3:15 AM
  • I have not setup Login-Mapping

    This may be required for something that is not SQL Server, but I have never worked with Oracale, so I can be wrong. Hopefully the blog that Rachel was kind to link to can set you straight.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Proposed as answer by Ashin_c Thursday, October 10, 2019 8:18 AM
    Tuesday, October 8, 2019 9:00 PM
  • UDL -



    • Edited by MarkCovi Tuesday, October 8, 2019 9:44 PM
    Tuesday, October 8, 2019 9:37 PM
  • Out Of process


    Covi


    • Edited by MarkCovi Tuesday, October 8, 2019 9:45 PM
    Tuesday, October 8, 2019 9:45 PM

  • CoviCo DBA


    • Edited by MarkCovi Tuesday, October 8, 2019 10:18 PM
    Tuesday, October 8, 2019 10:18 PM
  • Errrrrrrr…. SQL Server Agent test job fails


    CoviCo the amazing...


    • Edited by MarkCovi Tuesday, October 8, 2019 10:34 PM
    Tuesday, October 8, 2019 10:24 PM
  • Hi ,

    If it still shows the error , I think one possibility  is that the issue may be related to OraOLEDB.  If you have some questions about OraOLEDB, please post your issue on the corresponding forum.

     Oracle Communities

    If  you have post your issue in the corresponding forum, in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    If you have any question about SQL Server Data Access of Microsoft SQL Server , please let me know.

    Best Regards,

    Rachel 

     


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Ashin_c Thursday, October 10, 2019 8:19 AM
    Wednesday, October 9, 2019 6:08 AM
  • Please check it in Oracle Forum.
    Thursday, October 10, 2019 8:19 AM
  • Thanks for the Direction,  I checked Oracle, found a very similar

    Msg 7302, Level 16, State 1, Line 1 Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server "********".

     There no answers\discussion 1200 Views.

    This issue still remains... 

    The Problem now:  SSA Job | TSQL step executing:

    SELECT *FROM OPENQUERY (EXADATA,' SELECT SYSDATE FROM DUAL')


    Covi


    • Edited by MarkCovi Thursday, October 10, 2019 4:22 PM
    Thursday, October 10, 2019 4:13 PM
  • Added SSDE and SSA service accounts to local admin group and all errors resolved.  ETL jobs ran to success!

    In-Process on\off successful TSQL Executions. 

    SELECT *FROM OPENQUERY (EXADATA,' SELECT SYSDATE FROM DUAL')

    Should SSDE\SSA service accounts reside in Local Admin Group for SQL Server 2016?

    Thanks.


    Mark Covian

    Thursday, October 10, 2019 9:02 PM
  • I would guess that adding them as Administrators in Windows is unnecessarily heavy ammunition, although I can't say exactly what permission that is needed. You said that it worked from SSMS, but was that when you was logged in locally, or from your own workstation? And what are your permissions?

    I would play with the Allow in-process setting, as I guess the issue is to start the out-of-process thing.

    Please note that everything in this post are speculations and not based on actual knowledge.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, October 10, 2019 9:32 PM
  • Update,

    • Removed SQL Server\Agent service accounts from 'LocalHost Admin Group'
    • Added SQL Server\Agent service accounts to Oracle Install Parent Folder with FULL Access

    ProcMon identified the access failures.

    Thank You for assistance offered.

    MarkCovi


    Mark Covian

    • Marked as answer by MarkCovi Thursday, November 7, 2019 3:15 PM
    Thursday, November 7, 2019 3:15 PM