Infrastructure at your Service

Ali Asghari

Create and configure an Oracle linked server in SQL Server Instance

What is Linked server ?

Now a days in each company we have different applications and each one of them have different needs in term of databases support. When data exists on multiple databases the management become a little bit hard. For exemple moving some data from an Oracle database to a SQL Server database is not easy.

A linked servers is configured to enable the Database Engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, or another database product such as Oracle.

 

In this Blog, we will see step by step guide to create and configure an Oracle linked server in SQL Server Instance.

  • Download ‘oracle database client’
    https://edelivery.oracle.com/osdc/faces/SoftwareDelivery
  • Install ‘Oracle database client’

Install oracle database client

install oracle database client

install oracle database

  • Create a tnsnames.ora file in (C:\app\client\Administrator\product\12.2\client_1\network\admin)
WSDBA01 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.106)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = WSDBA01)
    )
  )

 

  • Run powershell as administrator and then do a tnsping
    Check your firewall on each server and between them. In this exemple we do not have any firewall.

 

  • Check your provider by creating a ‘test_connection.udl’ file in your Desktop
    Then double click on it (open it)

  • Choose ‘Oracle Provider for OLE DB’ and then click Next

test oracle OLE DB provider

  • Enter your service name (defind in tnsnames.ora file) for ‘Data Source’
    Enter a valide username and password and then click on ‘Test Connection’

  • Open your SSMS (SQL Server Management Studio) and connect to your SQL Server instance
    Under ‘Server Objects’ then ‘Linked Servers’ and then ‘Providers’ you must see the ‘OraOLEDB.Oracle’

  • Right Click on it and then ‘Properties’
    Check mark the ‘Allow inprocess’ and then Click on ‘OK’

 

  • Right click on ‘Linked server’ and then choose ‘New linked server’
  • In General section :
    ‘Linked server’ : Give a name to your Linked server
    ‘Provider’ : Choose ‘Oracle Provider for OLE DB’
    ‘Data source’ : Your service name (defined in tnsnames.ora file)

  • In Security Section
    Put a valide username and password and then click on ‘OK’

  • Now under your Linked server and then ‘Catalogs’ and then ‘default’ your must see every tables that your user have right to query.

 

enjoy 🙂

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Ali Asghari
Ali Asghari

Consultant