CODEWITHSUNDEEP
postgresqlssljdbcamazon-redshift
AFHood
AFHood

Reputation: 1040

Redshift connection issue: FATAL: password authentication failed for user

We are connecting remotely to AWS Redshift instance.

We can successfully connect with Aginity workbench.

However, we cannot connect with a JDBC based tool (DBVisualizer).

We are using the Postgresql driver 8.4-703 (from Redshift docs).

SSL is required.

We use a non-standard port (1433).

Message returned is 

FATAL: password authentication failed for user xxxx

JDBC string is 

jdbc:postgresql://xxxxxxx.us-west-2.redshift.amazonaws.com:1433/dev?tcpKeepAlive=true

Debug is: 

org.postgresql.util.PSQLException: FATAL: password authentication failed for user "xxxx"
    at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:291)
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:108)
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66)
    at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:125)
    at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:30)
    at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:22)
    at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:30)
    at org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.java:24)
    at org.postgresql.Driver.makeConnection(Driver.java:393)
    at org.postgresql.Driver.connect(Driver.java:267)
    at sun.reflect.GeneratedMethodAccessor55.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at com.onseven.dbvis.g.B.D.?(Z:1413)
    at com.onseven.dbvis.g.B.F$A.call(Z:1474)
    at java.util.concurrent.FutureTask.run(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)
SQLException: SQLState(28000)

UPDATED: I added SSL=true to the JDBC string..

New string: 

jdbc:postgresql://xxxxxxx.us-west-2.redshift.amazonaws.com:1433/dev?tcpKeepAlive=true&ssl=true

New debug is:

org.postgresql.util.PSQLException: The connection attempt failed.
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:150)
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66)
    at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:125)
    at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:30)
    at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:22)
    at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:30)
    at org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.java:24)
    at org.postgresql.Driver.makeConnection(Driver.java:393)
    at org.postgresql.Driver.connect(Driver.java:267)
    at sun.reflect.GeneratedMethodAccessor55.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at com.onseven.dbvis.g.B.D.?(Z:1413)
    at com.onseven.dbvis.g.B.F$A.call(Z:1474)
    at java.util.concurrent.FutureTask.run(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)
SQLException: SQLState(08001)

Obviously we've quadruple checked the password.

Upvotes: 1

Views: 13430

Answers (1)

Masashi M
Masashi M

Reputation: 2757

To enable SSL option for JDBC, you have to download a redshift certificate and add it to your Java system truststore on your machine.

Here is the step:

  • Download Amazon Redshift certificate file from here.

  • Register Redshift certificate to your Java system truststore.

    • Run ${JAVA_HOME}/bin/keytool -keystore ${JAVA_HOME}/lib/security/cacerts -import -alias <alias> -file <certificate_filename>
    • Run ${JAVA_HOME}/bin/keytool -keystore <keystore_name> -alias <alias> -import -file <certificate_filename> to create your own truststore, If you don't have a permission to access cacerts.
    • The default password is changeit
  • Specify the keystore and password in java VM properties
    • Open [DbVisualizer] -> [Preferences] -> [General] Tab -> [General]
    • Edit "Java VM Properties" section
      • -Djavax.net.ssl.trustStore=key_store_name_or_path
      • Djavax.net.ssl.trustStorePassword=password
    • Apply and restart

Some paths in the above commands might need to be changed for your environment. See Amazon Redshift Management Guide for details.

As for the authentication error, there might be unsupported password symbols in the Application level. It's worth a try to change a password to make it simple like containing only "_" for symbols.

Upvotes: 1

Related Questions