How to Link a SQL Server to an Oracle Database

 There can be plethora of scenarios that we need to connect to different databases, get data into MS SQL Server and update data in remote Server . SQL Server has linked server feature through which we can link to other database servers. 

In this article, I will explain what is Linked server, advantages of Linked servers and how we can link Microsoft SQL database to oracle database and connect to Oracle database with full steps. 

Additionally, we will learn to write sample OPEN Query to fetch data from Oracle database to SQL Server database after connection succeed.

What is Linked Servers

This feature is available on all SQL Server versions and Azure SQL Managed Instance. It allows SQL Server Database Engine and Azure SQL managed Instance to read data and execute commands against remote database servers. 

For instance, Oracle database server. After, the Linked server configuration it enables SQL server Database engine to execute Transact-SQL statement. Using this feature, we can fetch data and update it in other databases without developing custom application.

 Linked Servers has following components:

• An OLE DB provider
• An OLE DB data source

Linked Servers Benefits:

• Allow to access data from outside of SQL Servers
• It has ability to perform distributed queries including select, update as well as other commands and transactions across heterogeneous data sources.
• It has ability to address different databases similarly.

Prerequisites

• Oracle Client
• MS SQL Server
• SQL Server Management Studio

To link SQL Server to an Oracle database, firstly we need to install Oracle client on the machine where we have SQL Server and SQL Server Management Studio. I have assumed that we have already installed the Oracle client.

Then, follow below steps:

Open SQL Server Management Studio(SSMS), go to Server Objects and then Linked Servers.

Right click on Linked Server and then click on New Linked Server as depicted below.

Give Linked Server Name as per your naming convention. Select Oracle Provider for OLEDB in Provider.

In Data Source use the same name which is used in the tnsnames.ora file( with or without the domain name). You can omit the Provider string and Catalog.                                                                                                                              

In Security Section, provide security details as per your organization.

If you have setup in your Oracle database with remote login and password, then select Be made using this security context as shown below and provide Remote login and password.

Finally, click on OK.

Now, Linked Server is created right click on it and click on Test Connection to verify it.

If your test do not succeed then go to  OraOLEDB.Oracle as depicted below .

You will get below screen. Mark the checkbox for Allow inprocess as illustrated below.

Try again to test the connection to verify it. You will be successfully able to connect the oracle Server.

You can now, simple run the select query and see the data in SQL Server Management Studio.

Sample query to fetch data from Oracle database to MS SQL is provided below.

SELECT * FROM OPENQUERY(LinkedServerName,'select t.* from OracleDatabaseName.TableName t')

Cheers we are succeeded to fetch data from Oracle to SQL Server.