I need to connect to a remote server to consume some tables in an Oracle db so I installed the Oracle provider, V1021277-01, ODAC 21c.
The way I am connecting is by creating a linked server
Provider: Oracle Provider for OLE DB
Product Name: Oracle
Data Source: [ip-adress:port]/[instance-name]
Then I get the following error:
The linked server has been updated but failed a connection test. Cannot create an instance of OLE DB provider "ORAOLEDB.Oracle" for linked server "ABC". (Microsoft SQL Server, Error: 7302)
However, when I create an UDL file the test connection successes and I completely sure the connection works since I tested with TELNET
Here my source: Windows Servers 2016, SSMS 2019.
Edit (8/17/2022) I enabled the below provider options but no luck.
exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'AllowInProcess', 1
exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'DynamicParameters', 1
Any idea on why the connection fails on SSMS?