Create Snowflake Datasource with Username & Password (2FA)

As snowflake is promoting two factor authentication, i am wondering if we still can use username and password to create the snowflake datasource in QuickSight

Hi @Barbara

Welcome to the QuickSight community!

Yes, despite Snowflake’s promotion of two-factor authentication (2FA), you can still use your username and password to create a Snowflake data source in QuickSight. QuickSight offers two connection methods to Snowflake.

Login Credentials: This method involves using your Snowflake username and password.

OAuth Client Credentials: This method uses OAuth for enhanced security.

From the UI, I only see this option. Does this mean that if I want to create a data source through the UI, I must use login credentials?

For OAuth client credentials, can it only be done via the API?

Hi @Barbara

Yes, creating a Snowflake data source currently requires using your Snowflake login credentials (username and password).

OAuth client credentials is available exclusively through the QuickSight API.

Hey!

I’m also anticipating the mandatory MFA change from Snowflake and am implementing the OAuth connection method. Thought it might be helpful to share my progress and ask questions here.

I’m following the user guide for creating an OAuth connection.

Currently defining the SECURITY INTEGRATION in snowflake. Here’s my command so far:

create security integration
  AWS_QUICKSIGHT_OAUTH
  comment = 'Custom Security Integration for AWS Quicksight. '
  type = OAUTH
  oauth_client = CUSTOM
  oauth_client_type = 'CONFIDENTIAL' 
  oauth_redirect_uri = 'https://quicksight.aws.amazon.com/oauth2/callback' 
  oauth_refresh_token_validity = 7776000; 
  pre_authorized_roles_list = ( '<custom_quicksight_role>')
  enabled = 'TRUE'
  ;

select SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('AWS_QUICKSIGHT_OAUTH');

Question:
do I have the correct oauth_redirect_uri? I’m currently going off of chatGPT’s recommendation. I asked it to show me the docs where that URI is listed and it said “trust me bro”, so.

1 Like

If I create the data source using OAuth client credentials, will I be able to see it in the QuickSight UI and proceed with creating datasets, analyses, and dashboards from the UI as well?

yes, im concerning about this as well, what should we set for the oauth_redirect_uri? @Xclipse

Hi @Barbara

Yes, after creating a Snowflake data source using OAuth client credentials via the QuickSight API, the new data source becomes visible in the QuickSight UI.

When you connect to Snowflake with OAuth client credentials, you can create datasets that contain Snowflake data with the QuickSight APIs and in the QuickSight UI.

Please refer to the below documentation this might be helpful for you.

1 Like

@Xclipse can you also speak to the oauth_redirect_uri? what uri should we use?

1 Like

Hi @e451-timothy

In the context of establishing an OAuth client credentials connection between QuickSight and Snowflake, the oauth_redirect_uri parameter is not utilized. This is because the client credentials grant type is designed for machine-to-machine authentication and does not involve user interaction or redirection. Therefore, you don’t need to specify a redirect URI when setting up this type of connection.

For detailed guidance on configuring OAuth client credentials between QuickSight and Snowflake, please refer to the below official AWS documentation.

got it, thanks for clarifying!

So, here’s my security integration statement.

create security integration
  AWS_QUICKSIGHT_OAUTH
  comment = 'Custom Security Integration for AWS Quicksight. '
  type = OAUTH
  oauth_client = CUSTOM
  oauth_client_type = 'CONFIDENTIAL' 
  oauth_redirect_uri = 'https://localhost:8000/'
  oauth_refresh_token_validity = 7776000; 
  pre_authorized_roles_list = ( '<custom_quicksight_role>')
  enabled = 'TRUE'
  ;

select SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('AWS_QUICKSIGHT_OAUTH');

I’ve now moved on to adding the items to Secrets Manager and making the UpdateDataSource request. I’ll follow up with my completed request when I have it.

Hi @Xclipse

I’m facing this error “An error occurred (InvalidParameterValueException) when calling the CreateDataSource operation: TokenProviderUrl is not a valid URL”

Do you have any idea what causing the error?
I am using Snowflake OAuth to create the security integration.
I am thinking if we should use External OAuth(ex. OKTA) instead.

CREATE SECURITY INTEGRATION xxx
TYPE = OAUTH
ENABLED = TRUE
OAUTH_CLIENT = CUSTOM
OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
OAUTH_REDIRECT_URI = 'https://dummy.com'
OAUTH_ISSUE_REFRESH_TOKENS = TRUE
OAUTH_REFRESH_TOKEN_VALIDITY = 86400;

Below is my API

client = boto3.client('quicksight', region_name="XXX")  
    response = client.create_data_source(
        AwsAccountId="XXX",
        DataSourceId="XXX",
        Name="TestDataSource",
        Type="SNOWFLAKE",
        DataSourceParameters={
            "SnowflakeParameters": {
                "Host": "XXX.XXX.snowflakecomputing.com",
                "Database": "XXX",
                "Warehouse": "XXX",
                "AuthenticationType": "TOKEN",
                "DatabaseAccessControlRole": "XXX",
                "OAuthParameters": {
                    "TokenProviderUrl": "https://XXX.XXX.snowflakecomputing.com/oauth/token-request"
                }
            }
        },
        Permissions=[  
            {
                "Principal": "XXX",
                "Actions": [ 
                    "quicksight:DescribeDataSource",
                    "quicksight:DescribeDataSourcePermissions",
                    "quicksight:PassDataSource",
                    "quicksight:UpdateDataSourcePermissions",
                    "quicksight:UpdateDataSource",
                    "quicksight:DeleteDataSource"
                ]
            }
        ],
        Credentials={
            "SecretArn": "arn:aws:secretsmanager:XXX"
        }
    )

