OraOLEDB Linked Servers work with one SA login but not another

Copper Contributor

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 
		,@params = N'@ServerName nvarchar(250), @UserName nvarchar(250), @Password nvarchar(250)'


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:
linked server ora success.png


Test Connection by Another SA

linked server ora error.png

1 Reply

The solution was to turn on the AllowInProcess flag for the OraOLEDB.Oracle provider.


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