Password authentication error with Amazon Redshift if certain characters are present in the password

[shalini1017]

library(odbc)
con <- dbConnect(odbc::odbc(), 
driver = "Redshift", 
server = abc.redhsift.amazonaws.com, 
database = db_name, 
uid = abc,
pwd = password,
port = 1234)

returns
Error: nanodbc/nanodbc.cpp:1135: 0000: [RStudio][AmazonRedshift] (10) Error occured while trying to connect: [SQLState 28000] connection to server "abc.redhsift.amazonaws.com" , port 1234 failed: FATAL: password authentication failed for user "abc".

I do not see this error when I try connecting to Redshift using Python. My observation is that the whenever the Redshift password contains the characters []{}(),;?*=!@ it fails with the above error. The Redshift documentation here suggests to wrap the password inside curly braces {} whenever these characters are present but wrapping them inside curly braces does not work with odbc.

Database

Amazon Redshift

[hadley]

I just spotted this in the ODBC api docs:

Because of connection string and initialization file grammar, keywords and attribute values that contain the characters []{}(),;?*=!@ should be avoided. Because of the grammar in the system information, keywords and data source names cannot contain the backslash () character. For an ODBC 2.x driver, braces are required around the attribute value for the DRIVER keyword.

So this is likely to be a problem for other drivers too.

[hadley]

I do wonder if we should automatically escape these:

A DSN or connection string value enclosed with braces ({}) containing any of the characters []{}(),;?*=!@ is passed intact to the driver.

— https://learn.microsoft.com/en-us/sql/odbc/reference/syntax/sqldriverconnect-function?view=sql-server-ver16#comments

[shalini1017]

Thank you for this information, Hadley. This is helpful. I will try escaping {} and see if it works.