Password authentication fails while connecting to Amazon Redshift

Password authentication fails while connecting to Amazon Redshift

When you try to connect to Amazon Redshift data source from Spotfire Analyst, if the password contains special characters, then the following error might occur:
 

ERROR [28000] [Amazon][Amazon Redshift] (10) Error occurred while trying to connect: [SQLState 28000] FATAL:  password authentication failed for user "sdx01"

   at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
   at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, OdbcConnectionString connectionOptions)
   at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.Odbc.OdbcConnection.Open()
   at Spotfire.Dxp.Data.Adapters.PostgreSqlBase.PostgreSqlBaseAdapterConnection.TryOpenConnection(OdbcConnection connection, Int32 connectionTimeout)

  It is a known defect with Redshift connector if password contains any of the following special characters:

• @
• %
• }
• {
• +
• ;
• <space>

As a workaround, remove the above mentioned special characters from the password to make this work.

This issue is addressed in 10.3 HF-012, 10.8 and on wards. It will connect to Redshift if the password contains any of these characters: [ % , { , } ,+ , ; ,<space>].

Note that @ character cannot be used due to limitations in Redshift. 

 Although some exceptions have already been mentioned in the Redshift documentation. Following is the extract from the same:
 

"It can be any printable ASCII character (ASCII code 33 to 126) except ' (single quotation mark), " (double quotation mark), \, /, @, or space".

Redshift Documentation

• https://docs.aws.amazon.com/redshift/latest/mgmt/amazon-redshift-limits.html