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.
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.
TNS_ADMIN
environment variable should point to the approproate folder with
sqlnet.ora
and
tnsnames.ora
.
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 .
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))
)
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.
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)
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.