How can we connect SQL Server to Oracle Database using Linked Server

How can we connect SQL Server to Oracle Database using Linked Server
How can we connect SQL Server to Oracle Database using Linked Server

THE SQL Server it allows us to be able to connect and execute queries on remote Database Instances that are not necessarily in the same RDBMS. In this article we will see specifically how it is done from instance of SQL Server to its instance Oracle Database using Linked Server.

In previous article we had seen how to connect SQL Server to Oracle using PolyBase. The main difference is that with Linked Server we don't simply read data but we can execute Stored Procedures and modify the data with inserts, updates, deletes.

* All the differences between Linked Server and PolyBase can be read from here.

The installation

First we have to download him Oracle Database Client from here.

Then we will have to install it on the server that hosts SQL Server.

When installing as installation type we choose Administrator:

How can we connect SQL Server to Oracle Database using Linked Server
01

In the next tab we select Windows Build-in Account:

How can we connect SQL Server to Oracle Database using Linked Server
02

We leave the following tabs as they are and complete the installation.

Then we go to the following folder in the path that has been created and put where user the name of the user to whom the installation was done:

C:\app\client\user\product\19.0.0\client_1\network\admin

Inside this folder there will be a file named tnsnames.ora. We edit it and add the following entry by putting a name (e.g. ORADEV), the hostname, the door and as service_name the name of the database or service we want to connect to:

ORADEV=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracledev1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = oradev)
    )
  )

To test that so far everything is playing well we can open a Command Prompt and do tnsping with the name of the record in tnsnames.ora:

How can we connect SQL Server to Oracle Database using Linked Server
03

If it appears at the end Okay it means that we are a blessing, otherwise we may be cut by some firewall or network issue.

After making sure that the communication is working, we connect to SQL Server from the SQL Server Management Studio and we choose Server Objects and right click Linked Server, New Linked Server…:

How can we connect SQL Server to Oracle Database using Linked Server
04

In the tab General we define the name we want to have Linked Server, as Server type Provider Oracle Provider for OLE DB and as Data source the name we gave the registration to tnsnames.ora:

How can we connect SQL Server to Oracle Database using Linked Server
05

In the tab Security we choose Be made using this security context and put the credentials of the Oracle user with whom we will connect:

How can we connect SQL Server to Oracle Database using Linked Server
06

For our example I have created a user named “Stratos” which has right connect, resource, unlimited quota on tablespace users and I have made in it a table “Pelates” with 3 entries:

-- Create the user 
create user STRATOS identified by "password"
  default tablespace USERS
  temporary tablespace TEMP
  profile DEFAULT
  quota unlimited on users;
-- Grant/Revoke role privileges 
grant connect to STRATOS;
grant resource to STRATOS;

-- Create table
create table stratos.pelates
(
  id    INTEGER,
  onoma VARCHAR2(10)
);

insert into pelates values(1,'Stratos');
insert into pelates values(2,'Nikos');
insert into pelates values(3,'Giorgos');

commit;

By completing the Linked Server we can with the use openquery call the Linked Server we created and define the query we want to run to Oracle:

select * from openquery([oracle server], 'select onoma from pelates');
How can we connect SQL Server to Oracle Database using Linked Server
07

In the event that the following error appears during execution:

Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE SERVER".

Then we execute the following command which will easily fix the problem:

EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1

Sources:

Share it

Leave a reply