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.
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.
OLE DB error: OLE DB or ODBC error: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections.
The recommended way to create an OLE DB provider is to use the wizards to create an ATL COM project and a provider and then modify the files using the OLE DB templates. As you customize your provider, you can comment out unwanted properties and add optional interfaces.
In the Object Explorer, right-click the server and click Properties. On the Security page under Server authentication, select SQL Server and Windows Authentication mode and then click OK.
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:
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With