How do I resolve SQL exception errors with custom SQL data sources in QuickSight?
Last updated: 2022-07-25
I'm trying to use custom SQL data sources in Amazon QuickSight, but I get the error message "Your database generated a SQL exception." How do I resolve this?
You receive the following error message when Amazon QuickSight is querying or refreshing your SQL data source:
"Your database generated a SQL exception. This can be caused by query timeouts, resource constraints, unexpected DDL alterations before or during a query, and other database errors. Check your database settings and your query, and try again."
For more detailed information on what caused the error, choose Show Details under the error message.
Common reasons for receiving the error message include:
- The query times out.
- There's an issue with the VPC connection to your data source.
- Your QuickSight account doesn't have permission to access the data.
- Your QuickSight service role doesn't have permission to access the AWS managed Key Management Service (AWS KMS) key.
- You're using unsupported data types or functions.
The query times out
If the custom SQL query times out, simplify the query to optimize runtime. For other query timeout solutions, see How do I resolve query timeout issues in QuickSight?
There's an issue with the VPC connection to your data source
The details of your error message include the following:
Communications link failure The last packet successfully received from the server was nnnn milliseconds ago. The last packet sent successfully to the server was nnnn milliseconds ago.
Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
If you're experiencing VPC connection issues to your data sources, check the network security group in the VPC that's associated with the resource. For more information see, Connecting to a VPC with Amazon QuickSight.
Your QuickSight account doesn't have permission to access the data
If you experience an SQL exception error when trying to access data in an AWS service, check your QuickSight security and permissions settings.
- Open the Amazon QuickSight console.
- Choose Manage QuickSight.
- Choose Security & Permissions.
- Configure access to the supported services that you use.
If you use AWS Organizations, you can receive the error when you don't have the necessary service control policies (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 service role doesn't have permission to access the AWS managed KMS key
You receive the following error:
If you are encrypting query results with KMS key, please ensure you are allowed to access your KMS key.
Make sure that the QuickSight service role has the correct AWS KMS key permissions.
- Use the AWS Identity and Access Management (IAM) console to locate the QuickSight service role ARN.
- Use the Amazon Simple Storage Service (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.
- Add the QuickSight service role ARN to the KMS key policy.
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.
You're using unsupported data types or functions
If you try to import an unsupported data type or use an unsupported SQL function, you receive an SQL exception error. To resolve this issue, check the SQL data source to determine if the data type or SQL function is supported.
To see what's supported, check the following resources: