Secure External Password Store for RMAN Backup

There are many instances where DBAs, developers or an application team would like to setup jobs where they want to hide or encrypt the password. This is to ensure that no one sees the password so they can safely schedule jobs that connect to the database and run. To achieve this object, an Oracle DBA would choose secure external password store also known as SEPS.

 
I am going to walk you through an example use case in this blog: a DBA trying to encrypt catalog credentials for RMAN backup. The prerequisites needed are as follows:

• Setup a sqlnet.ora file
• Setup a TNS alias in tnsnames.ora for the instance you would like to connect
• Create wallet

Implementation

When TDE is in use, DBA must create two wallets, one for TDE (a) and other for SEPS (b).

a) ENCRYPTION_WALLET_LOCATION
b) WALLET_LOCATION

1. Setup the configuration files at [$ORACLE_HOME/network/admin]

a) ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=$ORACLE_BASE/admin/$ORACLE_SID/wallet)))
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=$ORACLE_BASE/admin/$ORACLE_SID/wallet_$ORACLE_SID)))
SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSESSL_VERSION = 0

 
b) Setup TNS Alias for the target database in TNSNAMES.ORA file, in my case TNS alias name is TESTDB.

Note: If DBA need to use any additional parameter as following in sqlnet.ora file, then we need to create another sqlnet.ora file at non-default location and use this encrypted credential by setting the TNS_ADMIN variable pointing to non-default location of sqlnet.ora.

SQLNET.CRYPTO_CHECKSUM_SERVER=REQUIRED
SQLNET.ENCRYPTION_SERVER=REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPE_SERVER=(SHA256,SHA384,SHA512)
SQLNET.ENCRYPTION_TYPES_SERVER=(AES256)
SQLNET.CRYPTO_CHECKSUM_CLIENT=REQUIRED
SQLNET.ENCRYPTION_CLIENT=REQUIRED
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

Here is how we can setup a different sqlnet.ora file at different location other than default location [$ORACLE_HOME/network/admin].

In this case WALLET_LOCATION & sqlnet.ora file created at /u01/oracle/admin/testdb/wallet_testdb directory.

$mkdir -p /u01/oracle/admin/testdb/wallet_testdb
$export TNS_ADMIN=/u01/oracle/admin/testdb/wallet_testdb
$cd $TNS_ADMIN
$vi sqlnet.ora and save it. Create this sqlnet.ora with following info.

WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=$ORACLE_BASE/admin/$ORACLE_SID/wallet_$ORACLE_SID)))
SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSESSL_VERSION = 0

$vi tnsnames.ora and save it. Create the tnsnames.ora with following info to point to actual tnsnames.ora file sitting at default location [$ORACLE_HOME/network/admin]

ifile=$ORACLE_HOME/network/admin/tnsnames.ora

2. Create wallet & Add credential (we can add as many credentials as many we want).

a) Create wallet

$mkstore -wrl /u01/oracle/admin/testdb/wallet_testdb create. It would ask for password

 
b) Add credential to wallet

Syntax: mkstore -wrl <wallet location> -createCredential <TNS Alias>  <userid> <pass>

$mkstore -wrl /u01/oracle/admin/testdb/wallet_testdb -createCredential testdb rmanbkp

Oracle Secret Store Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
Your secret/Password is missing in the command line
Enter your secret/Password:
Re-enter your secret/Password:
Enter wallet password:
$

Note: If you don’t give the password for database user ID at command prompt, then first it would ask for secret / password (DB User’s password) two times and then finally wallet password.

 
c) Validate the wallet files, sqlnet.ora & tnsnames.ora files

$cd /u01/oracle/admin/testdb/wallet_testdb
$ls -lrt
total 11
-rw-------   1 oracle   oracle         0 Jan 28 14:17 ewallet.p12.lck
-rw-------   1 oracle   oracle         0 Jan 28 14:17 cwallet.sso.lck
-rw-r-----   1 oracle   oracle        30 Jan 28 14:22 tnsnames.ora
-rw-------   1 oracle   oracle       536 Jan 28 14:24 ewallet.p12
-rw-------   1 oracle   oracle       581 Jan 28 14:24 cwallet.sso
-rw-r-----   1 oracle   oracle       520 Jan 28 14:25 sqlnet.ora

 
d) List the credentials just created.

$mkstore -wrl  /u01/oracle/admin/testdb/wallet_testdb -listCredential
Oracle Secret Store Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
List credential (index: connect_string username)
1: TESTDB rmanbkp

$ export TNS_ADMIN=/u01/oracle/admin/testdb/wallet_testdb
$cd $TNS_ADMIN
$ mkstore -wrl . -list
Oracle Secret Store Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
Oracle Secret Store entries:
oracle.security.client.connect_string1
oracle.security.client.password1
oracle.security.client.username1
ORACLE.SECURITY.DB.ENCRYPTION.AWDRH82BZ0/Jvwi+9l0tau8AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
ORACLE.SECURITY.ID.ENCRYPTION.
ORACLE.SECURITY.KB.ENCRYPTION.

 
Test the connection to database using encrypted credential:

$echo $TNS_ADMIN
/u01/oracle/admin/testdb/wallet_testdb
$ sqlplus /@TESTDB

RMANBKP@TESTDB> show user
USER is "RMANBKP"
RMANBKP@TESTDB>

 
How to delete credential:

$ mkstore -wrl /u01/oracle/admin/testdb/wallet_testdb -deleteCredential TESTDB
Oracle Secret Store Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:

Troubleshooting Scenario

Possible case 1 for troubleshooting : – ORA-01017: invalid username/password; logon denied

$ sqlplus /nolog
SQL*Plus: Release 12.2.0.1.0 Production on Mon Jan 27 08:41:00 2020
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
@> connect /@TESTDB
ERROR:

 
ORA-01017: invalid username/password; logon denied

Fix: Tried without following two parameters in sqlnet.ora , add this entry in sqlnet.ora file and try again.

SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSESSL_VERSION = 0

And set the TNS_ADMIN $export TNS_ADMIN=<to the sqlnet.ora which contains SEPS wallet location>

 
Possible case 2 for troubleshooting: Got an error wallet open failed

$ sqlplus /nolog
SQL*Plus: Release 12.2.0.1.0 Production on Mon Jan 27 08:39:19 2020
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
@> connect /@MDITDMIR
ERROR:

 
ORA-12578: TNS: wallet open failed

Fix:  Using the same sqlnet.ora file which has TDE wallets and additional parameter.

$export TNS_ADMIN=<to the sqlnet.ora which contains SEPS wallet location>

Create separate sqlnet.ora file with only required parameter for SEPS and use WALLET_LOCATION parameter to define the wallet location.

WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=$ORACLE_BASE/admin/$ORACLE_SID/wallet_$ORACLE_SID)))

SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSESSL_VERSION = 0

And there you have it! I hope this blog helps you learn a bit more about SEPS with an example use case. If you’re needing support with your Oracle databases, please get in touch with us. You can also find all of our Oracle resources here.