The leading edge of scripting languages and Oracle Database brought to you by the Data Access Development team
-
General
September 14, 2020
How to connect to Oracle Autonomous Cloud Databases
To connect your applications to an Oracle Autonomous Cloud Database - any of the Oracle Autonomous Database services like Autonomous Data Warehouse (ADW), Autonomous Transaction Processing (ATP), or the new Oracle Autonomous JSON Database (AJD) - then you will need to download and use a 'wallet'. This gives mutual TLS which provides enhanced security for authentication and encryption.
This post shows how to download a wallet and use it to connect to your Autonomous database from applications in Python, Node.js, PHP, Go, Ruby, and in tools like SQL*Plus. Basically in anything that uses Oracle's C stack "Oracle Call Interface" API.
Pre-requisites
The starting point for this post assumes you've already created a cloud database. But if you don't already have one, then you can quickly get an 'always free' account for no cost and create a database. There are other posts and documentation that show this, such as this blog. In summary, log in to your cloud account and, from the left hand menu, select which of the three kinds of database services suit your workload: ADW, ATP, or AJD. (Choose ATP if you are unsure). Click through the few prompts and enter required information, such as the database name. In my example I've used CJJSON.
During creation the 'Allow secure access from everywhere' option is selected. This gives easy access the database from your applications.
During creation you'll also set a password for the privileged database ADMIN user. (See my earlier blog about how to use ADMIN to create a 'normal' un-privileged user).
Download the Oracle Database wallet files
Downloading the wallet is simple.
-
Navigate to the database console page and select 'DB Connection':
-
Then select 'Download Wallet':
This will prompt for a wallet password. (For Node.js, Python, PHP, Go, Ruby and anything else that internally uses Oracle's "Oracle Call Interface" libraries, this wallet password won't actually be used at runtime).
-
Unzip the downloaded wallet ZIP file.
Finding the Easy Connect String from the tnsnames.ora File
People have been asking me about the "Easy Connect Plus" syntax so I'll show that first. Easy Connect is the simple syntax used in Oracle tools and APIs to identify the machine and database that you want to connect to. You might have used an Easy Connect string like localhost:1521/XEPDB1 for a local database. It identifies the host computer, port number, and the database service name that is running on that computer.
In the tnsnames.ora file extracted from the wallet ZIP, there are several network service names entries. (Whereas the three cloud "database service" options gave you a choice of workload capabilities, these "network services" provide different levels of performance and concurrency for a particular database. The differences between the network services are described here).
The first entry will be like:
cjjson_high = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.ap-sydney-1.oraclecloud.com))(connect_data=(service_name=abc_cjjson_high.adb.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adb.ap-sydney-1.oraclecloud.com,OU=Oracle ADB SYDNEY,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))
The mapping from tnsnames.ora to the Easy Connect Plus string follows the form:
protocol://host:port/service_name?wallet_location=/my/dir&retry_count=N&retry_delay=N
For the cjjson_high service, the equivalent Easy Connect Plus string is:
tcps://adb.ap-sydney-1.oraclecloud.com:1522/abc_cjjson_high.adb.oraclecloud.com?wallet_location=/Users/cjones/Cloud/CJJSON&retry_count=20&retry_delay=3
The only non-obvious parameter is the wallet_location which needs to be set to the directory containing the cwallet.sso file from the wallet ZIP. The other wallet files, including tnsnames.ora, are not needed when you use the Easy Connect Plus syntax.
If you are using this string in a terminal window, you may have to quote or escape some of the special characters.
Optional Extras
For quick testing you can simplify the connection string string to:
tcps://adb.ap-sydney-1.oraclecloud.com:1522/abc_cjjson_high.adb.oraclecloud.com?wallet_location=/Users/cjones/Cloud/CJJSON
If you are behind a firewall, then HTTP options can be used:
tcps://adb.ap-sydney-1.oraclecloud.com:1522/abc_cjjson_high.adb.oraclecloud.com?wallet_location=/Users/cjones/Cloud/CJJSON&https_proxy=proxy.example.com&https_proxy_port=80
Note connecting over HTTP is not recommended for production use due to the overheads.
For reference, the syntax is covered nicely in the technical brief Oracle Database 19c Easy Connect Plus Configurable Database Connection Syntax.
Connecting to an Autonomous Database with an Easy Connect String
To check database connection, I typically use SQL*Plus. Like the languages mentioned at the start, this uses Oracle Call Interface libraries, so the stack is consistent for my purposes. If you don't already have SQL*Plus and the Oracle Client libraries, you can get them from the free Oracle Instant Client.
Remember you need Oracle Client 19c and tools for Easy Connect Plus syntax syntax. If you have an older version then see the next section. However, since Oracle has client-server version interoperability, meaning Oracle Client 19c libraries can connect to Oracle Database 11.2 or later, I'd recommend upgrading your client-side tooling.
-
From the wallet ZIP file put cwallet.sso in a secure directory. You don't need any other files. Unlike some other Oracle wallets, the ADB cloud wallet doesn't contain database username and password credentials but you should still keep it secure.
I put cwallet.sso in the directory /Users/cjones/Cloud/CJJSON
-
Now run SQL*Plus. You will be prompted for the ADMIN password you chose during database creation:
$ sqlplus -l admin@tcps://adb.ap-sydney-1.oraclecloud.com:1522/abc_cjjson_high.adb.oraclecloud.com?wallet_location=/Users/cjones/Cloud/CJJSON SQL*Plus: Release 19.0.0.0.0 - Production on Mon Sep 14 18:18:46 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Enter password: Last Successful login time: Mon Aug 17 2020 18:22:52 +10:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.5.0.0.0 SQL> show user USER is "ADMIN" SQL>
I used a bash terminal window on macOS. In other environments, or when using more options, you may need to quote the string:
$ sqlplus -l admin@'tcps://adb.ap-sydney-1.oraclecloud.com:1522/abc_cjjson_high.adb.oraclecloud.com?wallet_location=/Users/cjones/Cloud/CJJSON&https_proxy=proxy.example.com&https_proxy_port=80' SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 15 18:05:23 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Enter password: Last Successful login time: Tue Sep 15 2020 18:05:09 +10:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.5.0.0.0 SQL>
-
If you are using other languages you would use the connect string in the normal way. For example in Python:
username = "admin" password = os.environ.get("ADMIN_PASSWORD") connect_string = "tcps://adb.ap-sydney-1.oraclecloud.com:1522/abc_cjjson_high.adb.oraclecloud.com?wallet_location=/Users/cjones/Cloud/CJJSON" connection = cx_Oracle.connect(username, password, connect_string) . . .
Connecting to Autonomous Database using pre-19c Oracle Client tools
If you don't want to use the Easy Connect Plus syntax - or your Oracle client libraries are older than Oracle 19c so you can't use the syntax - then there are some extra steps.
-
Download the wallet ZIP file, as shown earlier
-
Extract the cwallet.sso, sqlnet.ora, and tnsnames.ora files. Keep the files in a secure location:
-rw-r--r--@ 1 cjones staff 6725 15 Aug 00:12 cwallet.sso -rw-r--r-- 1 cjones staff 134 15 Aug 10:13 sqlnet.ora -rw-r--r--@ 1 cjones staff 1801 15 Aug 00:12 tnsnames.ora
The other files in the ZIP are not needed for the tools I mentioned at the start. They would be used, for example, if you were going to connect via JDBC.
-
There are now two options:
-
Move the three files to the network/admin directory of the client libraries used by your application. For example if you are using Instant Client 18c, you would put them in /home/cjones/instantclient_18_5/network/admin/.
-
Alternatively, move them to any secure, accessible directory, for example /Users/cjones/Cloud/CJJSON.
Now here's the important step: Edit sqlnet.ora and change the wallet location directory to the directory containing the cwallet.sso file:
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/Users/cjones/Cloud/CJJSON"))) SSL_SERVER_DN_MATCH=yes
Then you need to indicate to tools where the tnsnames.ora and sqlnet.ora files are, for example by setting the environment variable TNS_ADMIN to /Users/cjones/Cloud/CJJSON. Alternatively some APIs like cx_Oracle 8 let you pass the directory at runtime using functions like init_oracle_client(). Neither of these are needed, and you don't need to edit sqlnet.ora, if you have put all the files in the network/admin directory.
-
-
Now you can run SQL*Plus or other tools using one of the network service names in the tnsnames.ora file:
$ sqlplus -l admin@cjjson_high