Redshift Target - Invalid role ARN

[simonB2020]

My Redshift endpoint passed the 'test connection' process.

However, upon execution gives the error below. (Invalid role)

I can see that my S3 Staging is being populated, so that part is ok.
Assume is an issue with the COPY action.

The Redshift user has 'ALL' on the target schema.
The Redshift cluster role has * on the S3 location.

Any ideas ?
I'm quite curious about the error saying 'invalid' role arn, rather than 'access denied' ?

Failed to copy data of file I:\Attunity\Replicate\data\tasks\myTask\cloud\1\LOAD00000002.csv to database
Failed to load schema.table from S3, file name: LOAD00000002.csv
RetCode: SQL_ERROR SqlState: XX000 NativeError: 30 Message: [Amazon][Amazon Redshift] (30) Error occurred while trying to execute a query: [SQLState XX000] ERROR: Invalid role ARN: arn:aws:iam::xxxxxxxxxx:instance-profile/xxxxxxxxx_profile
DETAIL:
-----------------------------------------------
error: Invalid role ARN: arn:aws:iam::account:instance-profile/xxxxx_profile
code: 30000
context:
query: 3922559
location: xen_aws_credentials_mgr.cpp:402
process: padbmaster [pid=25289]
-----------------------------------------------

Failed to copy data of file I:\Attunity\Replicate\data\tasks\myTask\cloud\1\LOAD00000002.csv to database
Failed to load xxxxxx.xxxxx from S3, file name: LOAD00000002.csv
RetCode: SQL_ERROR SqlState: XX000 NativeError: 30 Message: [Amazon][Amazon Redshift] (30) Error occurred while trying to execute a query: [SQLState XX000] ERROR: Invalid role ARN: arn:aws:iam::xxxxxxxxxx:instance-profile/xxxxxxxxxx_profile
DETAIL:
-----------------------------------------------
error: Invalid role ARN: arn:aws:iam::account:instance-profile/xxxxxxxx_profile
code: 30000
context:
query: 3922559
location: xen_aws_credentials_mgr.cpp:402
process: padbmaster [pid=25289]
-----------------------------------------------

[KellyHobson]

Hey @simonB2020 

What version of Replicate are you using?

Have you followed all prerequisites listed here: https://help.qlik.com/en-US/replicate/May2021/Content/Replicate/Main/Amazon%20Redshift/prereq_redshi...

Below is an article with same error. They resolved by recreating the role.

https://thewerner.medium.com/aws-cloud-formation-role-arn-aws-iam-xxx-is-invalid-or-cannot-be-assume...

Best,

Kelly

[Shai_E]

Hi simonB2020,

Im going to add to what Kelly has already specified in her comment.

When searchhing for the key word "ARN" with amazon redshift i found this article also:

Authorizing COPY, UNLOAD, CREATE EXTERNAL FUNCTION, and CREATE EXTERNAL SCHEMA operations using IAM ...

[simonB2020]

I think we are at the probem.

I think for me, just a little clarification of what is going on under the covers of Replicate ...

When we execute a COPY command, we have to provide a role

copy mytable
from 's3://bucket/prefix/'
iam_role 'arn:aws:iam::myRole'

In my case, it looks like Replicate is using the "IAM Role ARN" specified in my S3 connection configuration ?

(that role being used for Replicate to write to S3, and to perform the COPY from Redshift)

And if I uses Key/secret in my S3 config, then Replicate would use the default role attached to it's EC2 instance ?

[simonB2020]

Even more confused now.

Looking in Redshift logs (STL_QUERY), I can see:
"COPY ANALYZE mytable" is being called by the DB User specified in my "Amazon Redshift Target" config in Replicate.

Hence wondering how the "IAM Role ARN" specified in my "Amazon S3 Staging" configuration is coming back in the error message for the COPY action ?

Can anyone explain the relationship there ?

[lyka]

Hi Simon,

I suggest opening a support case to further troubleshoot this issue

Thanks

Lyka