Creating Oracle Linked Server in SQL Server

I wrote multiple articles on heterogeneous linked server which helped user to connect to Sybase , SQL Server & MySQL RDBMS platforms. However I missed creating Oracle linked server , many users responded to write an article on this so I decided to complete it. As you know when you create a heterogeneous linked server you need proper driver of the destination RDBMS. I have split this into two sections a.) Installing Oracle Driver and configuring tnsnames.ora file b.) Creating Oracle linked server in SQL Server

Install Oracle Drier and Configure tnsnames.ora file

• Install Oracle client tools in the server where you are going to create linked server. (Install correct version of driver based on the oracle destination version)
• Now you need to update the details in tnsnames.ora file. If you haven’t updated it then follow the steps below to update the name in tnsnames.ora file. In this example I’m going to add ORAServer (this alias will have the actual server details) as alias to tnsnames.ora file. Before updating it you need to know where tnsnames.ora file exists so to find out that just execute the command tnsping ORAServer in command prompt, this will give you show you the path.

• In this case it’s pointing to “D:\oracle\product\10.2.0\db_1\network\admin\”. Actually it will point sqlnet.ora file but you need to extract the path  and go to that path as shown below. Now open tnsnames.ora  file and update the details.

• I have updated ORAServer in tnsnames.ora, you can update similar like this and then save the file.

• Now check whether you are able to ping the alias, follow step1 again and check whether you are getting the response as shown below

That’s it you are done with configuring tnsnames.ora file. Now let’s create the linked server.

Creating Oracle Linked Server in SQL Server

• Connect to SQL Server in SSMS and then expand Server Objects folder, right click on Linked Servers folder and then click “New Linked Server…”
• Now update linked server details in the wizard as shown below

• Now go to “Security” tab and then select “Be made using this security context” and update Oracle credentials as shown below and then click OK

That’s it we have created oracle linked server. Now let’s query using this linked server.

Querying Oracle Linked Server

• Oracle linked server can be accessed directly or through OPENQUERY
• Connect to SQL Server through SSMS and then open new query window and then execute the below command

--Linked Server Format
SELECT * FROM ORA..ORADB.PRODUCT
--Using OPENQUERY
SELECT * FROM OPENQUERY('SELECT * FROM PRODUCT')

Finally we are able to pull data as shown below.

Configuring any heterogeneous linked server easy only thing is you need to get proper driver for that RDBMS. Hope this article will help you in configuring oracle linked server.