SSMS fails connecting to Oracle via Linked Server but UDL works

Question

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?

• Bob Klimes: check if Allow inprocess is enabled on the Oracle Provider for OLE DB. in ssms, linked servers->providers->Oracle Provider for OLE DB->properties
• Jamo: Thanks for the response. I have updated my question, and yeah, I already did it, any other idea would be really appreciated.

Answer 1

I finally made this work.

After trying different alternatives I found one (I didn't think about that before). This is only about to stop all the SQL Services, install the provider and then restart all the SQL services and that's all.

Here the link

https://stackoverflow.com/a/55422848/1762393