Snowflake (Preview)

Reference

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)

Contact
Name	Microsoft
URL	Microsoft LogicApps Support Microsoft Power Automate Support Microsoft Power Apps Support

Connector Metadata
Publisher	Microsoft
Website	https://www.snowflake.com
Privacy policy	https://www.snowflake.com/privacy-policy

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.

Learn how to setup Azure Private Link and Snowflake
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

Navigate to the Microsoft Azure Portal and authenticate.
Navigate to Microsoft Entra ID.
Click on App Registrations.
Click on New Registration.
Enter Snowflake OAuth Resource, or similar value as the Name.
Verify the Supported account types are set to Single Tenant.
Click Register.
Click on Expose an API.
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>
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

Navigate to the Microsoft Azure Portal and authenticate.
Navigate to Azure Active Directory.
Click on App Registrations.
Click on New Registration.
Enter a name for the client such as Snowflake OAuth Client.
Verify the Supported account types are set to Single Tenant.
Click Register.
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.
Click on Certificates & secrets and then New client secret.
Add a description of the secret.
For testing purposes, select long-living secrets but for Production follow your security policies.
Click Add. Copy the secret. This will be known as the <OAUTH_CLIENT_SECRET> in the following steps.
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

Navigate to the Microsoft Azure Portal and authenticate.
Navigate to Azure Active Directory.
Click on App Registrations.
Click on the Snowflake OAuth Resource that you created in Configure the Oauth Resource in Microsoft Entra ID
Click on Endpoints in the Overview interface.
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

We currently do not support duplicate columns when the join command is executed. A workaround would be to add aliases to the duplicated columns.
Other limitations with Virtual Tables are listed here.
Virtual tables are only supported with connections created with 'Service Principal' authentication.
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.

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

Operation ID:: Cancel

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

Operation ID:: GetResults

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

Operation ID:: ExecuteSqlStatement

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

Partilhar via

Snowflake (Preview)

Connector in-depth

Supported Capabilities for Power Automate

Supported Capabilities for Power Apps

Supported Capabilities for Logic Apps

Virtual Network Support

Prerequisites

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

A. Configure the OAuth resource in Microsoft Entra ID

B. Create an OAuth client in Microsoft Entra ID

C. Collect Azure AD information for Snowflake

D. Validate Entra Authorization setup

E. Create a security integration with audiences

F. PowerApps Connector Setup

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)

Customers Using Older release of the Connector

Known issues and limitations

General Limits

Creating a connection

Service principal (Microsoft Entra ID application)

Service principal Delegated Auth (Microsoft Entra ID application)

Default [DEPRECATED]

Throttling Limits

Actions

Cancel the Execution of a Statement

Parameters

Returns

Check the Status and Get Results

Parameters

Returns

Submit SQL Statement for Execution

Parameters

Returns

Recursos adicionais