Connect to Teradata Database using R + DBI library + ODBC

Question

I'm trying to connect to a Teradata Database in R using the DBI library (if it matters I'm on Windows). I can successfully connect using the RODBC library so I know my credentials etc. are correct.

I suspect the issue is:

• I am not correctly specifying the authentication mechanism
• Wrong driver: instead of using ODBC, perhaps I should be using JDBC?

---

Background:

First here's my ODBC info if I look it the ODBC Database Source Administrator:

• Name = name_name
• Driver = Teradata
• Name or IP address = address.here.ok
• Mechanism = ldap
• Username = my_username

Using R 3.5.1 (2018-07-02), RStudio, Windows 10.

---

What works:

library(RODBC) 
con = odbcConnect(dsn = "name_name"
                  ,uid = rstudioapi::askForPassword("Username")
                  ,pwd = rstudioapi::askForPassword("Password")
                  )

---

What I've tried with DBI and fails:

library(DBI)

con <- DBI::dbConnect(odbc::odbc()
                      ,driver = "Teradata"
                      ,DBCName = "name_name"
                      ,host   = "address.here.ok"
                      ,uid    = rstudioapi::askForPassword("Database user")
                      ,pwd    = rstudioapi::askForPassword("Database password"))

Error: Error: nanodbc/nanodbc.cpp:950: 28000: [Teradata][ODBC Teradata Driver][Teradata Database] The UserID, Password or Account is Invalid

con <- DBI::dbConnect(odbc::odbc()
                      ,driver = "Teradata"
                      ,DBCName = "name_name"
                      ,host   = "address.here.ok"
                      ,uid    = rstudioapi::askForPassword("Database user")
                      ,pwd    = rstudioapi::askForPassword("Database password")
                      ,MechanismName = "ldap")

Error: Error: nanodbc/nanodbc.cpp:950: 28000: [Teradata][ODBC Teradata Driver][Teradata Database] The UserID, Password or Account is Invalid

I've seen this error in similar situations when the mechanism is not specified correctly (ldap), so maybe MechanismName is wrong?

I know I can say whatever I like in the connection string and no error is raised so if MechanismName is not the correct way to specify the authentication mechanism I will not have an error returned. For example:

con <- DBI::dbConnect(odbc::odbc()
                      ,driver = "Teradata"
                      ,DBCName = "name_name"
                      ,host   = "address.here.ok"
                      ,uid    = rstudioapi::askForPassword("Database user")
                      ,pwd    = rstudioapi::askForPassword("Database password")
                      ,MechanismName = "ldap")
                      ,made_up_input = "I like cats"

gives the error: Error: nanodbc/nanodbc.cpp:950: 28000: [Teradata][ODBC Teradata Driver][Teradata Database] The UserID, Password or Account is Invalid

Thanks in advance.

Edit: got idea to use MechanismName from an issue raised in Git

Answer 1

Like R, DBI is C-based and speaks ODBC. If it were Java-based, it would speak JDBC.

I would suggest you test with hard-coded UID and PWD, before using the askForPassword function.

That said, I believe you will succeed with --

DBI::dbConnect(odbc::odbc() 
    ,dsn = "name_name" 
    ,uid = rstudioapi::askForPassword("Username") 
    ,pwd = rstudioapi::askForPassword("Password")
   )

Answer 2

For those finding this late, the original DBI call will only work if the exact name of the driver as it appears in your ODBC manager is provided. Open the ODBC Data Source Administrator app and click on the Drivers tab. Enter the driver name exactly as it appears in the Name column and it should work. Example, 'Teradata Database ODBC Driver 16.10' instead of 'Teradata' as shown above.