Hi Raff,
Based upon the reply from Actual Tech below, you should definitely remove the
XML file name from the Setup File entry in the driver configuration.
I did not see an indication in your communication with them below that you
specifically mentioned the need for Kerberos based authentication, which would
be a critical omission in the interaction with them, if so. :-)
>From a search this morning, if my read is correct, it would seem that the
>Kerberos authentication needs to be supported in the ODBC driver, so that the
>authentication process is transparent to the calling program (eg. R/RODBC).
>Looking through some sites that I found, there are options in the DSN
>configuration in ODBC Manager that support Oracle Advanced Security. You may
>need to confirm the correct settings with your SysAdmin/DBAdmin for Kerberos
>on your Oracle server.
1. Go into ODBC Manager and select the DSN configuration for your Oracle
server.
2. On the initial page ("Introduction"), select Continue.
3. That will bring you to a page where you "Enter the data source name". Be
sure that information is correct and then press Continue.
4. On the next page ("Select the database"), select "Advanced Options". That
will bring up a dialog window where you can select "Use Oracle Advanced
Security". Once you select that, you can then enter the additional parameters
for Encryption and Checksum. Once you do that with the correct parameters,
press OK.
5. That should then bring you to a "Conclusion" window. On the bottom of that
window will be a Test button, which you should press. You should then be
prompted for username and password information. Then press OK, which should
then bring up a test results window with information on success/failure. If
successful, then press OK and you will be returned to the Conclusion window and
you are done.
Since I don't have Kerberos on my Oracle server here, I cannot actually test
all of this. Based upon some of those same sources I found, I believe that you
can leave the 'uid' and 'pwd' arguments to odbcConnect() blank, as the ODBC DSN
configuration information for the 'connection' argument should automatically
pass that information to Oracle, rather than needing to pass it in the
connection string. You will need to test that hypothesis of course.
Hopefully that gets you moving forward.
Regards,
Marc
P.S. "Mr." :-)
On Nov 30, 2012, at 6:57 PM, Raffaello Vardavas <r_varda...@hotmail.com> wrote:
> Dear R-SIG-DB users ,
>
> I am using a Mac OS X 10.6.8 . I have downloaded Oracle and imported an xml
> file that allows it to remotely connect to a database. The xml file contains
> the login details (username, password and the encryption used which is
> Kerberos authentication). I can do all my sql query searches from Oracle.
> However - I would like to be able to access this info - do sql queries and
> convert these to dataframes from R. I have so far been unsuccessful in this
> task.
>
> As can be seen from the correspondence below, Dr./Mr. Marc Schwartz has been
> very helpful in providing suggestions and ways forward in this. In particular
> he pointed me to the software at
> http://www.actualtech.com/product_oracle.php
> I download their ODBC Manager. In the set up configuration I enter the
> following:
> Driver Name: Actual Oracle
> Driver File: /Library/ODBC/Actual Oracle.bundle/Contents/MacOS/atoradb.so
> Setup File: /Users/rvardava/Documents/Projects_2012/OCIE/ism.xml
> Notice that the setup file points to the xml file that contains the userid
> and password to access the remote database. I however have also recently
> tried leaving the setup file entry blank.
> I then go to System DNS tab in the ODBC Manager and configure the DNS
> connection - naming it OCIE. In the set up I enter the server name and port
> that are included in the XML file.
> Then when I go to R I do the following:
> library(RODBC)
> aconn<-odbcConnect("OCIE",uid="eusip",pwd="PASS")
> Where PASS is the actual password that is contained in the xml file. When I
> run this in R - I get the following error:
>
> Exception in thread "Thread-4" java.lang.NullPointerException
> at ATVersion.getDatabaseProductVersion(ATVersion.java:978)
> Warning messages:
> 1: In
> odbcDriverConnect("DSN=OCIE;UID=eusip;PWD=051A46D71C9701D8FB5766C61317DB72E67B108AC0C33ECB2F")
> :
> [RODBC] ERROR: state HY000, code 100, message [Actual][Oracle] Could not
> connect to the database. Verify the database name and type (SID or service).
> (could not get message)
> 2: In
> odbcDriverConnect("DSN=OCIE;UID=eusip;PWD=051A46D71C9701D8FB5766C61317DB72E67B108AC0C33ECB2F")
> :
> ODBC connection failed
>
> Please help I'm a little lost in next steps to take.
>
> Thank you Raff.
>
>
>
>
>
> Hi Raff,
>
> Our driver does not support the XML file you mention in your e-mail. You
> should not modify the "Setup File" parameter for the driver configuration
> (the parameter should be empty).
>
> The driver only uses the connection information provided for your DSN (the
> driver launches a wizard for prompting you for all connection information if
> the "Setup File" parameter is empty).
>
> Please let me know if you have any questions.
>
> Best regards,
>
> Jonathan Monroe
> Actual Technologies - ODBC for Mac OS X
> supp...@actualtech.com
>
>
> On Nov 26, 2012, at 4:53 PM, "Vardavas, Raffaele" wrote:
>
> Dear Support,
> I am considering purchasing your software depending on whether I can make R
> connect to an Oracle database.
> I am using a Mac OS X 10.6.8 . I have downloaded Oracle and imported an xml
> file that allows it to remotely connect to a database.
> I have downloaded and installed ODBC Manager and created a new connection by
> going to the drivers tab and configuring an actual oracle connection. In the
> set up configuration I enter the following:
> Driver Name: Actual Oracle
> Driver File: /Library/ODBC/Actual Oracle.bundle/Contents/MacOS/atoradb.so
> Setup File: /Users/rvardava/Documents/Projects_2012/OCIE/ism.xml
> Notice that the setup file points to the xml file that contains the userid
> and password to access the remote database.
> I then go to System DNS tab in the ODBC Manager and configure the DNS
> connection naming it OCIE. In the set up I enter the server name and port
> that are included in the XML file.
> Then when I go to R I do the following:
> library(RODBC)
> aconn<-odbcConnect("OCIE",uid="eusip",pwd="PASS")
> Where PASS is the actual password that is contained in the xml file.
> This however doesn't seem to work. Please can you advise on how to set this
> up so I can connect to this database via R through your ODBC software.
> Attached is the xml file stripped of the password which should provide
> useful.
> Thank you.
> Raff.
>
>
>
> Subject: Re: [R] remote connection to an Oracle database - using RODBC -
> RMySQL..?
> From: marc_schwa...@me.com
> Date: Mon, 26 Nov 2012 14:00:44 -0600
> CC: r-h...@r-project.org
> To: r_varda...@hotmail.com
>
>
> On Nov 26, 2012, at 1:47 PM, Raffaello Vardavas <r_varda...@hotmail.com>
> wrote:
>
> Thank you Marc,
>
> I will study the material you sent and follow up on this at R-SIG-DB - should
> I still have problems.
>
> I'm using a Mac OS X 10.6.8
>
> Thanks.
> Raff.
>
>
> In that case, if you use RODBC, you will want to get the Oracle ODBC driver
> for OSX from Actual Technologies, which is also what I use on 10.8.2. More
> info here:
>
> http://www.actualtech.com/product_oracle.php
>
> Note that it is not free ($34.95 US) and Oracle does not provide OSX ODBC
> drivers. This is covered in the RODBC vignette.
>
> The good news is that it makes it fairly easy to set up the DSN connection to
> Oracle, as you don't have to worry about a lot of the low level configuration
> issues. You can download an eval version of the driver for free. The
> limitation of the free version is that a query will only return the first 3
> rows. You would then need to pay for a fully functional license.
>
> Regards,
>
> Marc
>
>
> > Subject: Re: [R] remote connection to an Oracle database - using RODBC -
> > RMySQL..?
> > From: marc_schwa...@me.com
> > Date: Wed, 21 Nov 2012 14:16:15 -0600
> > CC: r-h...@r-project.org
> > To: r_varda...@hotmail.com
> >
> >
> > On Nov 21, 2012, at 1:52 PM, Raffaello Vardavas <r_varda...@hotmail.com>
> > wrote:
> >
> > >
> > > Dear users,
> > >
> > > I can access an database oracle database using sql developer. This was
> > > done by importing an xml file that contains the login details - username,
> > > password and specifies that it uses the KERBEROS_AUTHENTICATION.
> > >
> > > I'm trying to connect R - so that it can access this database - do sql
> > > queries and convert the resulting tables into dataframes.
> > >
> > > I am a novice in SQL and database access - but a friend provided me with
> > > the following approach:
> > >
> > >
> > > library(DBI)
> > > library(RMySQL)
> > > drvr<-dbDriver("MySQL") #Or another driver, say from the RODBC package?
> > > acon<-dbConnect(drvr, user="ENTER_USERID", dbname="ENTER_NAME",
> > > host="ENTER_HOST",
> > > port=1521,password=NULL) #password maybe non-null?
> > > cmds<-dbSendQuery(acon,statement="YOUR SQL QUERY HERE")
> > > yourdata<-fetch(cmds, n=-1) #Collects all rows and columns of data
> > > requested query.
> > >
> > > I have provided this info changing the relevant info in the dbConnect
> > > command and provided the password. However this doesn't work. I suspect
> > > because in this command there is not specification of the encryption of
> > > the password (i.e., KERBEROS_AUTHENTICATION)
> > >
> > >
> > > When I look at the details of the connection in SQL developer - what is
> > > specified is the follow:
> > >
> > > connection name, username, password (that I cannot see), hostname, port
> > > and the SID.
> > >
> > > Note that although the password here cannot be seen -
> > > I believe it is computed by the longer password displaced the the xml
> > > file I use to set up the connection with sql developer using the
> > > KERBEROS_AUTHENTICATION.
> > >
> > > Any ideas on how to proceed.
> > >
> > > Please help.
> > >
> > > Thank you.
> > >
> > > Raff.
> >
> >
> > Several comments:
> >
> > 1. Future posts on this subject should be made to R-SIG-DB, not here. More
> > info:
> >
> > https://stat.ethz.ch/mailman/listinfo/r-sig-db
> >
> > 2. Why would you expect to use an R package and driver for MySQL when
> > attempting to access an Oracle server?
> >
> > 3. There is a good starting point on this subject generally in the R Data
> > Import/Export manual:
> >
> > http://cran.r-project.org/doc/manuals/r-release/R-data.html#Relational-databases
> >
> > 4. I would recommend using RODBC, which is what I use. You will of course
> > need to have an ODBC driver for Oracle installed on your system and
> > properly configured. You may need to get that from Oracle or other parties
> > depending upon your OS which is unstated here. You may also need to get
> > assistance with that process from your SysAdmin or DBAdmin.
> >
> > 5. If you use RODBC, there is additional, quite good information in the
> > package vignette, which is accessible by using:
> >
> > vignette("RODBC")
> >
> > post package installation.
> >
> > 6. I don't have any experience using Kerberos authentication on my Oracle
> > server here, so you may have to follow up on the R-SIG-DB list on that
> > point. A search of the archives did not reveal anything material on that
> > point.
> >
> > 7. Alternatives to RODBC would include ROracle and RJDBC via CRAN.
> >
> > Regards,
> >
> > Marc Schwartz
> >
>
[[alternative HTML version deleted]]
_______________________________________________
R-sig-DB mailing list -- R Special Interest Group
R-sig-DB@r-project.org
https://stat.ethz.ch/mailman/listinfo/r-sig-db
