I am currently working on this project Amazon Marketing Cloud Insights on AWS—Operational Guide and everything works fine UNTIL the step where I need to create a dataset (from Athena) in QuickSight.
Here is the error I got :
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://…
When I take a look at my Athena Editor, I also notice the errors in the “recent queries” tab.
There are two:
Access denied when writing to location: s3… (
This query ran against the “default” database, unless qualified by the query.
It should me the other Datalake formation database. Do you know how can I move from one to another?
I’m admin for both AWS and QuickSight accounts and I do everything on my email address.
Hello @Paul_Akcelrod - Thank your query. In addition to what @Max mentioned, did you check whether you have provided access via Lake Formation? In Lake Formation, you manage permissions with a grant/revoke syntax (which will be familiar to business intelligence (BI) developers), rather than defining JSON documents for IAM. When working with QuickSight principals, you will need to use the QuickSight user or group ARN as the Lake Formation principal. A Sample is provided for your reference :
Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”. This will help the community to find guidance and answers to similar question. Thank you!
Hello @sagmukhe, thanks for your reply! I can confirm that I already did it, thanks for asking.
@Max, thank you also for your answer! Here is the bucket policy I applied to the bucket where the query results is located:
{
“Version”: “2012-10-17”,
“Id”: “Policy1678091679361”,
“Statement”: [
{
“Sid”: “All”,
“Effect”: “Allow”,
“Principal”: {
“AWS”: “arn:aws:iam::accountnumber:root”
},
“Action”: “s3:",
“Resource”: "arn:aws:s3:::amc-dev-eu-west-1-accountnumber-athena/”
}
]
}
Does this seem correct to you? I tried again this morning but it didn’t change anything.
Thank you again very much for your help!
Hi @Paul_Akcelrod
I think there is a problem in your IAM policy document, your Action does not look correct. You should grant either “s3:*” or the specific minimum actions required.
FYI after running into this issue myself, the problem was quicksight was missing permissions to the KMS key that encrypted the S3 bucket.
I attached a custom policy onto the “aws-quicksight-service-role-v0” role, granting “kms:Decrypt” and “kms:GenerateDataKey” for the relevant KMS key resource.
Thank you very much @baolsen , I was pretty confident after reading your message but I still get the same error.
Nevertheless, I created a custom policy granting all the relevant KMS ressources to my service-role.
I would like to give you more details about something pretty weird.
When I tried to create a dataset coming from Athena in Quicksight, the Query “Select 1” is launched in Athena.
Here is the details I got : Error details
Access denied when writing to location: s3://amc-dev-eu-west-1-XXX-athena/wfm-demoteam-athenaresults/31318ad4-77a2-46c2-a43c-6ad8064aad6e.csv
This query ran against the “default” database, unless qualified by the query. Please post the error message on our forum or contact customer support with query id.
And, on the other hand, when I wrote the query directly in Athena, it worked.
My point here is : can Quicksight, by default, take into account a bad database? the one by default while all my data is in another one.
If so, what can I do to correct the situation? Has this ever happened to you?
You’re welcome @Paul_Akcelrod .
Obviously its difficult to debug without seeing your env, so I can only give a wide range of suggestions… anyway I had that exact issue where SELECT 1 was failing in Athena when it was issued by Quicksight, but when I ran it in Athena it worked fine. The issue in my case was missing KMS permissions for QS to use both the S3 bucket KMS key and the Glue Catalog KMS key.
Things I can suggest to check:
General AWS Troubleshooting docs for the issue. One odd thing I found is that the Glue table must have a TableType defined in the schema, or Athena will work but QS wont work, for example. I thought I hit gold on that but my tables already had it present =D
QS: Check Athena workgroup is chosen correctly.
Athena: Check that the workgroup is set up correctly and points to the right bucket.
Identity policies: Check what is attached on your QS service role looks right. (Covers S3 and KMS)
Resource policies: Check for a deny anywhere on S3 bucket policy or KMS key policy
SCPs: Are there any blocking SCPs perhaps.
One thing that helped me debug was creating a new Data Source for Athena using the Primary workgroup, and from that create a Custom SQL data set. In the Custom SQL query you can copy-paste a known-good query from Athena, and if it fails to run you might get a little more useful error message.
Well, thank you very much @baolsen I finally did it!
I follow your advices and I have made sure that my database is associated with the primary workgroup.
This seems to have changed the life of quicksight because the connection was immediately made!
Thanks again to all the community
Thank you @baolsen for sharing what has worked for you in the past!
Thanks @Paul_Akcelrod for letting us know that this worked and for marking it as “Solution.”
Could you perhaps log something to add the bit about S3 and Glue Catalog encryption to the troubleshooting docs?
And/or even better…
Since the QS service creates the managed QS roles but doesn’t give itself permissions to the KMS keys it needs; Would be great if the product could be updated to handle this scenario by itself and not need debugging for a custom policy attached to the role.
(Also note the custom policy approach breaks when you try to redeploy permissions from the QS UI as I indicated, so this is probably unsupported/unintended behaviour)
Hi @baolsen! Thanks for your feedback–very helpful! I am passing this along to our Documentation Team, and I have marked this for our PM team as a #feature-request. Thanks again!
