CODEWITHSUNDEEP
rwindowsodbcteradatar-dbi
i_love_chocolate
i_love_chocolate

Reputation: 464

Connect to Teradata Database using R + DBI library + ODBC

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:

Background:

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

Screenshot of "ODBC Driver Setup for Teradata Database" window

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

Upvotes: 2

Views: 1908

Answers (2)

burnedOut
burnedOut

Reputation: 1

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.

Upvotes: 0

TallTed
TallTed

Reputation: 9434

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")
   )

Upvotes: 2

Related Questions