Spotfire Analyst: Password authentication fails while connecting to Amazon Redshift using Spotfire connector

Product:TIBCO Spotfire Analyst
Versions:7.5 to 10.7

Summary:
Password authentication fails while connecting to Amazon Redshift using the Spotfire connector if the password contains special characters.

Details:
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)

 

Resolution:
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 hotfix 10.3 HF-012, 10.8 and onwards. 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".