How can I troubleshoot connect errors when connecting a Amazon OpenSearch Service domain to QuickSight?

Last updated: 2022-07-25

I'm trying to connect my Amazon OpenSearch Service domain to Amazon QuickSight. However, I received an error. How can I resolve this?

Short description

To connect QuickSight to your Amazon OpenSearch domain, make sure that:

  • Your domain is version 7.7 or higher.
  • Your OpenSearch version is 1.0 or higher.
  • Your OpenSearch Service domain is in the same AWS Region and account as QuickSight.

Note: Amazon OpenSearch Service supports OpenSearch and legacy Elasticsearch OSS up to 7.10.

Resolution

Follow these troubleshooting steps for your use case.

Unsupported engine version error

When connecting to an OpenSearch Service domain, you might receive the following error:

"Your database uses an unsupported engine version. Please review supported versions."

This error occurs when the compatibility mode is not enabled on the OpenSearch Service domain. OpenSearch version 1.0 introduces a domain setting named Enable compatibility mode. Some OpenSearch Service clients and plugins check the cluster version before connecting. Compatibility mode sets OpenSearch Service to report its version as 7.10 so that these clients continue to work. When compatible mode is enabled, QuickSight identifies the correct version and the connection is established successfully.

To enable compatibility mode, run the following command:

PUT /_cluster/settings
{
     "persistent": {
          "compatibility.override_main_response_version": true
     }
}

Cursor not enabled or unable to prepare this table error

When importing the OpenSearch Service domain data into SPICE, you might receive one of the following errors:

Can't perform this Action. The domain X has not enabled cursor.

-or-

Unable to prepare this table

To enable SQL cursor in your OpenSearch Service domain, run the following command:

PUT _opendistro/_sql/settings
{
     "transient": {
          "opendistro.sql.cursor.enabled": "true"
     },
     "persistent": {
          "opendistro.sql.cursor.enabled": "true"
     }
}

Connection error forbidden

If you have fine-grained access control enabled on OpenSearch Dashboards in your OpenSearch Service domain, you might receive the following error:

GENERAL_SQL_EXCEPTION. Connection error forbidden

This error occurs because the QuickSight service role isn't mapped to an OpenSearch Service role.

To resolve this error, follow the instructions to add permissions to a domain to allow access from QuickSight.