Snowflake (Preview)

Snowflake Connector allows you to build canvas apps and surface Snowflake data in Virtual Tables, while also enabling faster data processing and analytics compared to traditional solutions.

This connector is available in the following products and regions:

Connector in-depth

This article describes the capabilities and actions of the Snowflake connector.

Supported Capabilities for Power Automate

• Users can create flows and add actions to execute and get back results of custom SQL statements with the snowflake connection.

Supported Capabilities for Power Apps

• Users can create virtual tables and add canvas apps with the snowflake connection (connection created using only 'Service Principal authentication'). Learn how to create Virtual tables: Create and edit virtual tables with Microsoft Dataverse - Power Apps | Microsoft Learn

Supported Capabilities for Logic Apps

• Users can create flows and add actions to execute and get back results of custom SQL statements with the snowflake connection.

Virtual Network Support

With Azure Virtual Network support for Power Platform, users can integrate Power Platform with resources inside your virtual network without exposing them over the public internet. To connect to Virtual Network, please make sure to follow both steps mentioned below.

1. Learn how to setup Azure Private Link and Snowflake
2. Learn how to setup Virtual Network support for Power Platform

To know more about Virtual network, please check Virtual Network support overview.

Prerequisites

• Users must have Microsoft Entra ID for the external authorization. The authorization flow for the PowerApps leverages the Service-Principal, and Power Automate will support both Service-Principal and on-behalf-of-user flow.
• Users must have a premium Power Apps license.
• Users must have a Snowflake account.

Snowflake Configuration Steps: (documented with screenshots in the link here)

A. Configure the OAuth resource in Microsoft Entra ID

1. Navigate to the Microsoft Azure Portal and authenticate.
2. Navigate to Microsoft Entra ID.
3. Click on App Registrations.
4. Click on New Registration.
5. Enter Snowflake OAuth Resource, or similar value as the Name.
6. Verify the Supported account types are set to Single Tenant.
7. Click Register.
8. Click on Expose an API.
9. Click on the Set link next to Application ID URI to set the Application ID URI. Application ID URI will be of the format Application ID URI <api://9xxxxxxxxxxxxxxxxxx>
10. To add a Snowflake Role as a Role for OAuth flows where the programmatic client requests an access token for itself:

    1. click on Manifest.

    2. Locate the appRoles element.

    3. Enter an App Role with the following settings, the Snowflake role should be the one which has access to a warehouse, and usage privileges on the schema (check here for details on manifest vales)

    4. See the sample definition below:
       
       The App Role manifests as follows.
       

        "appRoles":[
            {
                "allowedMemberTypes": [ "Application" ],
                "description": "Account Administrator.",
                "displayName": "Account Admin",
                "id": "3ea51f40-2ad7-4e79-aa18-12c45156dc6a",
                "isEnabled": true,
                "lang": null,
                "origin": "Application",
                "value": "session:role:analyst"
            }
        ]
       

B. Create an OAuth client in Microsoft Entra ID

1. Navigate to the Microsoft Azure Portal and authenticate.
2. Navigate to Azure Active Directory.
3. Click on App Registrations.
4. Click on New Registration.
5. Enter a name for the client such as Snowflake OAuth Client.
6. Verify the Supported account types are set to Single Tenant.
7. Click Register.
8. In the Overview section, copy the ClientID from the Application (client) ID field. This will be known as the <OAUTH_CLIENT_ID> in the following steps.
9. Click on Certificates & secrets and then New client secret.
10. Add a description of the secret.
11. For testing purposes, select long-living secrets but for Production follow your security policies.
12. Click Add. Copy the secret. This will be known as the <OAUTH_CLIENT_SECRET> in the following steps.
13. For programmatic clients that will request an Access Token for themselves, configure API permissions for Applications as follows.
    a. Click on API Permissions.
    b. Click on Add Permission. c. Click on My APIs. d. Click on the Snowflake OAuth Resource that you created in Configure the Oauth Resource in Microsoft Entra ID
    e. Click on the Application Permissions. f. Check on the Permission related to the Roles manually defined in the Manifest of the Application that you wish to grant to this client.
    g. Click Add Permissions.
    h. Click on the Grant Admin Consent button to grant the permissions to the client. Note that for testing purposes, permissions are configured this way. However, in a production environment, granting permissions in this manner is not advisable.
    i. Click Yes.

C. Collect Azure AD information for Snowflake

1. Navigate to the Microsoft Azure Portal and authenticate.
2. Navigate to Azure Active Directory.
3. Click on App Registrations.
4. Click on the Snowflake OAuth Resource that you created in Configure the Oauth Resource in Microsoft Entra ID
5. Click on Endpoints in the Overview interface.
6. On the right-hand side, copy the OAuth 2.0 token endpoint (v2) and note the URLs for OpenID Connect metadata and Federation Connect metadata.

• The OAuth 2.0 token endpoint (v2) will be known as the <AZURE_AD_OAUTH_TOKEN_ENDPOINT> in the following configuration steps. The endpoint should be similar to https://login.microsoftonline.com/<tenant-id>/oauth2/v2.0/token.
• For the OpenID Connect metadata, open in a new browser window.
  • Locate the jwks_uri parameter and copy its value.
  • This parameter value will be known as the <AZURE_AD_JWS_KEY_ENDPOINT> in the following configuration steps. The endpoint should be similar to https://login.microsoftonline.com/<tenant-id>/discovery/v2.0/keys.
