OraOLEDB Linked Servers work with one SA login but not another

%3CLINGO-SUB%20id%3D%22lingo-sub-3242860%22%20slang%3D%22en-US%22%3EOraOLEDB%20Linked%20Servers%20work%20with%20one%20SA%20login%20but%20not%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3242860%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20running%20SQL%20Server%202019%20with%20a%20few%20dozen%20MSSQL%2C%20Teradata%20and%20Oracle%20linked%20servers%20on%20several%20Windows%20Server%202016%20machines%20(different%20environments%20-%20dev%2C%20test%2C%20etc.).%20All%20linked%20servers%20are%20set%20up%20to%20use%20remote%20user%20id%20and%20password%20for%20all%20logins.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%20SET%20%40SQL%20%3D%20N'EXEC%20master.dbo.sp_addlinkedsrvlogin%20%40rmtsrvname%3D%40ServerName%2C%40useself%3DN''False''%2C%40locallogin%3DNULL%2C%40rmtuser%3D%40UserName%2C%40rmtpassword%3D%40Password'%0A%0A%20EXEC%20%24(TargetServer).master.dbo.sp_executesql%20%0A%20%20%40stmt%3D%40SQL%0A%20%20%2C%40params%20%3D%20N'%40ServerName%20nvarchar(250)%2C%20%40UserName%20nvarchar(250)%2C%20%40Password%20nvarchar(250)'%0A%20%20%2C%40ServerName%3D%40ServerName%0A%20%20%2C%40UserName%3D%40UserName%0A%20%20%2C%40Password%3D%40Password%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20one%20of%20these%20servers%20will%20only%20the%20login%20that%20installed%20the%20Oracle%20Client%20software%20can%20connect%20to%20the%20Oracle%20servers.%20A%20different%20login%2C%20also%20SA%20on%20the%20server%2C%20gets%20the%20%22Cannot%20create%20an%20instance%20of%20OLE%20DB%20provider%20for%20linked%20server%22%20(7302)%20error%20on%20sp_testlinkedserver.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETest%20Connection%20by%20Windows%20admin%20login%3A%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22linked%20server%20ora%20success.png%22%20style%3D%22width%3A%20757px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F352479i7315D8EEC7FAB6E2%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22linked%20server%20ora%20success.png%22%20alt%3D%22linked%20server%20ora%20success.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETest%20Connection%20by%20Another%20SA%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22linked%20server%20ora%20error.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F352480iEDE95B57FE6C8F9C%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22linked%20server%20ora%20error.png%22%20alt%3D%22linked%20server%20ora%20error.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3245828%22%20slang%3D%22en-US%22%3ERe%3A%20OraOLEDB%20Linked%20Servers%20work%20with%20one%20SA%20login%20but%20not%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3245828%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20solution%20was%20to%20turn%20on%20the%20AllowInProcess%20flag%20for%20the%20OraOLEDB.Oracle%20provider.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EEXEC%20master.dbo.sp_MSset_oledb_prop%20N'OraOLEDB.Oracle'%2C%20N'AllowInProcess'%2C%201%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New 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 
		@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:
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