"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.

Mckibben

I am trying to run OpenQuery against my linked server and getting same error. My linked server is using OraOLEDB.Oracle provider and I have already enabled the allow In Process option. The funny part is I have been using this linked server without any issue since last week, I did sql server reboot and it suddenly started to give error. – Vitiated

Answer 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

Deadhead

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

Did you recreate your linked server? – Deadhead

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

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

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

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

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

Answer 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.

Gerianne

it's dcomcnfg - drop the i – Inesita

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

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. – Helbon

I was able to resolve a DB2COPY1 provider issue the same way. The Bin location was C:\Program Files\IBM\SQLLIB\BIN. No server reboots were needed in this case. – Heckle

Answer 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.

Heppman

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

Answer 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!

Inesita

Thank you for the clue! I find in the registry "HKEY_CLASSES_ROOT\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}\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. – Indehiscent

Answer 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!

Zymolysis

Answer 6

Just enable option "Allow in process" on the properties of the OraOLEDB.oracle provider as below

• [open server objects > Linked Severs > providers] 1 [right click on
• OraOLEDB.oracle > properties ] 2 then choose optaion "Allow in
• process" and click ok

Aboard

Answer 7

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

Lantern

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

Answer 8

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

Orvas

Answer 9

I want to left here something, that apparently I forgot for second time and took me few hours to figure it out again. After the installation of OraOLEDB, configure.bat, regsvr32 and etc working and the SQL provider does not work. Add c:\oracle and c:\oracle\bin to the system path!!!

Roark