Creating a SQL Linked Server to an Oracle database server

Within SQL it is possible to create a Linked Server to connect to an Oracle database server.

In previous versions of SQL, this was done by using the OLE DB Provider for Oracle. But this is this component is currently in maintenance mode. No new updates are planned for future releases of these components including their support against versions later than Oracle 8i. Read more.

Nowadays we can use the Oracle Provider for OLE DB to setup connections to an Oracle server from SQL server.

Steps

  1. Download and Install the Oracle x64 client
  2. Check if the System Environment Variable %ORACLE_HOME% is set to the installation directory of the Oracle client. If not, create the variable.
  3. Modify the TNSNAMES.ora file in the installation folder of the Oracle client so it contains the information about the Oracle server.

    image

  4. Download and Install Oracle ODAC x64 .
    This creates a OraOLEDB.Oracle provider in SQL Management Console under Server Objects, Linked Servers, Providers and enables the Oracle Provider for OLE DB option in the provider drop-down menu when creating a new Linked Server to Oracle.

    SNAGHTML175fb85

  5. Create a new Linked Server (make sure Step 2 and 3 are completed)

    image

  6. Enter the name for the Linked Server.
  7. Select Oracle Provider for OLE DB from the drop-down menu
  8. The Product Name and Datasource is the description of the sever in TNSnames.ora

    SNAGHTML1767f25

  9. On the Security tab, select Be made using the security context and enter the username and password of the user that has permissions on the Oracle database

    SNAGHTML176c43f

  10. Leave the Server Options default and click Ok

    SNAGHTML176f425

  11. Right click on the new Linked Server and click on Test Connection

    image

  12. The Linked Server can now be used to access the Oracle Database server.

    image

Advertisements
This entry was posted in Microsoft General and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s