You can connect to an Oracle Autonomous Database in different ways. This is well documented here. It’s a bit different from what we know from on-premises environments. In this blog post, I show the steps to connect to an Autonomous Database from a third-party IDE like DataGrip.
From a JDBC perspective, this is just an ordinary JDBC URL with some driver-specific properties. Therefore, this approach should work for any JDBC-based IDE and also for any Java application.
Step 1 – Download the Wallet
Go to your Autonomous Database and click on the “DB Connection” button. A screen similar to the following appears:
Press on the “Download Wallet” button and enter a password. This password is used to protect the key and the trust store. We will need it later. I named my instance “ATP21”. Therefore, in my case, a zip archive named “Wallet_ATP21.zip” was downloaded.
Step 2 – Unzip the Wallet
Unzip the downloaded zip file and move it to a location where want to keep it. The wallet contains the following files:
- README
- ewallet.p12
- ojdbc.properties
- tnsnames.ora
- cwallet.sso
- keystore.jks
- sqlnet.ora
- truststore.jks
Open the file “tnsnames.ora” in a text editor. It contains 5 entries. We need one of those entries to build the JDBC connection string. I highlighted the relevant part of the first entry in the next screenshot:
Step 3a – Configure Connection in DataGrip (Legacy Driver)
Add a new connection in DataGrip and select “Oracle” as shown in the following screenshot:
In the “General” tab change the Connection type to “URL only”. Enter the user, the password and the complete JDBC URL as shown in the next screenshot:
The URL starts with jdbc.oracle:thin:@. The rest is the text I’ve highlighted in the tnsnames.ora file above.
Then click on the “Advanced” tab and define the following properties:
- javax.net.ssl.trustStore
- javax.net.ssl.trustStorePassword
- javax.net.ssl.keyStore
- javax.net.ssl.keyStorePassword
Here are my settings (of course you need to amend the values to match the environment of your wallet):
Step 3b – Configure Connection in DataGrip (Current Driver)
DataGrip automatically downloads the latest Oracle Database JDBC driver. In my case version 21.1.0.0. Since version 18.3 there is an easier way to connect. The JDBC driver can access the wallet directory and its files. As a result, you do not need to configure the java.net.ssl.* JDBC properties anymore. You just have to define one additional JDBC property “TNS_ADMIN” to define the path to the wallet directory.
And of course, you can pass this JDBC property directly in the JDBC URL as shown in the next screenshot:
Conclusion
Establishing a connection to an Autonomous Database requires a wallet. The JDBC driver needs access to this wallet. This doesn’t make things easier, but it doesn’t make them overly complicated either.
However, you need to deal with this additional resource regularly because the wallet has a limited lifetime. This is documented in the README file.
Wallet Expiry Date
-----------------------
This wallet was downloaded on 2021-02-28 08:16:36.267 UTC.
The SSL certificates provided in this wallet will expire on 2023-03-19 21:43:22.0 UTC.
In order to avoid any service interruptions due to an expired SSL certificate, you must re-download the wallet before this date.So I have to update my wallet in two years otherwise I won’t be able to connect anymore.