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;
