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

4 minute read
0

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?

Short description

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.

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.

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.

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

  1. Open the Amazon QuickSight console.
  2. Choose Manage QuickSight.
  3. Choose Security & Permissions.
  4. 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.

  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 locate KMS key ID.
  3. 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:


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

AWS OFFICIAL
AWS OFFICIALUpdated 2 years ago
2 Comments

Clicking Show Details under the error message only gives you the following information:

  • region: us-east-1
  • timestamp: 1701362347191
  • requestId: a280c930-ea62-437b-a6d5-583d6b9e5b86

It would be good to include instructions for how to view the request and the actual SQL error it produced.

replied 4 months ago

Thank you for your comment. We'll review and update the Knowledge Center article as needed.

profile pictureAWS
MODERATOR
replied 4 months ago