How can we connect SQL Server to Oracle Database using Linked Server
Certifications:
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux - 1 July 2024
- How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
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:
In the next tab we select Windows Build-in Account:
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\adminInside 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:
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…:
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*:
*Alternatively, instead of putting the document from tnsnames.ora, we can use ezconnect by putting ip, port and service, for example oracle.dataplatform.gr:1521/orcl
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:
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');
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
