0

I have a few dozen linked servers, MSSQL, Teradata and Oracle. These are on SQL Server 2019. Oracle servers are using OraOLEDB.Oracle 19c client. They are all created with the same parametrized script.

Error Message:

Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server "XXXXXXX".

Edit: Works with the login of the Windows Administrator but not any other SA login.

Error number is 7302 but is not a DLL registration issue

The script was used to create the linked servers on SQL Server 1 and SQL Server 2, on #1 all works great. On #2, MSSQL and Teradata servers are good but Oracle servers only work with the account used to install Oracle. I have tried granting all rights to Everyone on Oracle base and Oracle Home for testing, but that didn't help.

All linked servers are set up to use remote user and remote password for all logins. Below is the part of the script that executes sp_addlinkedsrvlogin.

SET @SQL = N'EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=@ServerName,@useself=N''False'',@locallogin=NULL,@rmtuser=@UserName,@rmtpassword=@Password'

EXEC $(TargetServer).master.dbo.sp_executesql 
    @stmt=@SQL
    ,@params = N'@ServerName nvarchar(250), @UserName nvarchar(250), @Password nvarchar(250)'
    ,@ServerName=@ServerName
    ,@UserName=@UserName
    ,@Password=@Password
Improve this question
8
  • Is there any error message?
    – Ronaldo
    Mar 2, 2022 at 15:33
  • @Ronaldo, the error message is Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server
    – Metaphor
    Mar 2, 2022 at 15:45
  • What about the oracle log? do you see the attempted login failed on the oracle server? Could it be that you have any special character on the password that is causing problem?
    – Ronaldo
    Mar 2, 2022 at 15:47
  • 1
    @Ronaldo, the error is entirely on the client end. The same user id/password are used on server #1. I don't have access to the Oracle server logs, but the message suggests that the SQL Server cannot create an instance of the provider, so it isn't even attempting to access the Oracle server.
    – Metaphor
    Mar 2, 2022 at 15:58
  • This is a bit outside of my lane, but when I googled ' Cannot create an instance of OLE DB provider "OraOLEDB.Oracle"', I got lot's of promising looking hits, including some other threads on SE. I'd say Google is your friend. Use your friend.
    – EdStevens
    Mar 2, 2022 at 16:13

1 Answer 1

Reset to default
0

The solution turned out to be the AllowInProcess flag on the OraOLEDB.Oracle provider.

This solved the problem:

EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.