• For the Federation metadata document, open the URL in a new browser window.
  • Locate the "entityID" parameter in the XML Root Element and copy its value.
  • This parameter value will be known as the <AZURE_AD_ISSUER> in the following configuration steps. The entityID value should be similar to https://sts.windows.net/<tenant-id>/.

D. Validate Entra Authorization setup

If you intend to test your configuration at this time, please use curl and check if you are able to get an authorization and a bearer token.

    curl -X POST -H "Content-Type: application/x-www-form-urlencoded;charset=UTF-8" \ --data-urlencode "client_id=client_id from B.8> above" \ --data-urlencode "client_secret=<Secret from B.12 above" \ --data-urlencode "grant_type=client_credentials" \ --data-urlencode "scope=api://<Appl_URI_ID from A.9>/.default" \'https://login.microsoftonline.com/<tenant_id>/oauth2/v2.0/token'

You can copy the output from above step in https://jwt.ms/ and see if the token is valid

E. Create a security integration with audiences

The external_oauth_audience_list parameter of the security integration must match the Application ID URI that you specified while configuring Microsoft Entra ID.

create security integration external_oauth_azure_2

 type = external_oauth
     enabled = true
     external_oauth_type = azure
     external_oauth_issuer = '<AZURE_AD_ISSUER>'
     external_oauth_jws_keys_url = '<AZURE_AD_JWS_KEY_ENDPOINT>'
     external_oauth_audience_list = ('<SNOWFLAKE_APPLICATION_ID_URI>')
     external_oauth_token_user_mapping_claim = ‘sub’ 
     external_oauth_snowflake_user_mapping_attribute = 'login_name'; 

Now, you can test the entire config by using SnowSQL to see if the connection is established
Snowsql -a ijaxxxx -u ‘sub-value’ -r <snowflake-role from B.10 above> –authenticator oauth –token "output from curl at step.D"

F. PowerApps Connector Setup

You can now configure the connector below with the appropriate values from above. The connector connects only to one database at this time, if you have Apps that need to access multiple databases, you may need to create multiple Connect

• Snowflake SaaS URL – organization.account.snowflakecomputing.com (strip https)
• Snowflake database – Database whose tables will be accessed in PowerApps
• Warehouse name – Snowflake Warehouse which the role has USAGE privileges
• Role – Snowflake Role assigned to the Service Account role.
• Schema – Schema which has Tables that PowerApps needs access
• Tenant ID – Microsoft Entra ID Tenant
• Client ID – Entra Client_ID for the PowerApps client from B.8 above
• Client Secret – Entra Client_Secret for the PowerApps client from B.12 above
• Resource URL – Entra Resource Application ID from A.9 (strip api://)

Configuration Steps for Delegated on-behalf-of access can be found here (also need to ALTER your Snowflake security Integration to include 'upn' claim as per below)

• ALTER SECURITY INTEGRATION external_oauth_azure_2 set external_oauth_token_user_mapping_claim = ('sub','upn')
• Add https://global.consent.azure-apim.net/redirect/snowflakev2 as redirect_url for the Azure Client

Customers Using Older release of the Connector

• Snowflake has long offered an API-based connector. However, this new connector is an upgrade and replacement to the existing connector. If you have built Power Automate flows using the prior preview release of the connector, you need to set up a new connection following the steps documented here, and update the existing flows to use the new connection. You would also need to drop the "Convert result set rows from array to objects" as that functionality is now wrapped in "Check the Status and Get Results".

Known issues and limitations

1. We currently do not support duplicate columns when the join command is executed. A workaround would be to add aliases to the duplicated columns.
2. Other limitations with Virtual Tables are listed here.
3. Virtual tables are only supported with connections created with 'Service Principal' authentication.
4. When using Service Principle authentication, the user needs to have Read access to the information_schema.columns table.

General Limits

Creating a connection

The connector supports the following authentication types:

Service principal (Microsoft Entra ID application)

Auth ID: oauthSP

Applicable: All regions

Use Microsoft Entra ID application to access your Snowflake database.

This is shareable connection. If the power app is shared with another user, connection is shared as well. For more information, please see the Connectors overview for canvas apps - Power Apps | Microsoft Docs

Service principal Delegated Auth (Microsoft Entra ID application)

Auth ID: oauthSPUserDelegated

Applicable: All regions

Use Microsoft Entra ID application to access your Snowflake database.

This is shareable connection. If the power app is shared with another user, connection is shared as well. For more information, please see the Connectors overview for canvas apps - Power Apps | Microsoft Docs

Default [DEPRECATED]

Applicable: All regions

This option is only for older connections without an explicit authentication type, and is only provided for backward compatibility.

This is not shareable connection. If the power app is shared with another user, another user will be prompted to create new connection explicitly.

Throttling Limits

Actions

Cancel the Execution of a Statement

Check the Status and Get Results

Submit SQL Statement for Execution