remote connection to an Oracle database - using RODBC
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_vardavas at 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 support at 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_schwartz at me.com Date: Mon, 26 Nov 2012 14:00:44 -0600 CC: r-help at r-project.org To: r_vardavas at hotmail.com On Nov 26, 2012, at 1:47 PM, Raffaello Vardavas <r_vardavas at 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_schwartz at me.com Date: Wed, 21 Nov 2012 14:16:15 -0600 CC: r-help at r-project.org To: r_vardavas at hotmail.com On Nov 21, 2012, at 1:52 PM, Raffaello Vardavas <r_vardavas at 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