Connect to Redshift Error's

KNIME Extensions
1

Hey KNIME world,

I am new to KNIME and having trouble connecting to my redshift database using my credentials. I am connected via DBeaver so I know it is not a permisions issue. Currently we are using iam to authenticate through JDBC to the data base. I have created a new database driver in preferences (redshift connector has not worked so i am using DB Connector).

Here is what my connection string looks like for DBeaver

jdbc:redshift:iam://{host}:{port}/{database}?plugin_name=com.amazon.redshift.plugin.AdfsCredentialsProvider&idp_host=*************

Here is my current URL template in my new database driver.
jdbc:redshift:iam://://[plugin_name=com.amazon.redshift.plugin.AdfsCredentialsProvider&idp_host=*********]

Here is the current error I am getting in my log:
2021-05-25 14:14:48,494 : ERROR : KNIME-Worker-51-DB Connector 0:13 : : Node : DB Connector : 0:13 : Execute failed: [JDBC Driver]Unable to find a region via the region provider chain. Must provide an explicit region in the builder or setup environment to supply a region.
java.sql.SQLException: [JDBC Driver]Unable to find a region via the region provider chain. Must provide an explicit region in the builder or setup environment to supply a region.
at com.amazonaws.client.builder.AwsClientBuilder.setRegion(AwsClientBuilder.java:371)
at com.amazonaws.client.builder.AwsClientBuilder.configureMutableProperties(AwsClientBuilder.java:337)
at com.amazonaws.client.builder.AwsSyncClientBuilder.build(AwsSyncClientBuilder.java:46)
at com.amazon.redshift.plugin.SamlCredentialsProvider.refresh(Unknown Source)
at com.amazon.redshift.plugin.SamlCredentialsProvider.getCredentials(Unknown Source)
at com.amazon.redshift.plugin.SamlCredentialsProvider.getCredentials(Unknown Source)
at com.amazon.redshift.core.IamHelper.setIAMCredentials(Unknown Source)
at com.amazon.redshift.core.IamHelper.setIAMProperties(Unknown Source)
at com.amazon.redshift.core.PGJDBCConnection.connect(Unknown Source)
at com.amazon.jdbc.common.BaseConnectionFactory.doConnect(Unknown Source)
at com.amazon.jdbc.common.AbstractDriver.connect(Unknown Source)
at org.knime.database.connection.UrlDBConnectionController$ControlledDriver.connect(UrlDBConnectionController.java:95)
at org.knime.database.connection.UrlDBConnectionController.createConnection(UrlDBConnectionController.java:308)
at org.knime.database.connection.UserDBConnectionController.createConnection(UserDBConnectionController.java:249)
at org.knime.database.connection.AbstractConnectionProvider.createConnection(AbstractConnectionProvider.java:89)
at org.knime.database.connection.impl.DBConnectionManager.lambda$2(DBConnectionManager.java:501)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: com.amazonaws.SdkClientException: Unable to find a region via the region provider chain. Must provide an explicit region in the builder or setup environment to supply a region.

Help?

2

Hello @kmcguire,

did you install KNIME Amazon Redshift Driver extension? If not can you do it and then give Amazon Redshift Connector another try?

Br,
Ivan

3

@ipazin,

Thanks for replying, yes I have installed the driver. Just to clarify I do have a system user and password that work just fine in the Redshift Connector node. I am trying to get several other people in my org onto knime and IT is not willing to create more system users and I am not supposed to share my system user credentials.

So I am trying to connect with single sign on credentials. That is where I am hitting a wall, I think it is just my URL template syntax.

I have solved the region issue. Here is my database URL and new log info.

jdbc:redshift:iam://://[plugin_name=
com.amazon.redshift.plugin.AdfsCredentialsProvider&idp_host=
host_name_here&perferred_role=arn:aws:iam::12digit_number_here:role/ADFS-Redshift-data_reader&region=us-west-2]

Error:
Execute failed: [JDBC Driver]Unable to load AWS credentials from any provider in the chain

KNIME log:

2021-05-26 09:23:40,994 : ERROR : KNIME-Worker-76-DB Connector 0:13 : : Node : DB Connector : 0:13 : Execute failed: [JDBC Driver]Unable to load AWS credentials from any provider in the chain
java.sql.SQLException: [JDBC Driver]Unable to load AWS credentials from any provider in the chain
at com.amazonaws.auth.AWSCredentialsProviderChain.getCredentials(AWSCredentialsProviderChain.java:131)
at com.amazon.redshift.core.IamHelper.setIAMCredentials(Unknown Source)
at com.amazon.redshift.core.IamHelper.setIAMProperties(Unknown Source)
at com.amazon.redshift.core.PGJDBCConnection.connect(Unknown Source)
at com.amazon.jdbc.common.BaseConnectionFactory.doConnect(Unknown Source)
at com.amazon.jdbc.common.AbstractDriver.connect(Unknown Source)
at org.knime.database.connection.UrlDBConnectionController$ControlledDriver.connect(UrlDBConnectionController.java:95)
at org.knime.database.connection.UrlDBConnectionController.createConnection(UrlDBConnectionController.java:308)
at org.knime.database.connection.UserDBConnectionController.createConnection(UserDBConnectionController.java:249)
at org.knime.database.connection.AbstractConnectionProvider.createConnection(AbstractConnectionProvider.java:89)
at org.knime.database.connection.impl.DBConnectionManager.lambda$2(DBConnectionManager.java:501)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: com.amazonaws.SdkClientException: Unable to load AWS credentials from any provider in the chain

4

Hello @kmcguire,

have you had any success? Unfortunately don’t think I can help you much more so let’s see if someone else joins topic.

Br,
Ivan