How To Create a Linked Server To Connect To Oracle

dbtut

6 years ago

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

EXEC master.dbo.sp_addlinkedserver @server = 'XServer', @srvproduct = 'Oracle', @provider = 'OraOLEDB.Oracle',
@datasrc = 'Xsource' ,@provstr=N'SERVER=serverName\InstanceName;User ID=myUser'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'XServer', @locallogin = 'myUser' , @useself = N'False',
@rmtuser = N'oracleuser', @rmtpassword = N'oracleuserşifre'

EXEC master.dbo.sp_addlinkedserver @server = 'XServer', @srvproduct = 'Oracle', @provider = 'OraOLEDB.Oracle',

@datasrc = 'Xsource' ,@provstr=N'SERVER=serverName\InstanceName;User ID=myUser'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'XServer', @locallogin = 'myUser' , @useself = N'False',

@rmtuser = N'oracleuser', @rmtpassword = N'oracleuserşifre'

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

USE MASTER
GO
GRANT EXECUTE ON sys.xp_prop_oledb_provider TO myLogin

USE MASTER

GRANT EXECUTE ON sys.xp_prop_oledb_provider TO myLogin

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.