Configure Two-Factor Authentication Using PKI for Oracle Databases

While creating strong passwords and changing them regularly serves as a preferred security practice, configuring your database users to authenticate via two-factor authentication is a giant leap forward in improving your overall database security strategy.

Instead of taking a chance that the password for a user could be stolen or cracked, two-factor authentication uses something that a person has (e.g.: access card, token) and something that a person knows (e.g.: password, PIN). Two-factor authentication supports the TCPS protocol, providing the added benefit of encrypting the SQLNet traffic between the client and the database.

The below steps walk you through both the server and the client side configuration items for setting up two-factor authentication using Public Key Infrastructure (PKI). In this example, we assume all end users have an access card that contains unique certificates and the Certificate Authority (CA) is the same for the server and users.

Please note, this example also takes advantage of the Microsoft Certificate Store (MCS) on the client machines.

Part A: Prerequisites

Before getting started, you’ll need to check the minimum required version of the Oracle database software and client. Once that’s complete, you’ll need to make some initialization parameter and sqlnet.ora setting updates. Below are the prerequisites:

1. Oracle Database software is 12.1 or above.
2. Oracle Client 12.1 or above on all workstations.
3. Listener has a TCPS port enabled and that port is open (For this example, we will use port 1522). Update the local_listener parameter (Parameter is Node-Dependent for Oracle RAC configurations). Please note, you’ll want to use the IP Address of the database host for a stand-alone database and the VIP for Oracle RAC.

alter system set local_listener=’(ADDRESS_LIST=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER1))(ADDRESS=(PROTOCOL=TCP)(HOST=12.34.467.88)(PORT=1521))(ADDRESS=(PROTOCOL=TCPS)(HOST=12.34.467.88)(PORT=1522)))’ sid='inst1' scope=both; alter system register;

4. Set the parameters common_user_prefix, os_authent_prefix and remote_os_authent to NULL in the database.

alter system set common_user_prefix='' scope=spfile;
alter system set os_authent_prefix='' scope=spfile;
alter system set remote_os_authent=FALSE scope=spfile;

5. Update server sqlnet.ora settings:

WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA = (DIRECTORY = /u01/app/oracle/pki/server_wallet)     )
  )  
SQLNET.AUTHENTICATION_SERVICES= (TCPS,IPC,BEQ) SSL_CLIENT_AUTHENTICATION = TRUE SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_GCM_SHA384,SSL_RSA_WITH_AES_128_CBC_SHA256,SSL_RSA_WITH_AES_256_CBC_SHA256,SSL_RSA_WITH_AES_128_CBC_SHA256,SSL_RSA_WITH_AES_128_CBC_SHA256,SSL_RSA_WITH_AES_128_CBC_SHA,SSL_RSA_WITH_3DES_EDE_CBC_SHA,SSL_RSA_WITH_RC4_128_SHA,SSL_RSA_WITH_RC4_128_MD5) SSL_VERSION = 1.2 or 1.1 
sqlnet.crypto_checksum_client = ACCEPTED sqlnet.crypto_checksum_server = ACCEPTED

Note: The Wallet_location parameter will be the location of where the wallet is created in Part B.

Part B: Server Wallet/Certificate Configuration

Create the Server Wallet and Certificate Signing Request (CSR). In this example we are using this password: Welcome123.

mkdir /u01/app/oracle/pki/server_wallet
cd /u01/app/oracle/pki/server_wallet
orapki wallet create -wallet /u01/app/oracle/pki/server_wallet -auto_login -pwd Welcome123
orapki wallet add -wallet /u01/app/oracle/pki/server_wallet -dn "CN=oracle12c.aem.com" -keysize 2048 -sign_alg sha256 -pwd Welcome123

1. Export the CSR into a file.

orapki wallet export -wallet /u01/app/oracle/pki/server_wallet -dn "CN=oracle12c.aem.com" -request devserver.txt

2. If the Certificate Authority (CA) is unable to process a CSR with MD5 algorithm then the below commands need to be run (Oracle MOS Note 1448161.1).

openssl pkcs12 -in ewallet.p12 -nodes -out nonoracle_wallet.pem
openssl req -new -key nonoracle_wallet.pem -out devserver.csr

