DatabaseConnector Error with AWS and IAM connection string

Developers
1

We’re working with a community member that is having issues connecting to Redshift using DatabaseConnector. The only difference that we can identify is that they are using IAM roles as part of their authentication. The following connection string works from their SQL Workbench but fails with the following error when using DatabaseConnector.

> connectionDetails <- DatabaseConnector::createConnectionDetails(dbms= “redshift”, connectionString=“jdbc:redshift:iam://servername-replaced-to-protect-the-innocent”)

> connection <- DatabaseConnector::connect(connectionDetails) Connecting using Redshift driver Error in rJava::.jcall(jdbcDriver, “Ljava/sql/Connection;”, “connect”, : java.lang.NoClassDefFoundError: com/amazonaws/auth/profile/ProfilesConfigFile

Any help would be appreciated.

2

@Frank I think problem is in connection string.

It should be “jdbc:redshift://{host}:5439/{database_name}”. As far as I know DatabaseConnector is not able to work with IAM roles. You need to make standard Redshift account.

3

Hi Frank,

I concur with Konstantin. It seems like the createConnectionDetails parameters are malformed. Take a look below at the standard createConnectionDetails call made by OHDSIonAWS to Redshift. Could you try implementing it with this pattern?

connectionDetails <- DatabaseConnector::createConnectionDetails(dbms = “redshift”,
server = “test-2asdn9413-redshiftcluster-1nfsmywmhu42r.us-east-1.redshift.amazonaws.com/mycdm”,
user = “username”,
password = “password”,
port = “5439”)

6

Update: This issue has been resolved. Turns out that we have a utility script that runs at login to generate dynamic passwords. Thanks everyone for your help!

@JamesSWiggins, @Konstantin_Yaroshove
Thank you both for your suggestions. I have tested the following per your suggestions. They ended up with the same error related to password:

Standard connection

connectionDetails <- DatabaseConnector::createConnectionDetails(dbms= “redshift”, user=“myid”, password=“mypwd”,
server="{host}/analytics",
port=“5439”
)

connectionString

connectionDetails <- DatabaseConnector::createConnectionDetails(dbms= “redshift”, user=“myid”, password=“mypwd”,
connectionString =“jdbc:redshift://{host}:5439/analytics?DbGroups=rwed_us” )

ERROR:
Error in rJava::.jcall(jdbcDriver, “Ljava/sql/Connection;”, “connect”, :
java.sql.SQLException: Amazon Invalid operation: password authentication failed for user “myid”;

Did I misinterpret your suggestions? Anything else I should try? I will also point our IT contact to this string for further discussion.

Thank you!

7

hi @qifeng

It looks like connection is fine and the problem is in user/password