Using Wallet Authentication with SQLcl

Ever since SQLcl came out I was waiting for it to support wallet authentication.
Today the waiting is over and all you need to do is grab the new SQLcl release from the SQL Developer download page. (Hint: The download link is at the bottom of the page.)
Now that you have the latest release let's prepare your client machine to use credentials stored in a wallet.

Preparing the for wallet authentication

First of all you need an Oracle client installed as wallet authentication is not possible using the thin driver. Make sure to have your client home on 12.1.0.2.x otherwise SQLcl can't make use of it.

Then update your sqlnet.ora file by adding following lines:

WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = D:\ora_wallet)
     )
   )

SQLNET.WALLET_OVERRIDE = TRUE

Make sure to update the line where the directory is set to match the location where you want to store your wallet. The last line allows the wallet to override any existing OS authentication setting.

Now modify your tnsnames.ora and add another entry which points to the database you want to connect to later. Give it a recognizable name because you will need it again when saving the credentials in the wallet. I will use "wallet_test" as the tnsname in this post.
If you use different users on a single database you have to add one tns entry for each user. The reason is that there is a one to one relationship between a credential and a tns entry.

Next step is to create the directory where your wallet should be stored.
Afterwards create the wallet by using the orapki utility from the Oracle Client:

orapki wallet create -wallet "D:\ora_wallet" -pwd "test_1234" -auto_login_local

Now store the credentials in the wallet by using the mkstore tool with following syntax:

mkstore -wrl "D:\ora_wallet" -createCredential tnsname username password

You can also emit the password and then you'll be asked for it by the tool.
Enter the wallet password and wait for mkstore to exit.

Finally test your setup by using good old SQL*Plus:

sqlplus /@wallet_test

Using the wallet with SQLcl

SQLcl is a Java application and therefore uses jdbc to connect to the database.
The latest release added support for custom jdbc connect strings and that is what we actually need.
Connecting with SQLcl using the above created credential looks like this:

sql /@jdbc:oracle:oci8:@wallet_test

The syntax might look a bit strange compared to the SQL*Plus one but you need to put the "/@" before the custom jdbc connection string and still prefix the tnsname with an "@".

Enjoy connecting to your database without specifying a password on the command line.
The main use case will most probably be for batch scripts, but I think switching to wallet authentication as much as possible also helps when you have to work in public places.

Thanks to the SQLcl team for adding another great feature.

P.S.: @Kris, the next blog post is on you. ;-)