How To Create a Linked Server To Connect To Oracle

 

In some cases, a database that resides on SQL Server may require the data to be retrieved from the Oracle database.

 

Requirements

First, a related user is created on the oracle side.

If the Oracle Client did not installed on the server where your SQL Server is located,, the installation is necessary.

The tns information to be used by the user created on the Oracle side is saved in the tnsnames.ora file in the path where the client is installed (usually this happens -> C: \ app \ product \ x.y.z \ client_1 \ network \ admin).

If you can not save to the Tnsnames.ora file, open the notepad with run as adminintrator and then open the file “tnsnames.ora” which I mentioned above with Path-> Open.

 

Create a Linked Server

Then you can do this with the help of the following script.

myUser Sql login to use linked server
Xsource The name you give to TNS that you have saved in the “Tnsnames.ora” file
XServer Linked server’s name
oracleuser The user created on the Oracle side
oracleuserşifre The password of the user created on the Oracle side
serverName\InstanceName SQL Intance Name
 

To run the part of the above script that is related to “addlinkedsrvlogin” you need to know the user and password that has the privilege on the oracle side.

It is not appropriate for an MS SQL database expert to know a user password defined on oracle.

So you can make this operation to the developer that will use this linked server without ever learning this password.

The details of how to do this can be found in the article titled “How To Change The Remote Login Password For The Linked Server“.

 

In some cases, you may experience difficulty with linked server after installing Oracle Client.

If you make the following settings by right clicking on the OraOLEDB.Oracle tab under ServerObjects-> LinkedServers-> Providers in the SQL Server Instance, you will fix the problem.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

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