How do I resolve SQL exception errors with custom SQL data sources in QuickSight?

Short description

You receive the following error message when Amazon QuickSight queries or refreshes 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 about what caused the error, choose Show Details under the error message.

The following are common reasons why you receive this error message:

• The query times out.
• There's an issue with the virtual private cloud (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 used unsupported data types or functions.

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.

The query times out

If the custom SQL query times out, then 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

You see one of the following detailed error messages:

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

-or-

"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 experience VPC connection issues to your data sources, then 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 you try to access data in an AWS service, then check your QuickSight security and permissions settings.

Complete the following steps:

1. Open the Amazon QuickSight console.
2. Choose Manage QuickSight.
3. Choose Security & Permissions.
4. Configure access to the supported data sources that you use.

If you use AWS Organizations, then you can receive the error when the necessary service control policies (SCPs) aren't 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, then 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 error: "If you are encrypting query results with KMS key, please ensure you are allowed to access your KMS key."

To confirm that the QuickSight service role has the correct AWS KMS key permissions, complete the following steps:

1. Use the AWS Identity and Access Management (IAM) console to locate the QuickSight service role ARN.
2. 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 then locate the KMS key ID.
3. To 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 used unsupported data types or functions

If you try to import an unsupported data type or use an unsupported SQL function, then 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:

• Supported data types from other data sources
• Supported data types and values
• Functions by category

Related information

Quotas for direct SQL queries

How can I create a private connection from Amazon QuickSight to an Amazon Redshift cluster or an Amazon Relational Database Service (Amazon RDS) DB instance that's in a private subnet?

Actions, resources, and condition keys for Amazon QuickSight