topic Re: Redshift Target - Invalid role ARN in Qlik Replicate

Redshift Target - Invalid role ARN

simonB2020 — Tue, 03 May 2022 14:27:22 GMT

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]
-----------------------------------------------

Re: Redshift Target - Invalid role ARN

KellyHobson — Tue, 03 May 2022 14:47:07 GMT

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_redshift.htm

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-assumed-14c17e1098e2

Best,

Kelly

Re: Redshift Target - Invalid role ARN

Shai_E — Tue, 03 May 2022 15:14:44 GMT

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 roles - Amazon Redshift

Re: Redshift Target - Invalid role ARN

simonB2020 — Tue, 03 May 2022 16:22:49 GMT

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 ?

Re: Redshift Target - Invalid role ARN

simonB2020 — Tue, 03 May 2022 17:40:51 GMT

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 ?

Re: Redshift Target - Invalid role ARN

lyka — Wed, 04 May 2022 20:50:35 GMT

Hi Simon,

I suggest opening a support case to further troubleshoot this issue

Thanks

Lyka