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) |
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):
- Click on Add a Scope to add a scope representing the Snowflake role.
- Select who can consent.
- Add a description.
- 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:Click on Manifest.
Locate the
appRoles
element.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).
See the sample definition below:
The App Role manifests as follows. Avoid using high-privilege roles likeACCOUNTADMIN
,SECURITYADMIN
orORGADMIN
."appRoles":[ { "allowedMemberTypes": [ "Application" ], "description": "Analyst.", "displayName": "Analyst", "id": "3ea51f40-2ad7-4e79-aa18-12c45156dc6a", "isEnabled": true, "lang": null, "origin": "Application", "value": "session:role:analyst" } ]
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 tohttps://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 tohttps://login.microsoftonline.com/<tenant-id>/discovery/v2.0/keys
.
- Locate the
- For the Federation metadata document, open the URL in a new browser window.
- Locate the
"entityID"
parameter in theXML 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 tohttps://sts.windows.net/<tenant-id>/
.
- Locate the
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.
OR
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';
OR
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>"
OR
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:
- An error will show if the Snowflake connection is created directly in a Canvas app as shown in the below screenshot
- Instead of adding the connector directly in the Canvas app, create a service principal connection (not delegated) from outside of the Canvas app
- Use the Snowflake connection created above and create a virtual table
- Afterwards, the virtual table can be loaded in the Canvas app and build out of the Canvas app can proceed
- The ANIMALS table above is a virtual table, created using the Snowflake Connection as mentioned above
- An error will show if the Snowflake connection is created directly in a Canvas app as shown in the below screenshot
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.
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 |