password with special characters in odbc connection string in R

[alipprc]

Some of my shiny app users can not login to the database via app but they could from their windows machine via SQL management studio. I'm using the odbc package to create the connection string like :

ch <- odbc::dbConnect(odbc(), DSN = "myDSN", 
                Database = "myDb", 
                Authentication = "ActivedirectoryPassword",
                encoding = "windows-1252",
                AutoTranslate="yes", 
                UID = usr, 
                PWD = pas)

I checked some of the users using crazy passwords with special character like :

"1c0d9a1138a[]bb#$2m9d445654_Ñ#)/f8"

How I should change the encoding that this kind of password can be passed to the connection string via odbc in R?

[akdor1154]

I've been generating a connection string myself because I ran into this and other things..

pii_conn <-  DBI::dbConnect(drv = odbc::odbc(),
                            .connection_string = c(
                              "DSN={myDSN}",
                              "Database=myDb",
                              sprintf("UID={%s}", user_and_pass[1]),
                              sprintf("PWD={%s}", user_and_pass[2]) 
                            ) %>% paste(.data, sep=";", collapse="")
)

This has the advantage you can quote or not as you see fit - SQL server likes {} as well, note how I've used them in the example. It also means you can just google odbc connections for your db and use the parameters directly instead of trying to guess how odbc will pass them through.

[daniepi]

@akdor1154 My password contains "," "{" and "$". I tried your approach with connection string, but it didn't help. Connecting to teradata in Python, the same password works as-is when using Python's teradatasql.connect() Any ideas about how could I get things work using odbc in R? Thanks a lot.

[alipprc]

@akdor1154 That method did not works for me as well. I made another test also : I have created a test user in my database and set the authentication to sql server password, so my connection string looks like :

ch <- odbc::dbConnect(odbc(), DSN = "myDSN", 
                Database = "myDb", 
                Authentication = "SqlPassword",
                encoding = "windows-1252",
                AutoTranslate="yes", 
                UID = usr, 
                PWD = pas)

and now everything is fine !!! with the same set of special characters in my password ! So what ever it is, is related to the authentication method part !

[corinabioinformatic]

What about other special characters in password when using ODBC? Do you have any other reference?

[LucasSchelkes-BA]

When I use the configs given in .odbc.ini, are special characters are problem? And if yes, how do I escape them?

[hadley]

Duplicate of #367