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