When we create an Oracle ADB on OCI, we just can't connect directly to the database even though the Network ACL is disabled. The reason is mTLS authentication is set to Required by default.
Let us see how to connect to the ADB using SQL developer first and then SQL plus.
If you have downloaded SQL developer for connecting to your database on cloud, it's super easy to connect.
Method 1: Using mTLS
Mutual TLS (mTLS) is a 2-way handshake between a client and a server, and a more secure way of connection compared to TLS.
Mutual TLS (mTLS) is a 2-way handshake between a client and a server, and a more secure way of connection compared to TLS.
Step 1: Download the wallet (This is required for mTLS)
Go to your Autonomous database details page => Click on Database connection => Click on Download wallet
Provide the password and click on Download. This is your wallet password and not the password for the database Admin user or the database application user you are trying to connect.
Step 2: Once you have this credentials wallet zip, Open SQL Developer and edit the connection properties to connect to the Autonomous database as below
Make sure we have the connection type selected as Cloud Wallet and provide the downloaded zip file under configuration file.
While selecting Service, we have different default service to choose from which differs on how the connection works. I have selected atp01_high of the below 5 available.
- atp01_high
- atp01_low
- atp01_medium
- atp01_tp
- atp01_tpurgent
Click on the Test button to check the connectivity and we now have a Successful connection.
How do we connect to using SQL Plus?
To make a SQL Plus or any Oracle Call Interface (OCI), JDBC connection to the Autonomous database, we need the client software to be installed in the server from where we are trying to connect.
Since we need the wallet location to be specified, we should have sqlnet.ora file and tnsnames.ora file
I'll use my existing on-prem Linux server to attempt a connection without the wallet file location specified in the sqlnet.ora but with only the tnsnames.ora edited with the connection details of the ADB.
[oracle@linux-8 db-sample-schemas-main]$ cd order_entry/ [oracle@linux-8 order_entry]$ sqlplus admin/xxxxx@'(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522) (host=adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=g34efa58d1e9751_atp01_high.adb.oraclecloud.com)) (security=(ssl_server_dn_match=yes)))' SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 7 03:56:14 2024 Version 19.20.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. ERROR: ORA-12506: TNS:listener rejected connection based on service ACL filtering Enter user-name:So where do I get the connection description information from?
When I click on the Database connection button on the Database details page, I'll get the connection strings for all the 5 services or the downloaded zip will include the tnsnames.ora file containing this information.
As you see when we didn't provide the wallet location, we are not able to connect with error ORA-12506.
There are 2 things you can do to fix this.
Easy way is to unzip the contents of the downloaded zip file to a directory on the server and define the TNS_ADMIN environment variable value to this directory and attempt the connection. Make sure to make changes in the sqlnet.ora file to denote the wallet location
[oracle@linux-8 db-sample-schemas-main]$ ls -lrt total 28 -rwxrwx---. 1 root vboxsf 21984 Jan 9 21:17 Wallet_ATP01.zip drwxrwx---. 1 root vboxsf 4096 Feb 7 04:00 db-sample-schemas-main [oracle@linux-8 db-sample-schemas-main]$ unzip Wallet_ATP01.zip -d ./Wallet_ATP01 Archive: Wallet_ATP01.zip inflating: ./Wallet_ATP01/ewallet.pem inflating: ./Wallet_ATP01/README inflating: ./Wallet_ATP01/cwallet.sso inflating: ./Wallet_ATP01/tnsnames.ora inflating: ./Wallet_ATP01/truststore.jks inflating: ./Wallet_ATP01/ojdbc.properties inflating: ./Wallet_ATP01/sqlnet.ora inflating: ./Wallet_ATP01/ewallet.p12 inflating: ./Wallet_ATP01/keystore.jks [oracle@linux-8 db-sample-schemas-main]$ cd Wallet_ATP01/ [oracle@linux-8 Wallet_ATP01]$ vi sqlnet.ora [oracle@linux-8 Wallet_ATP01]$ more sqlnet.ora WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="?/network/admin"))) SSL_SERVER_DN_MATCH=yes [oracle@linux-8 Wallet_ATP01]$ vi sqlnet.ora [oracle@linux-8 Wallet_ATP01]$ more sqlnet.ora WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/media/sf_Oracle/software/db-sample-schemas-main/Wallet_ATP01"))) SSL_SERVER_DN_MATCH=yes [oracle@linux-8 Wallet_ATP01]$ export TNS_ADMIN=/media/sf_Oracle/software/db-sample-schemas-main/Wallet_ATP01 [oracle@linux-8 Wallet_ATP01]$ sqlplus admin/xxxxx@atp01_high SQL*Plus: Release 19.0.0.0.0 - Production on Sat Feb 10 05:02:32 2024 Version 19.20.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Last Successful login time: Sat Feb 10 2024 04:11:47 +05:30 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.22.0.1.0 SQL> select instance_name from v$instance; INSTANCE_NAME ---------------- feky1pod2 SQL>I just used atp01_high as my connect identifier as they are already defined in the tnsnames.ora file thats bundled in the zip.
If you already have a working TNS_ADMIN (which defaults to $ORACLE_HOME/network/admin), all you have to do is to only mention the below 2 parameters in the existing sqlnet.ora file and add the connection string for the ADB connection in the tnsnames.ora file.
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/media/sf_Oracle/software/db-sample-schemas-main/Wallet_ATP01")))SSL_SERVER_DN_MATCH=yes
Method 2: Using TLS connection.
Now let's see how to connect to the Autonomous database without the wallet files using a TLS connection.
We can connect to the autonomous database without the wallet files as well. If we wan't to allow TLS connection to connect to Autonomous database, we need to enable Access Control List (ACL). To do that, we can click on edit adjacent to the Disabled status of Access Control List
For our testing purpose, I'm just going to add my ip address to show the ACL and TLS connection.
Just click on Add my IP address and click Save. This will add our IP address to the ACL and the status of the ACL will turn as Enabled.
Now click on Edit adjacent to mTLS: Required
Uncheck the Require mTLS authentication check box to disable mTLS
Now, we have enabled TLS authentication to the Autonomous database but only the connections from my IP is allowed since we have also enabled ACLs.
Now, as we changed the configuration, lets try to connect to the ADB without the wallet files to the database using SQL Developer first and then using SQL Plus.
The configuration will look like the below.
We have now selected Custom JDBC instead of Cloud wallet since we are not using TLS connection for the database.
For the JDBC URL, the format should be
jdbc:oracle:thin:@<connection string for the service intended>
In my example, I'm trying to connect to atp01_high service and hence the JDBC URL will be as below.
jdbc:oracle:thin:@(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=g34efa58d1e9750_atp01_high.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))
Click on Test to see the connection status is Success.
If we need to connect using SQL Plus, we can save this connect string in the tnsnames.ora file and connect directly.
[oracle@linux-8 admin]$ pwd
/oracle/db/db19/network/admin
[oracle@linux-8 admin]$ ls -lrt sqlnet.ora tnsnames.ora
-rw-r-----. 1 oracle oinstall 1629 Feb 7 04:33 tnsnames.ora
-rw-r--r--. 1 oracle oinstall 619 Feb 10 05:16 sqlnet.ora
[oracle@linux-8 admin]$ rm sqlnet.ora
[oracle@linux-8 admin]$ tail tnsnames.ora
rmancat =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = linux-8.selvapc.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rmancat.selvapc.com)
)
)
atp = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=g34efa58d1e9751_atp01_high.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))
[oracle@linux-8 admin]$
[oracle@linux-8 admin]$ tnsping atp
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 13-FEB-2024 10:18:22
Copyright (c) 1997, 2023, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=g34efa58d1e9750_atp01_high.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))
OK (380 msec)
[oracle@linux-8 admin]$
[oracle@linux-8 admin]$ sqlplus admin/xxxxx@atp
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 13 10:18:37 2024
Version 19.20.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Last Successful login time: Mon Feb 12 2024 11:49:25 +05:30
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.1.0
SQL>
In the above demo, I just removed my tnsnames.ora file meaning we don't pass any location for the wallet files which is confirmed by blank o/p for Used parameter files of the tnsping. We are now able to connect to the autonomous database using TLS but only from my specific IP address.
Any connection attempt from other addresses will not be allowed as per the ACL.
Requirements of TLS connection:
SQL Developer - version 18.2 or later
SQL Plus - Oracle instant client/database client 19.14 or later and 21.5 or later (if you need it on Windows)
References:
Happy Connecting...!!!
