I am running SQL Server 2019 with a few dozen MSSQL, Teradata and Oracle linked servers on several Windows Server 2016 machines (different environments - dev, test, etc.). All linked servers are set up to use remote user id and password for all logins.
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
On one of these servers will only the login that installed the Oracle Client software can connect to the Oracle servers. A different login, also SA on the server, gets the "Cannot create an instance of OLE DB provider for linked server" (7302) error on sp_testlinkedserver.
Test Connection by Windows admin login:
Test Connection by Another SA
