A developer reach out to me about an error they were getting when attempting to run a stored procedure that used a linked server to pull data from an Oracle database using an Active Directory login. This was happen on the production database server, however this stored procedure worked on the development database server.
Msg 7302, Level 16, State 1, Procedure …, Line … [Batch Start Line ..]Cannot create an instance of OLE DB provider “OraOLEDB.Oracle” for linked server “…”.
Part of the problem when working with linked servers is that SQL Server stores some of its information in the Window Registry, and can not easily be verified.
The provider for the Oracle OLE DB needs to have the “AllowInProcess” enabled, which again does this with as a DWORD Value in the registry.
Thankfully, I don’t have to use the registry editor to find the value. There is a extended stored procedure that lets me view this.
On my SQL Server 2016 development database server I ran the following:
EXECUTE master.sys.xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\Providers\OraOLEDB.Oracle\', 'AllowInProcess';
Notice that the registry key is version dependent on the version of SQL Server running. I am able to see that with the value of “1”, that provider for the Oracle OLE DB “AllowInProcess” is enabled.
However when I ran the same SQL on my production database server, I see that provider for the Oracle OLE DB “AllowInProcess” was not found.
To enable the “AllowInProcess” for the provider for the Oracle OLE DB, I ran the “sp_MSset_oledb_prop” stored procedure:
EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1; GO
The developer tested & now will be able to use all its stored procures with that OLE provider.
Please note not to just cut & past with the error, make sure that OLE DB provider in the error message is the same in the SQL that is run to enable the “AllowInProcess”. My first attempt was a lazy cut & paste from Google, and I enabled the “AllowInProcess” for “Microsoft.ACE.OLEDB.12.0” provider.