Database Authentication with a Smart Card – Part 1 (Wallet)

I hate changing passwords every so many days. Seems like I no sooner get all of my passwords reset than it is time to start changing them again. Working in a government environment, one of those things that I tried to figure out for years was how to log on to a database using my Common Access Card (CAC) hardware token instead of a username and password. Imagine all the time saved by not stressing over the next set of expiration dates! I tried everything: tricks to get the Oracle Wallet to see the card reader library, RADIUS authentication, Kerberos and other Enterprise User Authentication setups. None of it worked, until I happened to be at an Oracle “Technology Day” and posed the question to an Oracle pre-sales engineer.

“Sure,” he said, “I know how to do that.” As if it was no big thing. I had been asking other DBAs for years, and none of us had ever figured it out, so I was skeptical to say the least. He very nicely offered to send me some documentation that described the process. What I got was a rough and ready set of basic instructions that described something I had already seen and dismissed several times before: SSL/PKI authentication using Oracle Wallet. After reading it through, however, I realized that there was one important difference between what I had tried before and what was contained in the instructions now before me. I tried configuring my client and server as described and “Eureka!” it worked!

In this series of posts I will describe in greater detail just how this configuration actually comes together, with step by step instructions. It is important to note that pretty much everything I am going to describe can be derived from Oracle’s freely available documentation – there isn’t any great trade secret or intellectual property concern here.

It is also important to note that because this solution does involve the use of the Oracle Wallet, it may not be for everyone. Please consider the following before starting any configuration that I lay out here:

• SSL/TLS communications must run on a separate network port from “normal” database connections, which may affect requirements for firewall exceptions.
• SSL/TLS connections can take a bit (sometimes a lot) longer to establish than connections with native Oracle encryption or without any encryption, as the key exchange process introduces additional overhead.
• When storing wallet data in the MCS, it is not possible to store username and password credentials – or any other information other than certificates – in the wallet.

One final warning: these instructions are provided “as is” without guarantee of any kind. Everything suggested here should be validated in your environment – preferably in a test environment – before configuring in production. Any errors or difficulties you encounter should be pursued through Oracle Support.

Assuming that these are not issues for you, please continue reading!

The configuration I will describe has been tested on Windows 10 and Windows Server 2012R2 clients, and Red Hat/Oracle Linux 6 and 7 servers with Oracle 11.2, 12.1, and 12.2 Enterprise Edition. Oracle OCI Client 12.1.0.2 (64-bit) and Instant Client 12.2 were tested successfully with common client applications such as SQL Developer, TOAD, and SQL*Plus. All wallet related commands in my examples are given using the orapki utility, as the Oracle Wallet Manager is often disabled by group policy object on government systems.

The first step in this process is to configure the Oracle Wallet on the database server.

1. Create a directory to put the server’s wallet in if one does not already exist. The remainder of the steps in this instruction will assume that all orapki commands are made from the wallet directory.

   # mkdir /u01/oracle/wallet
   # cd /u01/oracle/wallet

2. Use orapki to create the wallet and give it a strong password.

   # orapki create wallet -wallet . -auto_login -pwd [password]

3. Create the certificate signing request for your server. Use the fully qualified domain name of the server (e.g. myhost.mycompany.com). Be sure to include any additional LDAP parameters such as OU, O, or C required by your certification authority (CA) in the distinguished name of the certificate request, or the final certificate created by CA may not match the request and you will not be able to load the certificate in to your wallet.

   # orapki wallet add -wallet . -dn "CN=[hostname],OU=USAF,OU=PKI,OU=DoD,O=U.S. Government,C=US" -keysize 2048 -pwd [password]

4. Export the certificate signing request so that you can submit the request to your Certificate Authority to generate the unique server certificate and certificate trust chain.

   # orapki wallet export -wallet . -dn "CN=[hostname],OU=USAF,OU=PKI,OU=DoD,O=U.S. Government,C=US" -request CertRequest.cert

5. Submit the certificate request to the Certificate Authority.
6. Download the current Root Certificates from your certificate authority, and load them in to your server wallet to establish the necessary trust chain for your server certificate and all client certificates. Individual root certificates can be loaded in to your wallet with orapki.

   # orapki wallet add -wallet . -trusted_cert -cert [path_to_cert_file] -pwd [password]

7. When the signed server certificate is received from your certificate authority, save the Base64 version of the certificate as a User certificate in the Oracle Wallet on the server.

   # orapki wallet add -wallet . -user_cert -cert [path_to_base64_cert_file] -pwd [password]

This completes the setup for the server wallet. My next post will deal with configuration of sqlnet.ora and listener.ora on the server.