Create Snowflake Datasource with Username & Password (2FA)

Question & Answer
,
19

Sure. Here is the setup of the security integration:

CREATE OR REPLACE SECURITY INTEGRATION QUICKSIGHT
  TYPE = OAUTH
  ENABLED = TRUE
  OAUTH_CLIENT = CUSTOM
  OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
  OAUTH_REDIRECT_URI = 'https://localhost'
  OAUTH_ISSUE_REFRESH_TOKENS = TRUE
  OAUTH_REFRESH_TOKEN_VALIDITY = 7776000;

I can retrieve the client id and client secret with

WITH integration_secrets AS
(
  SELECT parse_json(system$show_oauth_client_secrets ('QUICKSIGHT')) AS secrets
)
SELECT
secrets:"OAUTH_CLIENT_ID"::string AS client_id,
secrets:"OAUTH_CLIENT_SECRET"::string AS client_secret
FROM integration_secrets

and store the client id and client secret in AWS SecretsManager.

I wrote a simple OAuth flow in Python to get a refresh token which allows to get an access token from https://{snowflake_host}/oauth/token-request. The Snowflake part works just fine. I can use the access token to authenticate both with the Python SDK and using SnowSQL.

You cannot call the Snowflake endpoint from QuickSight because the URL ends with /token-request which gives you an “TokenProviderUrl is not a valid URL” when creating the QuickSight data source. I setup an API Gateway in AWS with a URL ending in /token which calls the Snowflake url and forwards the access token.

I could then create the QuickSight dataset with boto3

boto3.client('quicksight').create_data_source(
        AwsAccountId=account_id,
        DataSourceId=datasource_id,
        Name="Snowflake OAuth",
        Type="SNOWFLAKE",
        DataSourceParameters={
            "SnowflakeParameters": {
                "Host": snowflake_host,
                "Database": database_name,
                "Warehouse": snowflake_warehouse,
                "AuthenticationType": "TOKEN",
                "OAuthParameters": {
                    "TokenProviderUrl": token_url,
                    "OAuthScope": "refresh_token",
                }
            }
        },
        Credentials={
            "SecretArn": secret_arn
        }

So far, so good. When I create a data set in QuickSight using the new data source, I see in my logs that QuickSight calls the token provider url. But after about 30 seconds I just get a message "Something went wrong”. I tried variations of names for the access token but it’s hard to guess what’s the matter without any error message.

I hope very much that someone can present a working solution.