3. Submit the CSR to the CA for them to generate a certificate.
4. Once the CA provides the server certificate, upload the certificate chain to the wallet.

orapki wallet add -wallet /u01/app/oracle/pki/server_wallet -trusted_cert -cert /u01/app/oracle/pki/server_wallet/ca_cert.crt -pwd Welcome123
orapki wallet add -wallet /u01/app/oracle/pki/server_wallet -trusted_cert -cert /u01/app/oracle/pki/server_wallet/chain.crt -pwd Welcome123
orapki wallet add -wallet /u01/app/oracle/pki/server_wallet -user_cert -cert /u01/app/oracle/pki/server_wallet/devserver.crt -pwd Welcome123

5. The completed server wallet should now look like this:

orapki wallet display -wallet .
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
Requested Certificates:
User Certificates:
Subject:        CN=oracle12c.aem.com,O=Default Company Ltd,L=Default City,C=XX
Trusted Certificates:
Subject:        CN=Intermediate CA,OU=Certificate Authority,ST=Virginia,C=US
Subject:        CN=Root CA,OU=Certificate Authority,ST=Virginia,C=US

Part C: User Configuration/Setup

1. Update the client side sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES=(TCPS,BEQ)
NAMES.DIRECTORY_PATH= (TNSNAMES)
SSL_CLIENT_AUTHENTICATION = TRUE
SSL_VERSION = 1.1
SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_GCM_SHA384,SSL_RSA_WITH_AES_128_CBC_SHA256,SSL_RSA_WITH_AES_256_CBC_SHA256,SSL_RSA_WITH_AES_128_CBC_SHA256,SSL_RSA_WITH_AES_128_CBC_SHA256,SSL_RSA_WITH_AES_128_CBC_SHA,SSL_RSA_WITH_3DES_EDE_CBC_SHA,SSL_RSA_WITH_RC4_128_SHA,SSL_RSA_WITH_RC4_128_MD5)
WALLET_LOCATION = (SOURCE = (METHOD=MCS))

2. Update the client tnsnames.ora file to include an entry for the TCPS connection.

testdb_TCPS=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCPS)
      (HOST=oracle12c.aem.com)
      (PORT=1522)
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=testdb.aem.com)
    )
  )

3. Update the database user account to be identified by the certificate (this will be different for every user based on Distinguished Name of their certificate).

alter user ggarrison identified externally as 'CN=lastname.firstname.middlename,OU=PKI,C=US';

Part D: For Oracle RAC Setup Only

1. Follow Section A steps 1-3 to make sure a TCPS listener is set up as well as the correct database parameters implemented.

2. Create a shared ACFS mount point that will house the server wallet (Click here for Oracle 12.1 instructions).

3. Create a shared server wallet, this will contain the user certificates for all the nodes in the cluster (Follow Part B, Step 1 above). The final product should look similar to the below:

[oracle@dm02dbadm02 server_wallet]$ orapki wallet display -wallet .
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
Requested Certificates:
User Certificates:
Subject:        CN=node1
Subject:        CN=node2
Trusted Certificates:
Subject:        CN=Intermediate CA,OU=Certificate Authority,ST=Virginia,C=US
Subject:        CN=Root CA,OU=Certificate Authority,ST=Virginia,C=US

Part E: For Oracle RAC or Stand-Alone

1. Update the listener.ora at $GRID_HOME/network/admin (WALLET_LOCATION parameter is the location of the server wallet. In the examples below, we are using the shared server wallet location for Oracle RAC.)

WALLET_LOCATION =
  (SOURCE =
   (METHOD = FILE)
    (METHOD_DATA =
     (DIRECTORY = /acfs/oracle/pki_wallet/server_wallet)
    )
  )

2. Update sqlnet.ora at $GRID_HOME/network/admin

WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /acfs/oracle/pki_wallet/server_wallet)
    )
  )

