Athena quicksight error 100071

Question & Answer
1

Hi everyone,

I have the exact same issue as Error when connecting to Athena Data Source

When creating dataset using athena data source, the write query result for test statement i.e. “select 1” to output s3 bucket is failing.

|sourceErrorCode:|ACCESS_DENIED_TO_RESULT_STAGING_AREA|

|sourceErrorMessage:|[Simba]AthenaJDBC An error has been thrown from the AWS Athena client. Access denied when writing to location: s3://aws-athena-query-results-us-east-1-account-number/526fff0c-xxxxx.csv [Execution ID: 526fff0c-xxxxx]

Has there been any solution found for this?

I have tried boto3’s “start_query_execution” in lambda function with similar role permissions and athena query works fine.

I have also added KMS permissions for s3 bucket but I am not sure if inline policy permission changes on quicksight service-role actually take effect.

Please help with this.

Note: Initially in the QuickSight “security & permissions” screen, I allowed access to s3 buckets (including query result s3 with write workgroup checked), but the workgroup list was not loading when creating athena datasource. I had to manually add “athena:*” permission into “AWSQuickSightIAMPolicy” (Adding this into a inline policy did not work). Maintaining these custom policy change via IAM console is difficult since any further change via Quicksight “security & permission” is not possible after role override.

2

Hi - are you writing the data to S3 via athena table from QuickSight. Out of curiosity why you are doing it… Writeback functionality may not be supported… can you first check whether you are able to access Athena data ( source as source) or not.

Regards - San

3

@Sanjeeb2022 when executing Athena statements, the result is written into a s3 bucket based on workgroup’s query result output S3 bucket configured.

All I am trying to do here is create a dataset using Athena as datasource. When clicking on “create dataset”, there is a test statement i.e. “select 1” being executed using workgroup selected & it fails since write to the corresponding output result S3 bucket is denied. Athena workgroup’s output result S3 bucket is not optional, it has to be configured.

This is happening even though I have selected this S3 bucket and workgroup write when configuring QS security & permissions.

The other question linked also has similar issue.

4

Thanks @Karthick for the details. Can you please check below details.

  1. Go to QuickSight → User ( In the top right side)–> Manage QuickSight
  2. Go to Security and Permission and see whether Athena is allowed or not. If not, allow Athena and allow S3 and select the buckets where your data and output sqls result are stored… Click Save.
  3. If you do not have permission to do the same, request your admin team to help you.

After doing the above steps, if you are still facing the issue, please submit a ticket to AWS so that they can help you in a chime call where you can show your environment and error details.

Hope this will help you.

Regards - San

1 Like