"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 & Windows Server 2008 x64 with MS SQL Server 2008 x64. Both showed the same error message.

Solution 1

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

Solution 2

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:

1. Run (CTRL + R) “dcomcnfg”. Navigate to “Component Services -> Computers -> My Computer -> DCOM Config”.
2. Open the properties page of “MSDAINITIALIZE”.
3. Copy the “Application ID” on the properties page.
4. Close out of “dcomcnfg”.
5. Run “regedit”. Navigate to “HKEY_CLASSES_ROOT\AppID{???}” with the ??? representing the application ID you copied in step #3.
6. Right click the “{???}” folder and select “Permissions”
7. Add the local administrators group to the permissions, grant them full control.
8. Close out of “regedit”.
9. Reboot the server.
10. Run “dcomconfig”. Navigate to “Component Services -> Computers -> My Computer -> DCOM Config”.
11. Open the properties page of “MSDAINITIALIZE”.
12. On the “Security” tab, select “Customize” under “Launch and Activation Permissions”, then click the “Edit” button.
13. Add “Authenticated Users” and grant them all 4 launch and activation permissions.
14. Close out of “dcomcnfg”.
15. Find the Oracle install root directory. “E:\Oracle” in my case.
16. Edit the security properties of the Oracle root directory. Add “Authenticated Users” and grant them “Read & Execute”, “List folder contents” and “Read” permissions. Apply the new permissions.
17. 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.
18. Find the “OraOLEDB.Oracle” provider in SQL Server. Make sure the “Allow Inprocess” parameter is checked.
19. Reboot the server.

Solution 3

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.

Solution 4

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!

Solution 5

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!

View more solutions

Comments

[th1rdey3]

Brabank, I already tried that, but it still shows the same error.

[David Brabant]

Did you recreate your linked server?

[th1rdey3]

yes, I have even restarted the sql service before recreating the linked server. but still no luck.

[David Brabant]

You might find additional tracks here: support.microsoft.com/kb/280106/en-us

[binu]

Thanks for this answer, it saved my day.

[Registered User]

I am so glad this was listed here. Thanks!

[scradam]

In my experience, it is necessary to restart the MSSQL Service in order for the "allow inprocess" option to take effect.

[Tequila]

The Oracle client has to be installed correctly on the SQL Server for this to work.

[GilesDMiddleton]

it's dcomcnfg - drop the i

[GilesDMiddleton]

In step 7 I am unable to add Administrators to full control permissions - Access is denied.

[Gary James]

Giles - This a little late, but within Permissions, select Advanced, then the Owner tab. Select the Replace owner... checkbook to change it to the administrator.

[jaraics]

It's a SQL Authentication setup, though. But this is the first Google result for that error message, so hopefully will help someone.

[Hybris95]

No need to restart MSSQL Service for me. It worked right away, not even re-create the linked server.

[jinhr]

Thank you for the clue! I find in the registry "HKEY_CLASSES_ROOT\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA300‌​80}\InprocServer32" the default key has value "D:\\app\\oracle\\product\\11.2.0\\client_1\\bin\\OraOLEDB11‌​.DLL". Then I realized that "D:\app\oracle" folder is deleted by someone! I managed to copy the whole folder from another server with same version. And it solves the problem.

[Baodad]

This answer sounds so plausible, I really wanted it to work, but it didn't for me, and I had to "Allow inprocess" anyway.