SQLNET.ENCRYPTION_TYPES_SERVER = (AES256,AES192)
SQLNET.ENCRYPTION_TYPES_CLIENT = (AES256,AES192)
SQLNET.CRYPTO_CHECKSUM_CLIENT = ACCEPTED
SQLNET.CRYPTO_CHECKSUM_SERVER = ACCEPTED
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA256,SHA1,SHA384)
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (SHA256,SHA1,SHA384)
###############  SSL  ########################
SQLNET.AUTHENTICATION_SERVICES= (TCPS, BEQ, IPC)
SSL_CLIENT_AUTHENTICATION = TRUE
SSL_CIPHER_SUITES = (SSL_RSA_WITH_3DES_EDE_CBC_SHA,SSL_RSA_WITH_AES_256_CBC_SHA,SSL_RSA_WITH_AES_128_CBC_SHA,SSL_RSA_WITH_3DES_EDE_CBC_SHA)
SSL_VERSION = 1.2 or 1.1

3. Update sqlnet.ora at $ORACLE_HOME/network/admin

WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /acfs/oracle/pki_wallet/server_wallet)
    )
  )

SQLNET.ENCRYPTION_TYPES_SERVER = (AES256,AES192)
SQLNET.ENCRYPTION_TYPES_CLIENT = (AES256,AES192)
SQLNET.CRYPTO_CHECKSUM_CLIENT = ACCEPTED
SQLNET.CRYPTO_CHECKSUM_SERVER = ACCEPTED
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA256,SHA1,SHA384)
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (SHA256,SHA1,SHA384)
###############  SSL  ########################
SQLNET.AUTHENTICATION_SERVICES= (TCPS, BEQ, IPC)
SSL_CLIENT_AUTHENTICATION = TRUE
SSL_CIPHER_SUITES = (SSL_RSA_WITH_3DES_EDE_CBC_SHA,SSL_RSA_WITH_AES_256_CBC_SHA,SSL_RSA_WITH_AES_128_CBC_SHA,SSL_RSA_WITH_3DES_EDE_CBC_SHA)
SSL_VERSION = 1.2 or 1.1

4. Restart the database(s) and all listeners so that the new settings take effect.

5. Connect via sqlplus using the TCPS tnsnames entry.

6. Toad: the username should be EXTERNAL and the password field is left blank.

7. SQL Developer: Requires version 17 of SQL Developer and perform the following steps:

a. Select Tools-> Preferences-> Database-> Advanced
b. Check – ‘Use Oracle Client’ and ‘Use OCI/Thick Driver’
c. Set Oracle Homes and TNS Names Directory
d. Create New Connection (Select the TCPS TNS Names Entry)
e. Select OS Authentication
f. Once connected, run the below queries to verify the connection.

Select user from dual;
SELECT SYS_CONTEXT ('USERENV', 'AUTHENTICATED_IDENTITY') FROM DUAL;
SELECT SYS_CONTEXT ('USERENV', 'AUTHENTICATION_METHOD') FROM DUAL; 
SELECT SYS_CONTEXT ('USERENV', 'NETWORK_PROTOCOL') FROM DUAL;

Part F: Troubleshooting

Update the client sqlnet.ora with below settings to run tracing. This will provide more detailed error messages in working with Oracle Support and/or researching the root cause of the problem.

TNSPING.TRACE_LEVEL = SUPPORT
TNSPING.TRACE_DIRECTORY = C:\Oracle\Oracle12R2\product\12.2.0\client_1\network\admin\trace
trace_unique_client = on
trace_timestamp_client = on
TRACE_LEVEL_CLIENT= 16
TRACE_DIRECTORY_CLIENT= C:\Oracle\Oracle12R2\product\12.2.0\client_1\network\admin\trace
TRACE_FILE_CLIENT= ssl_client
TRACE_LEVEL_SERVER= 16
TRACE_DIRECTORY_SERVER= C:\Oracle\Oracle12R2\product\12.2.0\client_1\network\admin\trace
TRACE_FILE_SERVER= ssl_srvr
SELECT SYS_CONTE

That’s it! Not only does this configuration greatly improve your security posture, but by using external authentication, there is no password that the DBA must continually reset— a true win-win.

Hopefully this post helps simplify what may otherwise seem like a daunting task. As we all know, technology is always evolving and the same holds true for how we secure our systems. AEM can help your organization implement this and other best practices to help keep your systems secure and optimized so please reach out to learn more about our service offerings.

Meet Greg Garrison