Cannot create an instance of OLE DB provider error as Windows Authentication user

Question

I am trying to run openrowset from MS SQL Server on an Oracle server   When i execute the following command   select   from OPENROWSET  OraOLEDB Oracle   srv   user   pass     select   from table     the following error occurs  Msg 7302  Level 16  State 1  Line 1 Cannot create an instance of OLE DB provider  OraOLEDB Oracle  for linked server   null      Can anyone tell me how I can use openrowset with OraOLEDB Oracle   I am using 64 bit version of MS SQL Server and Oracle OLEDB driver   Edit  I have tried this on two machines running Windows 7 x64  amp  Windows Server 2008 x64 with MS SQL Server 2008 x64  Both showed the same error message

User · Answer

In SQL Server Enterprise Manager  open  Server Objects Linked Servers Providers  right click on the OraOLEDB Oracle provider  select properties and check the  Allow inprocess  option  Recreate your linked server and test again   You can also execute the following query if you don t have access to SQL Server Management Studio    EXEC master dbo sp MSset oledb prop N OraOLEDB Oracle   N AllowInProcess   1

User · Answer

Ran into this issue where the linked server would work for users who were local admins on the server  but not for anyone else  After many hours of messing around  I managed to fix the problem using the following steps    Run  CTRL   R     dcomcnfg     Navigate to    Component Services -  Computers -  My Computer -  DCOM Config     Open the properties page of    MSDAINITIALIZE     Copy the    Application ID    on the properties page  Close out of    dcomcnfg     Run    regedit     Navigate to    HKEY CLASSES ROOT AppID         with the     representing the application ID you copied in step  3  Right click the             folder and select    Permissions    Add the local administrators group to the permissions  grant them full control  Close out of    regedit     Reboot the server  Run    dcomconfig     Navigate to    Component Services -  Computers -  My Computer -  DCOM Config     Open the properties page of    MSDAINITIALIZE     On the    Security    tab  select    Customize    under    Launch and Activation Permissions     then click the    Edit    button  Add    Authenticated Users    and grant them all 4 launch and activation permissions  Close out of    dcomcnfg     Find the Oracle install root directory     E  Oracle    in my case  Edit the security properties of the Oracle root directory  Add    Authenticated Users    and grant them    Read  amp  Execute        List folder contents    and    Read    permissions  Apply the new permissions  Click the    Advanced Permissions    button  then click    Change Permissions     Select    Replace all child object permissions with inheritable permissions from this object     Apply the new permissions  Find the    OraOLEDB Oracle    provider in SQL Server  Make sure the    Allow Inprocess    parameter is checked  Reboot the server

User · Answer

For error 7302 in particular  I discovered  in my registry  when looking for OraOLEDB Oracle that the InprocServer32 location was wrong   If that s the case  or you can t find that string in the registry  then you ll have to install or re-register the component    I had to delete the key from the GUID level  and then find the ProgID  OraOLEDB Oracle  key  and delete that too   The ProgID links to the CLSID as a pair    Then I re-registered OraOLEDB Oracle by calling regsvr32 exe on ORAOLEDB  dll   Just re-registering alone didn t solve the problem  I had to delete the registry keys to make it point to the correct location  Alternatively  hack the InprocServer32 location   Now I have error 7308  about single threaded apartments  rolling on

User · Answer

Similar situation for following configuration    Windows Server 2012 R2 Standard MS SQL server 2008  tested also SQL 2012  Oracle 10g client  OracleDB v8 1 7  MSDAORA provider Error ID  7302   My solution    Install 32bit MS SQL Server  64bit MSDAORA doesn t exist  Install 32bit Oracle 10g 10 2 0 5 patch  set W7 compatibility on setup exe  Restart SQL services Check Allow in process in MSDAORA provider Test linked oracle server connection

User · Answer

When connecting to SQL Server with Windows Authentication  as opposed to a local SQL Server account   attempting to use a linked server may result in the error message   Cannot create an instance of OLE DB provider   OLEDB provider name        The most direct answer to this problem is provided by Microsoft KB 2647989  because  Security settings for the MSDAINITIALIZE DCOM class are incorrect      The solution is to fix the security settings for MSDAINITIALIZE   In Windows Vista and later  the class is owned by TrustedInstaller  so the ownership of MSDAINITIALIZE must be changed before the security can be adjusted   The KB above has detailed instructions for doing so   This MSDN blog post describes the reason      MSDAINITIALIZE is a COM class that is provided by OLE DB  This class can parse OLE DB connection strings and load initialize the provider based on property values in the connection string  MSDAINITILIAZE is initiated by users connected to SQL Server   If Windows Authentication is used to connect to SQL Server  then the provider is initialized under the logged in user account  If the logged in user is a SQL login  then provider is initialized under SQL Server service account  Based on the type of login used  permissions on MSDAINITIALIZE have to be provided accordingly    The issue dates back at least to SQL Server 2000  KB 280106 from Microsoft describes the error  see  Message 3   and has the suggested fix of setting the In Process flag for the OLEDB provider   While setting In Process can solve the immediate problem  it may not be what you want   According to Microsoft       Instantiating the provider outside the SQL Server process protects the SQL Server process    from errors in the provider  When the provider is instantiated outside the SQL Server process     updates or inserts referencing long columns  text  ntext  or image  are not allowed    -- Linked Server Properties doc for SQL Server 2008 R2    The better answer is to go with the Microsoft guidance and adjust the MSDAINITIALIZE security

User · Answer

Received this same error on SQL Server 2017 trying to link to Oracle 12c  We were able to use Oracle s SQL Developer to connect to the source database  but the linked server kept throwing the 7302 error   In the end  we stopped all SQL Services  then re-installed the ODAC components  Started the SQL Services back up and voila

User · Answer

Aside from other great responses  I just had to give NTFS permissions to the Oracle installation folder   I gave read access

[sql-server] "Cannot create an instance of OLE DB provider" error as Windows Authentication user

Examples related to sql-server

Examples related to oracle

Examples related to oledb

Examples related to windows-authentication

Examples related to linked-server