Written by Nisarg Upadhyay• March 24, 2021• 12:43• Database development

Create and Configure Oracle Linked Server in SQL Server

HomeDatabase developmentCreate and Configure Oracle Linked Server in SQL Server

This article explains the step-by-step process of creating and configuring a linked server between Oracle 19c and SQL Server 2019.

Pre-requisites to create a linked server

For demonstration, I have installed SQL Server 2019 on my workstation. Along with it, I installed Oracle 19c and created an instance named EMPLOYEE. In my further articles, I will explain the step-by-step installation and configuration process of Oracle 19c too.

To configure the linked server, we must first create a database in Oracle and then create a user in the Oracle DB. The CREATE USER command will do it. The GRANT keyword ensures the connect, resource, and unlimited tablespace privileges to the user. You may refer to this article for more details about the CREATE USER keyword.

Open SQL Plus and get it connected by Oracle using the following command:

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Feb 21 17:40:15 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Enter user-name: sys as sysdba

Open SQL Plus and connect to the Oracle database using the following command

The command prompts for the password. Keep it empty and hit Enter.

Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

Now, we are connected to the Oracle instance successfully. Run the following query to create a user named nisarg:

SQL> create user nisarg identified by "Test@123";
User-created.

The next query grants the appropriate permission to the nisarg user:

SQL> grant connect,resource, unlimited tablespace to nisarg;
Grant succeeded.

Create a table on the Employee database

We are creating three tables: tblemployee, tblDepartment, and tblDesignation. The foreign key is on the tblemployee table. The foreign key columns are employee_dept_id and employee_designation_id. They reference to tblDepartment and tblDesignation tables. Scripts to create the tables are below.

Creates the tblemployee table:

CREATE TABLE NISARG."tblEmployee" (
  ID                        NUMBER(10, 0) GENERATED ALWAYS AS IDENTITY (
  MAXVALUE 1000000000000000000000000000),
  "Employee_name"           NVARCHAR2(50),
  "Employee_dept_id"        INTEGER,
  "Address"                 NVARCHAR2(1000),
  "ContactNumber"           NVARCHAR2(50),
  "Employee_designation_id" INTEGER,
  CONSTRAINT FK_TBLEMPLOYEE_EMPLOYEE_DEPT_ID FOREIGN KEY ("Employee_dept_id")
  REFERENCES NISARG."tblDepartment" ("Dept_ID"),
  CONSTRAINT FK_TBLEMPLOYEE_EMPLOYEE_DESIGN FOREIGN KEY ("Employee_designation_id")
  REFERENCES NISARG."tblDesignation" ("Designation_ID")
)
TABLESPACE USERS;

Create the tblDepartment table:

CREATE TABLE NISARG."tblDepartment" (
  "Dept_ID"   INTEGER GENERATED ALWAYS AS IDENTITY (
  MAXVALUE 1000000000000000000000000000),
  "Dept_name" NVARCHAR2(50)
)
TABLESPACE USERS;

Create the tblDesignation table:

CREATE TABLE NISARG."tblDesignation" (
  "Designation_ID"   NUMBER(10, 0) GENERATED ALWAYS AS IDENTITY (
  MAXVALUE 1000000000000000000000000000),
  "Designation_Name" NVARCHAR2(50)
)
TABLESPACE USERS;

Here you can see the ER diagram:

Now, let us create the linked server.

Create the Linked Server using SSMS

When we install Oracle 19c, client or server, a provider named OraOLEDB.Oracle will be created. These providers ensure communication between the SQL Server and other data sources or databases.

After installing Oracle, you can see that the OLEDB Provider has been created under the Providers node of SQL Server Management Studio.

Before we create a new linked server, we must enable the Allow inprocess parameter.

Right-click on OraOLEDB.Oracle and click on Properties:

On the Provider Options screen, click on Allow inprocess. Click OK to save configuration:

To create a linked server, open SSMS and connect to the database engine. Expand Server Objects > Linked Servers > New Linked Server:

In the configuration window, specify the following details:

Linked server. Specify the name of the linked server. This name must not contain space or other characters. In our case, the name of the linked server is ORACLESERVER.
Server Type. You can choose SQL Server or other data sources. In our case, we are creating a linked server between Oracle and SQL Server. Therefore, choose Other data source.
Provider. Select the name from the Provider drop-down menu. In our case, it is Oracle Provider for OLEDB.
Data Source. The data source name is the Service name of the Oracle instance. In our case, it is employee.

New Linked Server. The configuration window settings

You can view the list of services under Service Naming of the Oracle Net Configuration manager > Service Naming > select the service name.

Now, let us configure the security settings to connect to the Oracle database server: in the New Linked Server window, go to the Security section.

We establish the connection using the Remote login and Password. For that, select the Be made using this security context option and specify the credentials:

To execute remote stored procedures, we must enable RPC (remote procedure call) in the linked server.

Go to the Server Options section and set the RPC value as True:

Now, click OK to create a linked server. Once it is done successfully, you can view it under the LinkedIn Servers node in SSMS:

Create a linked server using T-SQL

You can execute the T-SQL script to create a linked server. Then, to add a linked server, you can use the sp_addlinkedserver stored procedure. To configure the login for the linked server, you can use the sp_addlinkedsrvlogin stored procedure. To set the configuration parameters, you can use the sp_serveroption stored procedure.

In our case, you should execute the following code to add the linked server:

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'ORACLESERVER', 
@srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'employee'

Execute the following code to configure the linked server security:

USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ORACLESERVER',
@useself=N'False',@locallogin=NULL,@rmtuser=N'nisarg',@rmtpassword='########'

The following code is for setting the configuration parameters:

EXEC master.dbo.sp_serveroption @server=N'ORACLESERVER', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORACLESERVER', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ORACLESERVER', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORACLESERVER', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORACLESERVER', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ORACLESERVER', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ORACLESERVER', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORACLESERVER', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ORACLESERVER', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'ORACLESERVER', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORACLESERVER', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ORACLESERVER', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ORACLESERVER', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

Now, let us test the configuration by querying the data.

Test the connectivity by querying the data

To test connectivity, launch SQL Server Management Studio and execute the following query:

select * from [oracleserver]..NISARG.tblEmployee

The query output:

The output of the query to test connectivity

As you can see, the query has been executed successfully. It indicates the successful configuration of the linked server.

Now, let us create a stored procedure in SQL Server to access the data from the Oracle database and run Oracle SQL queries:

create procedure sp_view_employee
as
begin
select * from [oracleserver]..NISARG.tblEmployee
end

Run the below command to execute the stored procedure:

Exec sp_view_employee

The query output:

The output of the query to execute stored procedure

Let us execute the parameterized stored procedure. The code is following:

create procedure sp_view_employee_name
@employeeName varchar(50)
as
begin
select * from [oracleserver]..NISARG.tblEmployee where employee_name=@employeeName
end

Run the procedure:

exec sp_view_employee 'Nisarg Upadhyay'

The query output:

Summary

This way, you can configure the Linked server in Oracle vs SQL Server 2019. We’ve described the process step-by-step, and we hope this article will help you in your work.

Read also

Pitfalls of Linked Server Usage

Tags: linked server, oracle, sql server Last modified: July 15, 2022

About the Author / Nisarg Upadhyay

Nisarg is a SQL Server Database Administrator and Microsoft certified professional who has more than 5 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the Bachelors in Information Technology from Ganpat University.