I'm using SQL Server 2017 and am trying to set up a linked server to an Oracle database. I installed [ODAC](https://www.oracle.com/technetwork/database/windows/downloads/index-090165.html) and updated my PATH to include the Oracle root from ODAC installation.

Everything looks good and I am able to choose the Oracle Provider for OLE DB when setting up the server, but when I save my configuration, I get the following error:

> 
> ===================================
> 
> 
> The linked server has been created but failed a connection test. Do you want to keep the linked server?
> 
> 
> ===================================
> 
> 
> An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
> 
> 
> 
> 
> ---
> 
> 
> Program Location:
> 
> 
> at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
>  at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String cmd, Boolean retry)
>  at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection()
>  at Microsoft.SqlServer.Management.SqlManagerUI.LinkedServerProperties.DoPreProcessExecution(RunType runType, ExecutionMode& executionResult)
> 
> 
> ===================================
> 
> 
> Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server "UDW". (.Net SqlClient Data Provider)
> 
> 
> 
> 
> ---
> 
> 
> For help, click: <http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=13.00.5081&EvtSrc=MSSQLServer&EvtID=7302&LinkId=20476>
> 
> 
> 
> 
> ---
> 
> 
> Server Name: localhost
>  Error Number: 7302
>  Severity: 16
>  State: 1
>  Procedure: sp\_testlinkedserver
>  Line Number: 1
> 
> 
> 
> 
> ---
> 
> 
> Program Location:
> 
> 
> at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
>  at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
> 
> 
> 

This error message is not exactly helpful, and if you follow the Microsoft link, it redirects you to the Microsoft homepage (thanks Microsoft!)

Is there a way to test if this is a problem of not finding TNSNAMES vs some sort of firewall issue on the Oracle server?

I am able to set up a LinkedServer on another database in the same network so I can confirm that the TNSNAMES is exactly the same and the credentials I am using are valid.