X

Information, tips, tricks and sample code for Big Data Warehousing in an autonomous, cloud-driven world

How to Access Non-Oracle Databases from Autonomous Database using Oracle Database Gateway

Can Tuzla
Senior Product Manager

Being able to create database links from Autonomous Database on Shared Infrastructure (ADB-S) to other ADB-S instances or Oracle databases has been one of the most sought-after features that we introduced. ADB-S now supports creating database links to Oracle Database Gateways in order to access non-Oracle databases. In this blog post, we are going to explore how to access a Microsoft SQL Server database from an ADB-S instance using a database link that we will create to a gateway.

Before we jump on the detailed steps, let’s do a quick recap of database links in ADB-S. As you may already know, ADB-S requires the target database to be configured with TCP/IP with SSL (TCPS) authentication for outgoing database links since it uses TCPS authentication by default. What this means is that the gateway we want to connect needs to be configured with TCPS authentication as well. We already covered how to configure an Oracle database, such as DBCS, with TCPS authentication in one of my earlier blog posts. Good news is that configuring Oracle Database Gateway with TCPS is very similar and requires couple minor additional steps. Let’s start!

The non-Oracle database that I will be using for this demonstration is Microsoft SQL Server 2019 Express running on Windows Server 2019. For simplicity, I chose to install Oracle Database Gateway 19c on the same Windows VM. The installation of the gateway using the Oracle Universal Installer is extremely quick and simple. All you need to provide is a Windows user that has no admin privileges (gateway owner), which non-Oracle database you are planning to connect to and the details of that database such as host name, port number, database name etc. The installer also partially configures a listener for the gateway during the installation and it’s possible to choose TCPS authentication in the UI (I will explain what I mean by 'partially' in a moment). Here’s how my listener.ora looked like after the installation (note the TCPS protocol in the listener description):

 
# listener.ora Network Configuration File: C:\app\GW\product\19.0.0\tghome_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCPS)(HOST = ctuzlaWindows)(PORT = 1522))
    )
  )

SID_LIST_LISTENER = 
  (SID_LIST= 
    (SID_DESC=
      (SID_NAME=dg4msql)
      (GLOBAL_DBNAME=dg4msql_svc)
      (ORACLE_HOME=C:\app\GW\product\19.0.0\tghome_1)
      (PROGRAM=dg4msql)
    ) 
  )

In the listener.ora above, LISTENER was automatically created by the installer; however, I had to manually add the SID_LIST_LISTENER variable. It’s important to note that SID_NAME is the SID of the gateway and GLOBAL_DBNAME is the service name of the gateway. The value of the GLOBAL_DBNAME variable can be anything you specify and this is the value that we will be using as the target database service name when we create our database link from our Autonomous Database. Additionally, as you can tell from the ORACLE_HOME path, GW is our Windows user who is also the gateway owner.

Even though our listener already has the TCPS endpoint, we still need to perform couple additional steps so that our ADB-S instance can successfully communicate with the gateway. Here are the steps needed to complete the TCPS configuration in our gateway:

  • Create wallets with self signed certificates for server and client
  • Exchange certificates between server and client wallets (Export/import certificates)
  • Add wallet location in the server network files

Create wallets with self signed certificates for server and client

As part of enabling TCPS authentication, we need to create individual wallets for the server and the client. Each of these wallets has to have their own certificates that they will exchange with one another. For the sake of this example, I will be using a self signed certificate. The client wallet and certificate can be created in the client side; however, I'll be creating my client wallet and certificate in the server and moving them to Object Store later on. See Configuring Secure Sockets Layer Authentication for more information.

Directories to be used for the wallets and certificates

C:\Users\GW\Desktop\server\wallet
C:\Users\GW\Desktop\client\wallet
C:\Users\GW\Desktop\tmp

Create a server wallet with the GW user

C:\Users\GW\Desktop\server\wallet>orapki wallet create -wallet ./ -pwd ************ -auto_login
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.

Create a server certificate with the GW user

C:\Users\GW\Desktop\server\wallet>orapki wallet add -wallet ./ -pwd ************ -dn "CN=windows" -keysize 1024 -self_signed -validity 3650 -sign_alg sha256
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.

Create a client wallet with the GW user

C:\Users\GW\Desktop\client\wallet>orapki wallet create -wallet ./ -pwd ************ -auto_login
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.

Create a client certificate with the GW user

C:\Users\GW\Desktop\client\wallet>orapki wallet add -wallet ./ -pwd ************ -dn "CN=ctuzla" -keysize 1024 -self_signed -validity 3650 -sign_alg sha256
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.

Exchange certificates between server and client wallets (Export/import certificates)

Export the server certificate with the GW user

C:\Users\GW\Desktop\server\wallet>orapki wallet export -wallet ./ -pwd ************ -dn "CN=windows" -cert C:\Users\GW\Desktop\tmp\server.crt
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.

Export the client certificate with the GW user

C:\Users\GW\Desktop\client\wallet>orapki wallet export -wallet ./ -pwd ************ -dn "CN=ctuzla" -cert C:\Users\GW\Desktop\tmp\client.crt
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.

Import the client certificate into the server wallet with the GW user

C:\Users\GW\Desktop\server\wallet>orapki wallet add -wallet ./ -pwd ************ -trusted_cert -cert C:\Users\GW\Desktop\tmp\client.crt
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.

Import the server certificate into the client wallet with the GW user

