Redshift connection issue: FATAL: password authentication failed for user

AFHood Published at Dev

AFHood

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.

Masashi Miyazaki

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.

Collected from the Internet

Please contact [email protected] to delete if infringement.