Can't create a linked server connection on SQL Server 2014 to Oracle 11g

Question

I am trying to create a linked server in SQL Server 2014. I am attempting to connect to an Oracle 11g server. I am using the Oracle in OraClient 11g connection provided by Oracle. The ODBC connection is set up on the Server and I can test connect, so it works though Windows.

I right click on the Linked Servers in the database and choose Other Data source. There is no Oracle provider option there. If I try and force it using the Microsoft OLE DB Provider for ODBC drivers, the connection returns an error message that says "An exception occurred while executing a t-sql statement. Cannot initialize the data source object of OLE DB provider "MSDASQL" for Linked server "Server". OLE DB provider "MSDASQL" for linked server "Server" returned message "[Micrsoft][ODBC Driver Manager] Data Source name not found and no default driver specified." (Microsoft SQL Server, Error: 7303)

It appears I am missing something, a way for the sql server to recognize the connection, but I can't figure out what. Any guidance would be appreciated.

Answer 1

According to SQL Mag:

Before you can create a SQL Server 2008 linked server to Oracle, you need to install the Oracle client software on the SQL Server 2008 system. The Oracle client software provides the network libraries that are required to establish connectivity to the Oracle database. In this case, the Oracle 11g database server is already installed and functional. Installing the Oracle client software on the SQL Server system enables it to connect to the existing Oracle server.

The article applies to 2008/11g but the principle should be the same on 2014/11g.

Answer 2

Took me a couple of days u I found this:

https://nampark.wordpress.com/2011/01/20/installing-64-bit-odac-11-2-to-microsoft-sql-server-2008-r2-x64-for-replication/

It gives a great walk through of how to install the missing components I need. The downside to this solution is that it requires the installation to be done via cmd window. Not something I have much experience with, but this walk through really gave me the details I needed to get it done. That cleared the error message I was getting.