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 should first create virtual tables and then load them into apps with the Snowflake connection (a 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 their 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 PowerApps leverages the Service-Principal, and Power Automate will support both Service-Principal and on-behalf-of-user flows.
Users must have a premium Power Apps license.
Users must have a Snowflake account.

A few things that must be kept in mind regarding configuration for using the Snowflake connector

The authorization server can grant the OAuth client an access token on behalf of the user, referred to DELEGATED BASED AUTH.
The authorization server can grant the OAuth client an access token for the OAuth client itself, referred as SP BASED AUTH.
For Oauth client, make sure to add a Redirect URI (Web based) for delegated based AUTH.
Redirect URI - https://global.consent.azure-apim.net/redirect/snowflakev2
A security integration with audiences must be created.
For delegated based authentication, external_oauth_token_user_mapping_claim = 'upn'
For sp based authentication, external_oauth_token_user_mapping_claim = 'sub'
At time of creating the security integration, describe the integration created and check if the role given to the user of Snowflake is in the blocked list or not. If in the blocked list, then either change or remove the role of the user in the blocked list.
Ensure that the login_name and roles are correctly set in the Snowflake account. This can be checked via Admin Tab > Users and Roles > Select a user and Edit the user details.

Configuration Steps

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 link next to Application ID URI to add the Application ID URI. Application ID URI will be of the format Application ID URI <api://9xxxxxxxxxxxxxxxxxx>
For Delegated Auth (screenshots here):
1. Click on Add a Scope to add a scope representing the Snowflake role.
2. Select who can consent.
3. Add a description.
4. Click Add Scope to save.
  Example: session:scope:analyst
  
  OR
For Service Principal Auth (screenshots here):
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. Avoid using high-privilege roles like ACCOUNTADMIN, SECURITYADMIN or ORGADMIN.
```
 "appRoles":[
     {
         "allowedMemberTypes": [ "Application" ],
         "description": "Analyst.",
         "displayName": "Analyst",
         "id": "3ea51f40-2ad7-4e79-aa18-12c45156dc6a",
         "isEnabled": true,
         "lang": null,
         "origin": "Application",
         "value": "session:role:analyst"
     }
 ]
```
5. Click Save

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 necessary security policies.
Click Add. Copy the secret. This will be known as the <OAUTH_CLIENT_SECRET> in the following steps.
For Delegated Auth:
a. Click on Manage -> API Permissions.
b. Click on Add Permission.
c. Click on My APIs.
d. Click on the Snowflake OAuth Resource that was created in Configure the Oauth Resource in Microsoft Entra ID
e. Click on the Delegated Permissions box.
f. Check on the Permission related to the Scopes manually defined in the Application that are wished to be granted 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.
j. Click Manage -> Authentication, add a platform - > Web and enter Redirect URI's
https://global.consent.azure-apim.net/redirect/snowflakev2

OR
For Service Principal Auth:
a. Click on Manage -> API Permissions.
b. Click on Add Permission.
c. Click on My APIs.
d. Click on the Snowflake OAuth Resource that was created in Configure the Oauth Resource in Microsoft Entra ID .
e. Click on the Application Permissions box.
f. Check on the Permission related to the Roles manually defined in the Manifest of the Application that are wished to be granted 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 was 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

It is recommended the configuration be tested at this time, please use the curl below and check if Entra is issuing a token using any API testing tool such as Insomnia or others.

Delegated Auth: (Optional)

A prior step must be executed to get the code, this document can be followed

curl --request POST --url https://login.microsoftonline.com/<TENANT_ID>/oauth2/token --header 'Content-Type: multipart/form-data' --form client_id=<AAD_CLIENT_ID> --form client_secret=< AAD_CLIENT_SECRET> --form resource=< AAD_RESOURCE_ID> --formgrant_type=authorization_code --form code=<CODE_GENERATED_ABOVE> --form redirect_uri=https://localhost

Note: Please add the redirect URI in the AAD client app.

Service Principal Auth:

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

To validate the token, execute the below command in Snowflake:

select system$verify_external_oauth_token(‘<token>’);

E. Create a security integration with audiences

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

Delegated Auth:

create security integration external_oauth_azure_1    
    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 = ‘upn’ 
    external_oauth_snowflake_user_mapping_attribute = 'login_name or email address';

If Security Integration for Azure AD is already set up, then use:

alter security integration external_oauth_azure_1 set external_oauth_token_user_mapping_claim = ('sub','upn');

In the case of Delegated Authentication, the Snowflake user’s login_name or email_address should match the Entra email of the user who will run the Power Automate flow.

Example:

ALTER USER SNOWSQL_DELEGATE_USER  
LOGIN_NAME = '<ENTRA-USERID>' or EMAIL_ADDRESS = ‘ENTRA-USERID’ 
DISPLAY_NAME = 'SnowSQL Delegated User'  
COMMENT = 'A delegate user for SnowSQL client to be used for OAuth based connectivity';

Service Principal Auth:

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';

Continue below for Service Principal Authentication setup only.

Create a user for Service Principal based connection:

The subvalue should be mapped to a user in Snowflake, avoid using high privilege accounts Accountadmin, Orgadmin, Securityadmin.

CREATE OR REPLACE USER SNOWSQL_OAUTH_USER  
LOGIN_NAME = '<subvalue from decoded token>'  
DISPLAY_NAME = 'SnowSQL OAuth User'  
COMMENT = 'A system user for SnowSQL client to be used for OAuth based connectivity'; 

CREATE ROLE ANALYST; 

GRANT ROLE ANALYST TO USER SNOWSQL_OAUTH_USER;

F. Validate Snowflake Access [Optional]

Delegated Auth

snowsql -a organization-locator -u 'user@sandbox.onmicrosoft.com' --rolename <rolename> --authenticator oauth --token "<token-value>"

Service Principal Auth

snowsql -a <snowflake-accountname> -u ‘sub-value’ -r <snowflake-role from A.11.h above> –authenticator oauth –token <output from curl at step D>

Customers using Snowflake Connector [DEPRECATED]

Applicable: All regions

To migrate from an older Snowflake connector to the new one, please follow the steps below.

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

If a Power Automate flow using a prior connectorhas been built (now marked as deprecated), a new connection will need to be set up following the steps documented under Configuration Steps above, and update the existing flows to use the new connection.

The action "Convert result set rows from array to objects" would also need to be dropped 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.
Snowflake connections cannot be created directly in Canvas apps, error information and steps which are needed to resolve the issue are as follows:
1. An error will show if the Snowflake connection is created directly in a Canvas app as shown in the below screenshot
2. Instead of adding the connector directly in the Canvas app, create a service principal connection (not delegated) from outside of the Canvas app
3. Use the Snowflake connection created above and create a virtual table
4. Afterwards, the virtual table can be loaded in the Canvas app and build out of the Canvas app can proceed
5. The ANIMALS table above is a virtual table, created using the Snowflake Connection as mentioned above

Note

Users always need to make sure that all of the Snowflake account details (warehouse, role, schema, database) are in the same letter case as the Snowflake account while configuring the connection/flow-actions.
As a validation step for both Delegated and Service Principal based connections, please create a Power Automate flow to validate the connection.

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

Поділитися через

Snowflake (Preview)

Connector in-depth

Supported Capabilities for Power Automate

Supported Capabilities for Power Apps

Supported Capabilities for Logic Apps

Virtual Network Support

Prerequisites

A few things that must be kept in mind regarding configuration for using the Snowflake connector

Configuration Steps

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. Validate Snowflake Access [Optional]

Customers using Snowflake Connector [DEPRECATED]

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

Додаткові ресурси