title: SQL Server Linked with Oracle Database
body: <p>I'm using SQL Server 2017 and am trying to set up a linked server to an Oracle database.  I installed <a href="https://www.oracle.com/technetwork/database/windows/downloads/index-090165.html" rel="nofollow noreferrer">ODAC</a> and updated my PATH to include the Oracle root from ODAC installation.</p>
  
  <p>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:</p>
  
  <blockquote>
    <p>===================================</p>
    
    <p>The linked server has been created but failed a connection test. Do you want to keep the linked server?</p>
    
    <p>===================================</p>
    
    <p>An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)</p>
    
    <hr>
    
    <p>Program Location:</p>
    
    <p>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&amp; executionResult)</p>
    
    <p>===================================</p>
    
    <p>Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server "UDW". (.Net SqlClient Data Provider)</p>
    
    <hr>
    
    <p>For help, click: <a href="http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&amp;ProdVer=13.00.5081&amp;EvtSrc=MSSQLServer&amp;EvtID=7302&amp;LinkId=20476" rel="nofollow noreferrer">http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&amp;ProdVer=13.00.5081&amp;EvtSrc=MSSQLServer&amp;EvtID=7302&amp;LinkId=20476</a></p>
    
    <hr>
    
    <p>Server Name: localhost
    Error Number: 7302
    Severity: 16
    State: 1
    Procedure: sp_testlinkedserver
    Line Number: 1</p>
    
    <hr>
    
    <p>Program Location:</p>
    
    <p>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)</p>
  </blockquote>
  
  <p>This error message is not exactly helpful, and if you follow the Microsoft link, it redirects you to the Microsoft homepage (thanks Microsoft!)</p>
  
  <p>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?</p>
  
  <p>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.</p>