Unable to connect to a Oracle Database from a Microsoft SQL Server Mgmt Studio.

The Oracle Client needs to be installed on the server, in the same bitness as MSSQL (32 or 64bit), to allow to see it as provider in SSMS.

i have SSMS installed on my workstation, and i downloaded the oracle client. i still was not able to get it to show.  do you have any links to help w/ the install process.

As Qlemo said, you need the Oracle client installed on the SERVER, not the workstation. Since SQL Server is almost certainly going to be a 64 bit process you'll need the 64 bit client.

You may also need to change the provider properties to get it to work properly. When I do it I have to set AllowInProcess to true.

The Oracle server is at a vendor, they allow for ODBC. I can connect using Oracle SQL client, and through excel / access using ODBC.

If i can't get them to install the client on their end, is that the only way to get connected via SSMS. is there no other way to get that OraOLEDB.oracle to show in SSMS. In order to get the SSMS to connect via Linked Server it sounds like i need to have this as option.

I don't think Oracle can connect through ODBC. I've never tried it and most of the Google search results I've been through walk through using the Oracle client to connect. The only examples I've found using an ODBC driver still use the Oracle client to perform the actual connection and ODBC is merely the conduit.

Correct, ODBC still requires to have the Oracle Client installed, so it doesn't change anything for you.

Note: you need the driver on the machine where SQL Server runs, not the Oracle server. I'm not sure this is clear after reading "If i can't get them to install the client on their end,".

What is the purpose of the connection? If you need to copy data there are means to manage that on the client.

Let's rewind a minute,
You have a SQL Server database and you want to connect to an Oracle database?
Yes, you need to install the Oracle client on the same machine as the SQL Server database.
I would assume you need the 64 bit version if you're only running open queries from SSMS.
If however you plan to execute SSIS (integration services) packages, you will also need the 32 bit version of the client.
It can be a real pain installing both and getting them both to function. I've done this several times recently for a recent project (ORacle 12c, SQL Server 2016, SSMS, SSIS, SSRS, SSAS.

I tend to also configure:
tnsnames.ora file (with the references to the target destination hostname, portnumber, servicename, etc.)
sqlnet.ora file (various options, e.g. to control logging, default domains, name service, etc.)
hosts file (resolves the aliases you probably used in your tnsnames.ora to the target server IP addresses)
Environment Variable ORACLE_HOME (pointing to your Oracle Home)
Environment Variable TNS_ADMIN (pointing to the location where you stored your tnsnames.ora file)
Environment Variable PATH (pre-existing)...updated to also include the ORACLE_HOME
Firewall Rules - Your target server will not likely already have ports open for your source server to connect to the target through. You might have to speak to your networks team, providing them with the source and target host names, Indicate TCP protocol, and if you don't know which port, speak to your  Oracle DBA (or go with the default of 1521 for luck!)

Once you can successfully test a connection to the Oracle database from the SQL*PLUS application (in ORACLE_HOME\Bin folder), you are ready to fire up SSMS and create a Linked Server (Server Objects > Linked Server> Create New Linked Server).

Be sure to pick the right protocol from the dropdown immediately after you first click New Linked Server. It will default to an MS OLE DB (ie to connect to another SQL Server). You want to change it to Oracle. If it is not available to select, go back into the ORacle CLinet installation....do NOT pick instant client....go for CUSTOM and install the OLE DB drivers (as well as SQL Plus, net client, and generally, for convenience, I might install the ODBC drivers whilst I'm at it, in case I need some other access mechanism for Powershell scripts or something.

Once the linked server is created, click test connection. Hopefully it will show "Connection Test Succeeded". If so, you're good to go. Open SSMS, Open a new query window and start writing some quieries using OPENQUERY.

Hope this helps!
Regards,
JT

Hi,

You can try the following steps:

(A) Download Oracle Data Access Component.
(B) Install and Restart the system.
(C) Now you need to create the Linked Server as below:
    (1) Launch SSMS
    (2) Go to Server Objects >> Linked Servers >> Providers >> Properties
    (3) Tick Allow in process.
    (4) Right click on Linked Server >> New Linked Server.
    (5) Configure the General/Security Tabs.
    (6) Once complete try to connect the Database.


Regards,
Pratik