Skip to content

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 🙂

Let’s work together

Ready to operate at
the next level?

Tell us about your challenge. We’ll bring the depth, the expertise, and the Swiss precision.

Oracle Team

Oracle Team

Posts published under the Oracle Team account are collective contributions by the dbi services Oracle consultants. The practice covers the full Oracle Database lifecycle: architecture, migration and upgrade, high availability with RAC and Data Guard, backup and recovery, performance tuning, and engineered systems such as Oracle Database Appliance and Exadata. The team holds Oracle certifications up to Certified Master level and operates customer platforms under service level agreements.View Author posts

1,009 posts since December 2010

Most read in Oracle

The Oracle posts our readers open most: the all-time favourites, and what is trending right now.

Leave a Comment

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