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.
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.
This question is related to
sql-server
oracle
oledb
windows-authentication
linked-server
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!
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
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!
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:
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.
Similar situation for following configuration:
My solution:
Aside from other great responses, I just had to give NTFS permissions to the Oracle installation folder. (I gave read access)
Source: Stackoverflow.com