SOURCE_ROUTE in easy connect plus syntax

I regularly use Oracle connection manager (CMAN) to simplify connectivity to the database in complex IT environments. Normally the network infrastructure between the CMAN host and the target service is transparent for the client. 

but in some cases, the client can (or need) to define not only the connection to the CMAN, but also the 2nd leg to the DB service. This is called SOURCE_ROUTE in Oracles network naming. 

A common (quite simple) connection description is this tnsnames.ora entry: 

ALIAS=
 (DESCRIPTION= 
   (SOURCE_ROUTE=yes) 
   (ADDRESS=(PROTOCOL=tcp)(HOST=CMAN_HOST)(PORT=1521))  
   (ADDRESS=(PROTOCOL=tcp)(HOST=DB_INSTANCE_HOST)(PORT=1521))  
   (CONNECT_DATA=(SERVICE_NAME=DB_Service))
 )

By the definition SOURCE_ROUTE=yes the first entry defines the direct connection to the CMAN_HOST. In this connection the 2nd connection is described implicit to go to DB_INSTANCE_HOST. 

 

But sometimes, a tnsnames.ora entry isn't useful and a Easy Connect syntax is preferred. The simplest syntax does not show any way for a multi step connection. 

host:port/SERVICE_NAME

But this very simple syntax

the full possible syntax is

[[protocol:]//]host1{,host2}[:port1]{,host2:port2}[/[service_name][:server_type][/instance_name]][?parameter_name1=value1{&parameter_name2= value2}]

In a more specific case it looks like

//CMAN_HOST,DB_INSTANCE_HOST:1521/DB_Service?SOURCE_ROUTE=YES

And it works quite fine. I can do a simple test with sqlplus:

sqlplus x/x@CMAN_HOST,DB_INSTANCE_HOST:1521/DB_Service?SOURCE_ROUTE=YES

SQL*Plus: Release 21.0.0.0.0 - Production on Mon Jun 26 10:54:02 2023
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

And ORA-01017 is exactly what I expect: I could reach the instance, but of course authentication for user x with password x failed.

Another feature worth to mention is tnsping. This only tries to connect to the given listener but never checks for the service available. In addition it also prints the connection it tested in tnsnames.ora format. A simple connect string looks like: 

tnsping CMAN_HOST:1521/DB_Service

TNS Ping Utility for Linux: Version 21.0.0.0.0 - Production on 26-JUN-2023 10:53:44

Copyright (c) 1997, 2021, Oracle.  All rights reserved.

Used parameter files:
/tns/admin/sqlnet.ora

Used EZCONNECT adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=DB_Service))(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.2)(PORT=1521)))
OK (10 msec)

But even with a source route defined, tnsping is quite honest what's (not) doing: 

tnsping CMAN_HOST:1521,DB_INSTANCE_HOST:1521/DB_Service?SOURCE_ROUTE=YES

TNS Ping Utility for Linux: Version 21.0.0.0.0 - Production on 26-JUN-2023 10:53:51

Copyright (c) 1997, 2021, Oracle.  All rights reserved.

Used parameter files:
/tns/admin/sqlnet.ora

Used EZCONNECT adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=DB_Service))(SOURCE_ROUTE=YES)(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.2)(PORT=1521)))
OK (10 msec)

It adds the SOURCE_ROUTE=YES entries, but does not add the 2nd host (or IP) it doesn't even try to reach the DB_INSTANCE_HOST. 

The usage of tnsping is limited in any cman environment. 

qgis with Oracle Cloud Database

qgis is A Free and Open Source Geographic Information System. It can use several spatial backends, and Oracle is just one of them. From what I see it is quite often used with other databases like PostgreSQL or even SpatiaLite. And even Oracle is quite well supported, there sem to be some loose ends, especially for complex situations.

One of these complex situations is the connection to an Always Free Autonomous Database. Oracle tries to secure the database access, so by default it does not allow a simple connection with host, port, service_name and username/password. Instead a wallet is provided to secure the network access before providing the credentials. 

The zip contains several files which can be used for OCI or JDBC connections. In it's tnsnames.ora the aliases are slightly more complex than normally expected:

db1234_medium = 
  (description= 
    (retry_count=20)(retry_delay=3)
    (address=
      (protocol=tcps)
      (port=1522)
      (host=adb.eu-frankfurt-1.oraclecloud.com)
    )
    (connect_data=
      (service_name=pxyz_db1234_medium.adb.oraclecloud.com)
    )
    (security=
      (ssl_server_cert_dn="CN=adwc.eucom-central-1.oraclecloud.com, OU=Oracle BMCS FRANKFURT, O=Oracle Corporation, L=Redwood City, ST=California, C=US")
    )
  )

On the other hand, qgis (3.24.1) provides these input fields:
Name, Database, Host and Port define the connection endpoint. But there is no field available for additional details like protocol=tcps or ssl_server_cert_dn=.... Also the specific wallets location can not be provided. 

But it is possible to add all these details. To understand the method, it's required to understand how qgis simply concatenates the given strings to one connection URL which is then handed over to the Oracle client libraries. This client also accepts an Easy Connect String.  

With this information, I managed to generate these values: 

Database	pxyz_db1234_medium.adb.oraclecloud.com?ssl_server_cert_dn="CN=adwc.eucom-central-1.oraclecloud.com, OU=Oracle BMCS FRANKFURT, O=Oracle Corporation, L=Redwood City, ST=California, C=US"&wallet_location=C:\tns_admin
Host	tcps://adb.eu-frankfurt-1.oraclecloud.com
Port	1522

Here are 3 important details: in the Database field, you can add information about the certificates DN and the wallet location. In the Host field, the prefix tcps:// defines the specific protocol used. 

This alltogether generates an URL

tcps://adb.eu-frankfurt-1.oraclecloud.com:1522/pxyz_db1234_medium.adb.oraclecloud.com?ssl_server_cert_dn="CN=adwc.eucom-central-1.oraclecloud.com, OU=Oracle BMCS FRANKFURT, O=Oracle Corporation, L=Redwood City, ST=California, C=US"&wallet_location=C:\tns_admin

which is sufficient to connect to my ATP Database 
∎