Using RxOdbcData with Oracle Wallet

  • Article

In Microsoft R Server you can use RxOdbcData class to connect to an ODBC DataSource, for example a table in an Oracle database. At times some of our customer's have a requirement to hide the credentials for their Oracle database (username and password) in the connection string they specify when writing an R script in Microsoft R Server. Oracle's driver does not support saving the password when creating an ODBC DSN in Windows. In this blog we describe a way to achieve this requirement using Oracle's Wallet.

Prerequisites

  1. Oracle client must be installed and the client's bin folder must be added to PATH environment variable.
  2. Recommend using separate sqlnet.ora and tnsnames.ora files so as to separate regular connections from wallet connection. Normally you can find these files under $ORACLE_HOME/network/admin folder or something similar.
  3. TNS_ADMIN environment variable should point to the approproate folder with sqlnet.ora and tnsnames.ora.

Setting up wallet

1. Create a directory for a wallet and use Oracle's mkstore command to create a wallet in that directory:

mkdir -p C:\Oracle\admin\mywallet

mkstore -wrl C:\Oracle\admin\mywallet -create

For a comprehensive description of Oracle wallet see here.

2. Add the following lines to sqlnet.ora file (this will disallow other types of authentication, which is why recommendation is to separate it from the usual sqlnet.ora and point TNS_ADMIN to this only for running of R scripts purposes).

SQLNET.WALLET_OVERRIDE = TRUE WALLET_LOCATION=( SOURCE=(METHOD=FILE) (METHOD_DATA=(DIRECTORY=C:\Oracle\admin\mywallet)) )

3. Add the username and password to the wallet we created above:

mkstore -wrl C:\oracle\admin\mywallet -createCredential <TNS alias> <myuser> <mypassword>

(TNS alias is the alias from tnsnames.ora file)

Now we can test the wallet using sqlplus:

sqlplus /@<TNS alias>

This should log you in on sqlplus/SQL prompt without asking for username or password.

4. After you have setup the wallet you can connect to it from R script by specifying the TNS alias in the connection string along with driver for Oracle. Note that we specify the DBQ and Driver parameters in the connection string instead of usual DSN and Provider. Here is an example that tests it:

sConnectionStr <- "Driver={Oracle in OraClient12Home1};DBQ=<TNS alias>;" checkDBversion <- RxOdbcData(sqlQuery="SELECT BANNER FROM V$VERSION", connectionString = sConnectionStr, rowBuffering=FALSE) rxGetInfo(checkDBversion, numRows = 1)

OUTPUT:

Connection string: Driver={Oracle in OraClient12Home1};DBQ=<TNS alias>; Data Source: ODBC Data (1 row starting with row 1): BANNER 1 Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

References:

Oracle Wallet Reference: https://docs.oracle.com/middleware/1213/wls/JDBCA/oraclewallet.htm#JDBCA599

RxOdbcData Reference: https://msdn.microsoft.com/en-us/microsoft-r/scaler/packagehelp/rxodbcdata