1. Install Oracle Client where MSSQLSERVER is running. I've used 11.2.0 client for my case.
2. Add oracle TNS Entries for the target Oracle Database. TNS location would be:
Here is a sample TNS file. You need to copy this from the oracle server where you want to connect.
3. Now start configuring mssql server LINKED SERVER.
Server Objects --> Linked Servers -->Right click on Linked Servers --> New Linked Server
Here you need to mention the link name, provider name etc. As I’m connecting to Non MSSQL so I had to select "Other data Source" as server type.
4. Select Oracle Provider for OLE DB from Provider drop down.
Type data source & provider string similar to the TNS file.
5. Click Security from left side. And select "Be made using security context" then Type username & password.
6. Click Provider --> OraOLEDB.Oracle --> Right click-->Properties --> check "Allow inprocess"-->OK
7. Right click on newly created connection ---> Test Connection ---> Success!!!
Now you can either browse the tables from Linked Server connection or you may run following to select your tables.