MS SQLSERVER to ORACLE connectivity with Linked Server configuration:

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:

$ORACLE_HOME/network/admin/tnsnames.ora

Here is a sample TNS file. You need to copy this from the oracle server where you want to connect.

oraLab01 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = oralab01)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = lab.com)

    )

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.

select * from [oralab01]..scott.tiger;