[SQLState XX000] ERROR: User arn:aws:redshift:ap-south-1:316720341464:dbuser:redshift-cluster-1/analytics is not authorized to assume IAM Role arn:aws:iam::323232323:role/aws-access-for-redshift

While Configure Data Migration Service between Redshift and any kind of heterogeneous source , we come across these issues often .

Error looks like an IAM policy is missing for attached , so we straightly move into IAM console granted AmazonS3FullAccess AmazonRedshiftFullAccess , then restart failed DMS tasks .

But no luck , still we are getting same issue

When we looking into Redshift IAM role , customer have been added custom roles with permissions . so it is missing trusted entities for both redshift.amazonaws.com and dms.amazonaws.com

Move into Trust relationships tab in custom role and edit trust relationship as below ,

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "",
      "Effect": "Allow",
      "Principal": {
        "Service": [
          "redshift.amazonaws.com",
          "dms.amazonaws.com"
        ]
      },
      "Action": "sts:AssumeRole"
    }
  ]
}

after adding trusted entities

Attached policies for DMS tasks

Once we update trust policy , able to load data between MySQL to Redshift

Remember to troubleshoot DMS tasks in depth , always enable detailed debug in cloudwatch logs