How to create Linked Server from Microsoft SQL Server to an Oracle Autonomous Database

August 16, 2022 | 4 minute read
Ramesh Babu Donti
Principal Cloud Architect
Text Size 100%:

There will be a requirement to query the data from Oracle databases from Microsoft SQL Server. To query the data from the Oracle database, you need to create the SQL Server Linked Server to query the Oracle data. You will need to install and configure the Oracle Database client to make a successful connection to Oracle Autonomous Database.

Perform the below steps on Windows Server to install and configure the Oracle Database 19c client:

Download the Oracle Database client:

You can download the Oracle 19c client software from the link.  

Oracle 19c Client

Extract the zip file and click setup.exe.

Oracle 19c Client setup

Install the Oracle Client 19c. During the installation, choose the installation type Administrator, as shown in the following image.Install the Oracle client 19c. During the installation, choose the installation type Administrator, as shown in the following image.

Oracle Database Client 19c

Download the Download Client Credentials (Wallets) from Autonomous database using the link.

Update Credentials (Wallets) in Windows Server:

Uncompress Wallet .zip file into a secure folder. In the uncompressed folder, edit the sqlnet.ora file, replacing “?/network/admin” with the name of the folder containing the client credentials.

For example:  

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="C:\app\client\opc\product\19.0.0\client_1\network\admin ")))

SSL_SERVER_DN_MATCH=yes

Create Environment Variable:

To edit an environment variable, go to your computer’s control panel > System and Security > System > Advanced System Settings. 
Navigate to System Properties, click on the Advanced tab, then click on Environment Variables.

Create a new environment variable ORACLE_HOME, then set it to the Oracle Client software location. 
Create a new environment variable named TNS_ADMIN, then set it to the secure folder containing the credentials file. 

Environment Variable

Test tnsname resolution:

Open cmd.exe and run the tnsping command.  You will get the similar output as shown in the following image.

tnsping test

 

Create a Linked Server to an Autonomous Oracle Database:

To verify the OLE DB provider, connect the SQL Server engine from SQL Server Management Studio and navigate to Server Objects >> Providers.

Oraledb

To allow inprocess, Right-click on ORAOLEDB.Oracle provider and enable Allow inprocess.

Oracle Provider For OLE DB

To create the Linked Server, connect the SQL Server engine from SQL Server Management Studio and navigate to Server Objects. Expand the Server Objects section, and you will find Linked Servers. Right-click on Linked Servers to create the New Linked Server.

SQL Linked Server to Oracle

 

Provide the details as shown in the following image and the Data source (Oracle Connection String TNS Name) details.

Oracle DataSource

Provide the Oracle remote login and password, and click ok to create the Linked Server. 

Oracle Remote login

Right-Click on the Linked Server and click on Test Connection to check the linked server connectivity to the Autonomous Oracle Database. 

Linked Server test connectivity check

 

To query the Oracle table from SQL Linked Server, run the following query.  

 SELECT * FROM OPENQUERY([ORACLETEST], 'select sysdate from dual');

The results are shown as follows.

SQL Query check

Ramesh Babu Donti

Principal Cloud Architect


Previous Post

OCI demonstrates its alignment with 2G3M Framework in Japan’s medical industry

Toyin Adewole | 2 min read

Next Post


Design APIs with OCI Code Editor

Shyam Suchak | 4 min read