How do I resolve "Access denied" errors when I use Athena as a data source in Quicksight?

Short description

The following are common reasons for Access denied errors when you use Amazon Athena as a data source in Amazon QuickSight:

• Your QuickSight account doesn't have the required permission to access the Amazon Simple Storage Service (Amazon S3) bucket.
• Your data file is encrypted with an AWS Key Management Service (AWS KMS) key.
• You don't have the necessary AWS Identity and Access Management (IAM) permissions policy assigned to you.
• The Amazon S3 bucket doesn't exist. Or, the IAM role that's used to query the data doesn't have the required S3 permissions.
• (For QuickSight accounts that use AWS Organizations) You don't have the necessary service control policies (SCPs) assigned to you. 
• (For Athena accounts that use Lake Formation) Your QuickSight user or group doesn't have AWS Lake Formation permissions. 

Note: Before you begin troubleshooting, make sure that you can access your data in Athena.

Resolution

Note: If you receive errors when you run AWS Command Line Interface (AWS CLI) commands, then see Troubleshoot AWS CLI errors. Also, make sure that you're using the most recent AWS CLI version.

Your QuickSight account doesn't have the required permission to access the Amazon S3 bucket

You receive an error similar to the following:

"An error has been thrown from AWS Athena client. Permission denied on S3 path:sourceErrorMessage: s3:/example bucket/object name"

To gain permission to the S3 bucket, complete the following steps:

1. Open the Amazon QuickSight console.
2. Choose Manage QuickSight.
3. Choose Security & Permissions.
4. Under QuickSight access to AWS Services, choose Manage.
5. From the list of AWS Services, select Amazon S3.
6. Choose Select S3 buckets, and then select the S3 bucket.
7. Choose Write permission for Athena Workgroup, and then choose Finish.
8. Choose Save.

Your data file is encrypted with an AWS KMS key

If your data file is encrypted with an AWS KMS key, then Amazon S3 can deny you access to the data. To resolve this issue, use either the AWS CLI or AWS KMS console to grant your QuickSight service role access to the AWS KMS key. 

Use the AWS CLI

Complete the following steps:

1. Use the IAM console to locate the QuickSight service role ARN.

2. Use the Amazon S3 console to find the AWS KMS key ARN.

3. Go to the bucket that contains your data file.

4. Choose the Overview tab, and then locate KMS key ID.

5. Add the QuickSight service role ARN to the KMS key policy.

6. Run the AWS CLI create-grant command:

   aws kms create-grant --key-id aws_kms_key_arn --grantee-principal quicksight_role_arn --operations Decrypt
   

   Note: Replace aws_kms_key_arn with the ARN of your AWS KMS key and quicksight_role_arn with the ARN of your QuickSight service role.

Use the AWS KMS console

To add the QuickSight service role to the AWS KMS key policy, change the key policy. Then, add the following permissions to the key policy:

{     "Sid": "Allow use of the key",
     "Effect": "Allow",
     "Principal": {
         "AWS": [
              "arn:aws:iam::aws-account-id:role/service-role/aws-quicksight-service-role-v0",
              "arn:aws:iam::aws-account-id:role/service-role/aws-quicksight-s3-consumers-role-v0"
              ]
         },
         "Action": [
            "kms:Decrypt"
               ],
         "Resource": "*"
}

Note: In the preceding policy, make sure that you add the ARN for your QuickSight service roles in the Principal section.

You don't have the necessary IAM permissions policy assigned to you

To check which policies are assigned to you and verify whether the policy restricts your access, complete the following steps:

1. Open the Amazon QuickSight console.
2. Choose Manage QuickSight.
3. Choose Security & Permissions.
4. Choose IAM policy assignments.
5. Check whether there are any IAM policy assignments to access Athena.
6. Verify that the policy doesn't restrict your access to either S3 or Athena.

If a policy restricts your access to either S3 or Athena, then ask your QuickSight administrator to change the policy. If you're the administrator, then deactivate the IAM policy assignment, and edit the policy to include S3 and Athena permissions. For more information, see Setting granular access to AWS services through IAM.

The S3 bucket doesn't exist. Or, the IAM role that's used to query the data doesn't have the required S3 permissions

You receive the following error:

"Unable to verify/create output bucket."

To resolve the preceding error, see How do I resolve the "Unable to verify/create output bucket" error in Amazon Athena?

If the bucket doesn't exist, then add the valid S3 bucket. From the Amazon QuickSight console, select Amazon S3 from the list of AWS Services. Then, select the S3 bucket that's used for the query result location.

You don't have the necessary SCPs assigned to you (Organizations)

Ask the Organizations administrator to check your SCP settings to verify the permissions that are assigned to you. If you're an Organizations administrator, then see Creating, updating, and deleting service control policies.

Your QuickSight user or group doesn't have Lake Formation permissions

For Athena accounts that use Lake Formation, you might receive the following error:

"An error has been thrown from the AWS Athena client. Insufficient permissions to execute the query. Insufficient Lake Formation permission(s)."

To grant Lake Formation permissions for QuickSight, you must be a Lake Formation administrator.

Complete the following steps:

1. Locate the ARN for the QuickSight user or group.

2. Run one of the following AWS CLI commands:

   describe-user

   aws quicksight describe-user  --user-name user_name  --aws-account-id account_id --namespace default
   

   -or-

   describe-group

   aws quicksight describe-group --group-name group_name  --aws-account-id account_id --namespace default
   

   Note: In the preceding commands, replace user_name with your user's name, group_name with your group's name, and account_id with your account's ID.

3. Open the Lake Formation console.

4. Choose Tables.

5. Choose Actions, and then choose Permissions.

6. From the list of Principals, choose Grant.

7. Under SAML and Amazon QuickSight users and groups, enter the QuickSight user or group ARN. For example, arn:aws:quicksight:region:accountId:user/namespace/username.

8. Select the correct data source, and then choose All tables. The table permissions are Select and Describe.

9. Choose Grant.

After you grant permissions, return to the QuickSight console to try to create the dataset again.

Related information

Insufficient permissions when using Athena with Amazon QuickSight

Introducing Amazon QuickSight fine-grained access control over Amazon S3 and Amazon Athena

Turn on fine-grained permissions for Amazon QuickSight authors in AWS Lake Formation