10 October, 2020

On-Premises SQL Server to Oracle ADB on the Cloud - 1

Setting up a connection between an On-Premises SQL Server Database Instance to an Oracle ADB on the Oracle Cloud

Strong Caveat :  This is only a POC.  Most organisations would NOT allow an open direct connection between an On-Premises Database and and External Site (whether a Database or any other Service).  

1. On my Free-Tier Oracle ADB, I login as the ADMIN user and configure a new database account "ss_user"

SQL Developer to Oracle ADB

2. I install Oracle Client and configure connectivity to the ADB database with the Wallet information

(for detailed instructions see the Oracle Cloud documentation here).  {I used an 18c Client on Windows to connect to 19c ADB, simply because I already and 18c client and didn't want to wait to download the 19c client}



3.  Optionally re-register the OraOLEDB18.DLL file  (using CMD as Administrator)  (you might also need to reboot your Windows or restart the SQL Server Instance)

CMD as Administrator

4.  Define the Linked Server in SQL Server (using SSMS)

Oracle OLEDB Provider

Linked Server Configuration Pag

Linked Server Security Configuration

5.  Test Connectivity

Test Connectivity Option for Linked Server

6  Run queries against from SSMS

SSMS Query and Results Pane

See the next blog post on actual transaction and query (insert and select from On-Premises SQL Server to Oracle ADB in the cloud)

No comments: