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

Last updated: 2022-07-29

I'm getting "Access denied" errors when I try to use Amazon Athena as a data source in my Amazon QuickSight account. How do I resolve this?

Short description

The following are common reasons for getting 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.
  • You don't have the necessary service control policies (SCPs) assigned to you. (For QuickSight accounts that use AWS Organizations)
  • Your QuickSight user or group doesn't have AWS Lake Formation permissions. (For Athena accounts that use Lake Formation)

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

Resolution

If you receive errors when running AWS Command Line Interface (AWS CLI) commands, 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 select the S3 bucket that you want to access from QuickSight.
  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, grant your QuickSight service role access to the AWS KMS key. Complete the following steps.

Using the AWS CLI

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.
        Go to the bucket that contains your data file.
        Choose the Overview tab, and locate KMS key ID.

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

4.    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.

Using the AWS KMS console

To add the QuickSight service role to the AWS KMS key policy, follow the instructions on How to change a key policy. Then, edit the key policy by adding the following permissions 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: Replace aws_account_id with your AWS account's ID.

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

You can also receive an Access denied error if you don't have the necessary IAM permissions policy assigned to you. To check which policies are assigned to you, 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 if 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 there's a policy that 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

If you use AWS Organizations, you can receive an Access denied error when you don't have the necessary SCPs assigned to you. Ask the AWS Organizations administrator to check your SCP settings to verify the permissions that are assigned to you. If you're an AWS Organizations administrator 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.

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

2.    Open the Lake Formation console.

3.    Choose Tables.

4.    Choose Actions, and then choose Permissions.

5.    From the list of Principals, choose Grant.

6.    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

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

8.    Choose Grant.

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