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

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

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.

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.