@Xclipse I’m also having issues with the CreateDataSource operation.

Here is my API script with boto3:

response = client.create_data_source(
    AwsAccountId= 'XXX',
    DataSourceId= 'XXX',
    Name='OAuth Connection Test',
    Type='SNOWFLAKE',
    DataSourceParameters={
        'SnowflakeParameters': {
            'Host': 'xxx.xxx.snowflakecomputing.com',
            'Database': 'XXX',
            'Warehouse': 'XXX',
            'AuthenticationType': 'TOKEN',
            'DatabaseAccessControlRole': 'REPORTER',
            'OAuthParameters': {
            'TokenProviderUrl': 'https://xxx.xxx.snowflakecomputing.com/oauth/token-request'
        },
        }
    },
    Credentials={
        'SecretArn': 'arn:aws:secretsmanager:us-east-1:XXXXX:secret:prod/quicksight-5lHVHC'
    }
)

I then get these errors.

Unknown parameter in DataSourceParameters.SnowflakeParameters: "AuthenticationType", must be one of: Host, Database, Warehouse
Unknown parameter in DataSourceParameters.SnowflakeParameters: "DatabaseAccessControlRole", must be one of: Host, Database, Warehouse
Unknown parameter in DataSourceParameters.SnowflakeParameters: "OAuthParameters", must be one of: Host, Database, Warehouse

I searched around and found this thread that says “AuthenticationType” is not a valid param for DataSourceParameters and to move it to the Credentials object. So I did this:

response = client.create_data_source(
    AwsAccountId= 'XXX',
    DataSourceId= 'XXX',
    Name='OAuth Connection Test',
    Type='SNOWFLAKE',
    DataSourceParameters={
        'SnowflakeParameters': {
            'Host': 'XXX.XXX.snowflakecomputing.com',
            'Database': 'XXX',
            'Warehouse': 'XXX'
        }
    },
    Credentials={
        'AuthenticationType': 'XXX',
        'DatabaseAccessControlRole': 'XXX',
        'OAuthParameters': {
            'TokenProviderUrl': 'https://XXX.XXX.snowflakecomputing.com/oauth/token-request'
        },
        'SecretArn': 'arn:aws:secretsmanager:us-east-1:XXX:secret:prod/quicksight-5lHVHC'
    }
)

And got this:

Unknown parameter in Credentials: "AuthenticationType", must be one of: CredentialPair, CopySourceArn, SecretArn
Unknown parameter in Credentials: "DatabaseAccessControlRole", must be one of: CredentialPair, CopySourceArn, SecretArn
Unknown parameter in Credentials: "OAuthParameters", must be one of: CredentialPair, CopySourceArn, SecretArn

Ok, another update from me.

  1. I wasn’t able to resolve the “Unknown parameter” error, but an admin with our AWS account was. They then got the “TokenProviderUrl is not a valid URL” error that @Barbara first found.
  2. The admin also connected QuickSight to AWS Secrets.
  3. We had been using the /oauth/token-request path for the TokenProviderUrl param. We tried /oauth/token instead and got a new error.
"ErrorInfo": {
          "Type": "GENERIC_SQL_FAILURE",
          "Message": "A JSONObject text must begin with '{' at 1 [character 2 line 1]"
     },

So it seems that Quicksight only supports the token endpoint as a valid URL, but doesn’t handle what that endpoint returns.

So we’ve also created a AWS support case.

1 Like

So it seems that Snowflake Oauth is not supported at this time. AWS Support has suggested using an external Oauth like Okta, but that’s another paid solution that we’re not interested in implementing. We’ve asked for alternatives and are awaiting a response.

1 Like

Hi there,

I’m also looking into integrating QuickSight with Snowflake using OAuth and came across your post. Just wanted to check in—have you received any updates from AWS regarding alternative solutions that don’t require an external provider like Okta?

Would really appreciate any insight you can share.

Thanks!

Hello,

I face the same issues with QuickSight OAuth to Snowflake. I also first stumbled into the “TokenProviderUrl is not a valid URL” error and eventually found the solution by trial and error. I managed to create the QuickSight data source using Snowflake itself as identity provider. I can connect to Snowflake via OAuth using snowsql. However, when I try to create a dataset in QuickSight using the data source with OAuth, I only see a message “Something went wrong”.

There are no error messages that would allow me to understand why QuickSight fails to use the access token. I also didn’t find any useful documentation besides the page @e451-timothy mentioned above.

Please let us know if you can make it work!

Hi there,

Thanks for sharing your progress!

Out of curiosity, did you try configuring Delegated Authorization as described in Snowflake’s OAuth consent documentation?

I’m also trying to get this working with QuickSight and would be really interested to know more about the details of your setup—specifically:

  • The SQL you used to create the OAuth integration
  • The structure of the secret you stored (excluding any sensitive data, of course)
  • Whether you used any API calls to assist with the setup

Would really appreciate any insight you can share!

Thanks!

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.