C:\Users\GW\Desktop\client\wallet>orapki wallet add -wallet ./ -pwd ************ -trusted_cert -cert C:\Users\GW\Desktop\tmp\server.crt
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Operation is successfully completed.

Add wallet location in the server network files

We now need to modify the server network files so that they point to the server wallet location and they are ready to use the TCPS protocol. Here's how those files look in my case:

Server-side $ORACLE_HOME/network/admin/sqlnet.ora

 
# sqlnet.ora Network Configuration File: C:\app\GW\product\19.0.0\tghome_1\NETWORK\ADMIN\sqlnet.ora
# Generated by Oracle configuration tools.

SSL_SERVER_DN_MATCH= (ON)

WALLET_LOCATION =
  (SOURCE =
    (METHOD = File)
    (METHOD_DATA =
      (DIRECTORY = C:\Users\GW\Desktop\server\wallet)
    )
  )

Server-side $ORACLE_HOME/network/admin/listener.ora

 
# listener.ora Network Configuration File: C:\app\GW\product\19.0.0\tghome_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

WALLET_LOCATION =
  (SOURCE =
    (METHOD = File)
    (METHOD_DATA =
      (DIRECTORY = C:\Users\GW\Desktop\server\wallet) 
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCPS)(HOST = ctuzlaWindows)(PORT = 1522))
    )
  )

SID_LIST_LISTENER = 
  (SID_LIST= 
    (SID_DESC=
      (SID_NAME=dg4msql)
      (GLOBAL_DBNAME=dg4msql_svc)
      (ORACLE_HOME=C:\app\GW\product\19.0.0\tghome_1)
      (PROGRAM=dg4msql)
    ) 
  )

Notes About the Target Environment

  • If your target database is hosted on a Windows VM, make sure the firewall is turned off or configured in a way to not block the incoming traffic.
  • As you can see in my listener description above, my listener is configured on port 1522. Since my VM is hosted in OCI, I needed to add an ingress rule for that port that in the security list of my virtual cloud network (VCN). Without this step, I wouldn’t be able to reach the listener from my Autonomous Database.
  • Here’s how the HS parameters in my gateway agent init file looks like:
# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server
#
# HS init parameters
#
HS_FDS_CONNECT_INFO=ctuzlaWindows:8000//mssfinance
HS_FDS_TRACE_LEVEL=ODBC
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

Please note that my SQL Server database (mssfinance) is configured to have TCP enabled on port 8000 as shown above.

  • Confirm the listener status:
C:\Users\GW>lsnrctl status

LSNRCTL for 64-bit Windows: Version 19.0.0.0.0 - Production on 24-JUL-2020 09:27:30

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=ctuzlaWindows)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 19.0.0.0.0 - Production
Start Date                17-JUL-2020 20:35:35
Uptime                    6 days 12 hr. 51 min. 59 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\app\GW\product\19.0.0\tghome_1\network\admin\listener.ora
Listener Log File         C:\app\GW\diag\tnslsnr\ctuzlaWindows\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=ctuzlaWindows)(PORT=1522)))
Services Summary...
Service "dg4msql_svc" has 1 instance(s).
  Instance "dg4msql", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Create a Database Link from ADB-S to the Gateway

We now have a working TCPS authentication in the gateway. Here are the steps from the documentation that we will follow to create a database link from ADB-S to the gateway:

  • Copy the client wallet (cwallet.sso) that we created in C:\Users\GW\Desktop\client\wallet to Object Store.
  • Create credentials to access your Object Store where you store the cwallet.sso. See CREATE_CREDENTIAL Procedure for details.
  • Create a directory to store the target database wallet:
SQL> create directory wallet_dir as 'walletdir';

Directory WALLET_DIR created.
  • Upload the wallet to the wallet_dir directory on ADB-S using DBMS_CLOUD.GET_OBJECT:
SQL> BEGIN
  DBMS_CLOUD.GET_OBJECT(
    credential_name => 'OBJ_STORE_CRED',
    object_uri => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/adbstraining/b/target-wallet/o/cwallet.sso',
    directory_name => 'WALLET_DIR'); 
END;
/    
 
PL/SQL procedure successfully completed.
  • On ADB-S, create credentials to access the target database. The username and password you specify with DBMS_CLOUD.CREATE_CREDENTIAL are the credentials for the target database that you use to create the database link:
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'SSFINANCE_LINK_CRED',
    username => 'mssadmin',
    password => '************');
END;
/ 

PL/SQL procedure successfully completed.
  • Create the database link to the target database using DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK:
BEGIN
     DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
          db_link_name => 'FINANCELINK', 
          hostname => '149.343.90.113', 
          port => '1522',
          service_name => 'dg4msql_svc',
          ssl_server_cert_dn => 'CN=windows',
          credential_name => 'SSFINANCE_LINK_CRED',
          directory_name => 'WALLET_DIR',
          gateway_link => TRUE);
END;
/

PL/SQL procedure successfully completed.

Note that DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK procedure has a new parameter called gateway_link that needs to be set to TRUE when creating a database link to an Oracle Database Gateway.

  • Use the database link you created to access the data on the target database:
select count(*) from CUSTOMERS@FINANCELINK;

COUNT(*)
--------
4

This is all you need in order to access a non-Oracle database from your Autonomous Database! In this post, we have gone through gateway installation, TCPS configuration in gateways, target environment tips and database link creation in order to access a SQL Server database. The same steps apply to other non-Oracle databases that are supported by Oracle Database Gateway as well.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.