DMS task target validator using athena "Unable to verify/create output bucket"

Question

When executing a task the last step is validating the data migrated with the source against target apparently using Athena, I have the following error:

2023-11-07T22:09:04 [VALIDATOR_TARGE ]E:  Not retriable error:  Unable to verify/create output bucket [target bucket name] [1001730]  (anw_retry_strategy.cpp:118)
2023-11-07T22:09:04 [VALIDATOR_TARGE ]I:  The athena start query execution request failed: Unable to verify/create output bucket [target bucket name] (athena_client.cpp:439)
2023-11-07T22:09:04 [VALIDATOR_TARGE ]I:  Failed to create Athena Database aws_dms_s3_validation_dbo  (file_validation_table_preprocessor.c:1046)
2023-11-07T22:09:04 [VALIDATOR_TARGE ]I:  Failed to perform s3 validation preprocessing step  (file_target_endpoint_validation.c:97)
2023-11-07T22:09:04 [VALIDATOR       ]I:  Failed to apply validation preprocessiong step for table [table name]  (task_validator.c:504)
2023-11-07T22:09:04 [VALIDATOR       ]I:  Preprocessing step failed for table 1, suspending the validation  (task_validator.c:306)2023-11-07T22:09:04 [VALIDATOR       ]I:  Suspending table : 1 from validation with error message : Failed to preprocess table for S3 Validation  (partition_update_tracker.c:1284)

the task is finished succesfully but this error disables the EventBridge rule that automates the execution of the task.

I have tried the following things:
* verified every permission needed for the dms role against all resources
* the next step in the process is a lambda script that transfers the file to another bucket, deactivated it just in case it worked "too fast"
* deleted the specified kms key on the bucket
* specified a output bucket for athena using the same target bucket

Would like to know if anyone ever had the same error, this solution is designed to work as a data lake, extracting data with dms and creating the catalog with Glue. Thank you so much!

Answer

If you manually set the query result location, you must confirm that the S3 bucket exists. Then, check the IAM policy for the user or role that runs the query:

Confirm that the permissions in the following example policy, such as s3:GetBucketLocation are allowed.
Be sure that the IAM policy does not contain a Deny statement that uses aws:SourceIp or aws:SourceVpc to restrict S3 permissions.

If the bucket already exists, then the s3:CreateBucket permission isn't required. If you manually set the query result location, then don't include arn:aws:s3:::aws-athena-query-results-* in the policy. The policy must include arn:aws:s3:::query-results-custom-bucket and arn:aws:s3:::query-results-custom-bucket/* only if you manually set the query result location.

DMS task target validator using athena "Unable to verify/create output bucket"

Relevant content