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.

oracle_linked_server_1

  • 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.

oracle_linked_server_2

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

oracle_linked_server_3

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

oracle_linked_server_4

 

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

oracle_linked_server_5

  • 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

oracle_linked_server_6

 

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')

oracle_linked_server_7

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.


Posted

in

by

Comments

7 responses to “Creating Oracle Linked Server in SQL Server”

  1. preethi N avatar
    preethi N

    Hi,

    need an clarification please does the distributed tran works with the same environment.

    begin distributed tran

    select * from openquery (oracledb,’select * from tablename)
    commit

    please suggest , i am held in this problem for a long time

    1. VidhyaSagar avatar

      Yes it should work.

  2. Eldad avatar
    Eldad

    Hi
    made the connection and it seems to work (connection tested successfully) only i cant select from any table. Seems like a ‘grant select’ issue or something like that.
    Any Idea ?

    1. VidhyaSagar avatar

      Probably you dont have enough permission in oracle.. Make sure the account you connect to oracle got necessary permission

  3. Anandan Kanagarajan avatar
    Anandan Kanagarajan

    Vidya,

    Excellent step-by-step guide to create SQL Server Linked Server to Oracle. Thanks for the same & I am referring this link for the last at least 2 years.

    In our environment, they recently migrated to Netezza.

    The latest Netezza driver has been installed on the Server on which SQL Server 2012 instance has been installed.

    Now, I created a Linked Server using the Provider : “Microsoft OLE DB Provider for ODBC Drivers without having any TNS entry.

    However, my understanding to created Linked Server to Oracle using the following 2 Providers, the TNS entry is mandatory.

    1. Microsoft OLE DB Provider for Oracle (MSDAORA)
    2. Oracle OLE DB Provider (OraOLEDB.Oracle)

    Am I understand it correctly, please advise.

    Steps involved in my Linked Server creation :

    – Latest Netezza driver (7.0) has been installed
    – ODBC Data source has been created with relevant Netezza credentials.
    – Linked Server has been created with the provider : Microsoft OLE DB Provider for ODBC Drivers

  4. Riazul avatar

    I can view only tables and views. How can i see store procedure also

    1. VidhyaSagar avatar

      Is this something with permission on oracle side?

Leave a Reply to VidhyaSagar Cancel reply

Your email address will not be published. Required fields are marked *