Dela via


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:

Service Class Regions
Logic Apps Standard All Logic Apps regions except the following:
     -   Azure Government regions
     -   Azure China regions
     -   US Department of Defense (DoD)
Power Automate Premium All Power Automate regions except the following:
     -   US Government (GCC)
     -   US Government (GCC High)
     -   China Cloud operated by 21Vianet
     -   US Department of Defense (DoD)
Power Apps Premium All Power Apps regions except the following:
     -   US Government (GCC)
     -   US Government (GCC High)
     -   China Cloud operated by 21Vianet
     -   US Department of Defense (DoD)
Connector Metadata
Publisher Microsoft

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

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.

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://)

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

Name Value
Maximum number of requests being processed by the connector concurrently 50

Creating a connection

The connector supports the following authentication types:

Service principal (Microsoft Entra ID application) Use Microsoft Entra ID application to access your Snowflake database. All regions Shareable
Service principal Delegated Auth (Microsoft Entra ID application) Use Microsoft Entra ID application to access your Snowflake database. All regions Shareable
Default [DEPRECATED] This option is only for older connections without an explicit authentication type, and is only provided for backward compatibility. All regions Not shareable

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

Name Type Description Required
Tenant string True
Client ID string True
Client Secret securestring True
Resource URL string Snowflake OAuth Audience URL (Resource URL) True
Snowflake SaaS URL string Snowflake URL not including HTTPS prefix (e.g., fnpuupu-in12345.snowflakecomputing.com) True
Snowflake database string Specify the database to connect to True
Warehouse name string Snowflake warehouse to connect to
Role string Snowflake role to connect as
Schema string Snowflake schema to connect to

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

Name Type Description Required
Client ID string Snowflake OAuth Client ID True
Client Secret securestring Snowflake OAuth Client Secret True
Resource URL string Snowflake OAuth Audience URL(Resource URL) True

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

Name Calls Renewal Period
API calls per connection 900 60 seconds

Actions

Cancel the Execution of a Statement

Cancel the Execution of a Statement

Check the Status and Get Results

Check the Status of the Execution of a Statement and Get the Results

Submit SQL Statement for Execution

Submit a SQL Statement for Execution

Cancel the Execution of a Statement

Cancel the Execution of a Statement

Parameters

Name Key Required Type Description
Instance
Instance True string

The uri of your Snowflake instance (without https://)

statement handle
statementHandle True string

statement handle

Request ID
requestId string

Request ID

Returns

Name Path Type Description
code
code string

code

sqlState
sqlState string

sqlState

message
message string

message

statementHandle
statementHandle string

statementHandle

statementStatusUrl
statementStatusUrl string

statementStatusUrl

Check the Status and Get Results

Check the Status of the Execution of a Statement and Get the Results

Parameters

Name Key Required Type Description
Instance
Instance True string

The uri of your Snowflake instance (without https://)

statement handle
statementHandle True string

statement handle

Request ID
requestId string

Request ID

partition
partition integer

partition

name
name string

name

database
database string

database

schema
schema string

schema

table
table string

table

nullable
nullable boolean

nullable

precision
precision integer

precision

scale
scale integer

scale

byteLength
byteLength integer

byteLength

collation
collation string

collation

length
length integer

length

type
type string

type

Returns

Name Path Type Description
Partitions
Partitions array of object

Partitions

RowCount
Partitions.RowCount integer

RowCount

UncompressedSize
Partitions.UncompressedSize integer

UncompressedSize

CompressedSize
Partitions.CompressedSize integer

CompressedSize

Schema
Schema array of object

Schema

name
Schema.name string

name

database
Schema.database string

database

schema
Schema.schema string

schema

table
Schema.table string

table

precision
Schema.precision integer

precision

scale
Schema.scale integer

scale

type
Schema.type string

type

nullable
Schema.nullable boolean

nullable

byteLength
Schema.byteLength integer

byteLength

collation
Schema.collation string

collation

length
Schema.length integer

length

Data
Data array of

Data

StatementHandle
StatementHandle string

StatementHandle

Rows
Metadata.Rows integer

Rows

Format
Metadata.Format string

Format

Code
Metadata.Code string

Code

StatementStatusUrl
Metadata.StatementStatusUrl string

StatementStatusUrl

RequestId
Metadata.RequestId string

RequestId

SqlState
Metadata.SqlState string

SqlState

CreatedOn
Metadata.CreatedOn string

CreatedOn

StatementHandles
Metadata.StatementHandles array of string

StatementHandles from executing multiple statements

Submit SQL Statement for Execution

Submit a SQL Statement for Execution

Parameters

Name Key Required Type Description
Instance
Instance True string

The uri of your Snowflake instance (without https://)

Request ID
requestId string

The ID of the Request

Asynchronous
asyncexecution boolean

Indicates if the query should be executed asynchronously

Nullable
nullable boolean

If nullable is false, null values will be replaced by string

statement
statement string

The SQL statement to be executed - batches of statements not yet supported

timeout
timeout integer

Number of seconds before timeout occurs

database
database string

database

schema
schema string

schema

warehouse
warehouse string

warehouse

role
role string

role

timezone
timezone string

Time zone to use when executing the statement.

query tag
query_tag string

Query tag that you want to associate with the SQL statement.

binary output format
binary_output_format string

Output format for binary values.

date output format
date_output_format string

Output format for DATE values.

time output format
time_output_format string

Output format for TIME values.

timestamp output format
timestamp_output_format string

Output format for TIMESTAMP values.

timestamp ltz output format
timestamp_ltz_output_format string

Output format for TIMESTAMP_LTZ values.

timestamp_ntz_output_format
timestamp_ntz_output_format string

Output format for TIMESTAMP_NTZ values.

timestamp tz output format
timestamp_tz_output_format string

Output format for TIMESTAMP_TZ values.

multi statement count
MULTI_STATEMENT_COUNT integer

Number of statements to execute when using multi-statement capability. 0 implies variable number of statements. Negative numbers are not allowed.

Returns

Name Path Type Description
Partitions
Partitions array of object

Partitions

RowCount
Partitions.RowCount integer

RowCount

UncompressedSize
Partitions.UncompressedSize integer

UncompressedSize

CompressedSize
Partitions.CompressedSize integer

CompressedSize

Schema
Schema array of object

Schema

name
Schema.name string

name

database
Schema.database string

database

schema
Schema.schema string

schema

table
Schema.table string

table

nullable
Schema.nullable boolean

nullable

precision
Schema.precision integer

precision

scale
Schema.scale integer

scale

byteLength
Schema.byteLength integer

byteLength

collation
Schema.collation string

collation

length
Schema.length integer

length

type
Schema.type string

type

Data
Data array of

Data

Rows
Metadata.Rows integer

Rows

Format
Metadata.Format string

Format

Code
Metadata.Code string

Code

StatementStatusUrl
Metadata.StatementStatusUrl string

StatementStatusUrl

RequestId
Metadata.RequestId string

RequestId

SqlState
Metadata.SqlState string

SqlState

StatementHandle
Metadata.StatementHandle string

StatementHandle

StatementHandles
Metadata.StatementHandles array of string

StatementHandles from executing multiple statements

CreatedOn
Metadata.CreatedOn string

CreatedOn