How to Authenticate with Snowflake: External OAuth with Azure AD

Introduction

Snowflake provides several options for authentication for users to access their resources. One method of authentication is with Azure AD (Entra ID) as an external OAuth provider.

FME supports two OAuth grant types for this integration:

Authorization Code Flow Grant
- The Microsoft Azure Active Directory (Template) Web service
Client Credentials (Service Account) Flow Grant
- The Microsoft Azure Active Directory (Client Credentials) Web service

This article walks you through configuring an Azure web service connection to use in a Snowflake database connection. We first set the connection in FME Form, then upload and reconfigure that connection for FME Flow.

If you need a better understanding of OAuth and web services/connections, please view this article: Creating an OAuth 2.0 Web Service and Connection in FME

Azure Configuration

If you haven’t configured the Azure app registration or Snowflake security integration yet, please do so following Snowflake’s guide for your specific OAuth grant type:

App registration/security integration requirements often differ across organizations, so for that reason, recommendations for app registrations and security integrations are out of the scope of this article. However, here are some key things to consider while doing so:

Snowflake recommends a single-tenant Azure app registration
To configure apps and integrations, you will need some level of admin access and permissions, which may include permission to:
- Create Azure app registrations, including:
  - Delegate permissions,
  - Grant admin consent to permissions,
- Map Microsoft email/accounts to Snowflake users/accounts,
- Create Snowflake security integrations
For the Authorization Code grant type, redirect URIs are required. A compatible redirect URI and Azure platform must be used for each FME application:
- For FME Form:
  - The Azure platform must be set to a Mobile/Desktop Application with an appropriate URI, typically a local one like: https://localhost/myapp
- For FME Flow:
  - The Azure platform must be set to a Web Application, with an appropriate URI for that particular Flow machine: https://<your FME Flow URL>/fmeoauth
- An Azure app registration can have multiple platforms and redirect URIs

It's strongly recommended to test your Azure and Snowflake OAuth configuration outside of FME before trying to configure a web service and web connection in FME. The Snowflake articles at the top of this section include steps for this.

Requirements

Azure app registration and Snowflake-Azure Security Integration
Basic understanding of OAuth 2.0

Step-by-Step Instructions

This section will walk you through setting up Snowflake’s External OAuth in FME. FME is the OAuth client for both grant types; however, configuring the authentication in FME requires specific values from the Azure OAuth client app registration:

Client ID
Client Secret
Authorization Scope/Scope
Authorization Code Flow only:
- Redirect URI
Client Credentials Grant only:
- Token Endpoint URL

These steps also follow FME 2024.0 for the configuration. While the UI might differ from other compatible FME versions (mentioned in requirements above), the process is generally similar across most versions.

Part 1: Configuring Authorization Code Grant in FME Form

This section walks you through connecting to a Snowflake database using the authorization code grant type. Because FME Form is a “public client,” this type of authorization is most recommended for Form, since it requires authenticating with logins rather than passwords or Client Secrets. However, you can still upload and configure the connection in the FME Flow browser UI. Reauthenticating this grant type in FME Flow is also done using your external browser.

1. Open FME Workbench and Add a Snowflake Database Connection

Start a new FME Workbench session, then from the top ribbon, navigate to:

Tools > FME Options > Database Connections

Click on the + symbol, then Add to select Snowflake as the Database Connection:

2. Set the Authentication and Add a Web Connection

Next, you will create a new web connection by first setting the Authentication parameter to OAuth Connection, then selecting Add Web Connection. This will open the Add Web Connection dialog box.

In FME, a Web Service is a reusable configuration template that stores all the information to create web connections, like defining specific authentication parameters, while a Web Connection is a specific instance using that service, where you enter and store your credentials. You'll first create or select a Web Service, then create a Web Connection that uses it.

Within the dialog, click on the Web Service parameter box and select Microsoft Azure Active Directory (Template) to open the Create New Web Service Configuration for the Authorization Code grant flow.

Here you will see a simplified template with the required values for this OAuth to work in FME Form in red.

Client Secret is left as optional and blank for Authorization Code Flow Grant. This is intentional and recommended for Azure web services of this grant type.

While we can configure the web service in this simplified template, we can get a more detailed view of the requests (like Authorization and Refresh Token requests) within the Manage Web Services dialog. You can navigate there by clicking the Open Web Services Manager link.

Alternatively, the Manage Web Services dialog may also be accessed via:

FME Options > Web Connections > Manage Web Services

Then click the + button to select Create From to create a new web service from Microsoft Azure Active Directory (Template).

3. Obtain the Values from the Azure OAuth Client Registration App:

Now, obtain the following values from the Azure app registration:

Client ID
Tenant ID
Redirect URI
Authorization Scope

Where the Client ID and Tenant ID are alphanumeric strings found on the Overview page:

The Redirect URI is set on the Authentication page, where the platform is also configured:

The Authorization Scope appears with a URI scheme, such as https:// or api://, at the beginning, followed by session:scope and a specified Snowflake role. The authorization scope is saved on the API permissions page under the Snowflake OAuth Resource API/ Permission Name:

The Authorization Scope in Azure determines which Snowflake role(s) a user can access (e.g., ANALYST, ADMIN, SUPPORT_RL). When you configure the database connection, you must specify a role that:

1. Has been assigned to your user through the Security Integration
2. Matches one of the roles defined in your Authorization Scope
3. Has appropriate privileges for the operations you need to perform

4. Populate the Web Service

Now we can configure the web service. Filling in the values and parameters:

Web Service Name
- A unique name that is easy to identify and cross-reference with the Azure app registration and Snowflake database, for example: Snowflake_AzureOAuth
Client Information
- Client ID: Your Client ID
- Client Secret: Left blank and Not Required enabled
- Redirect URI: Your Redirect URI
Authorization Parameters > URL - replace:
- [TENANT_ID] with your Tenant ID
- [AUTHORIZATION_SCOPE] with your Authorization Scope
Retrieve Token Parameters > URL - replace:
- [TENANT ID] with your Tenant ID
- [AUTHORIZATION SCOPE] with your Authorization Scope
Refresh Token Parameters > URL - if not filled in automatically, replace:
- [TENANT ID] with your Tenant ID
- [AUTHORIZATION SCOPE] with your Authorization Scope

The configured web service should look similar to this:

You can preview what the OAuth service requests by toggling on the Preview. For the authorization parameters that would look like:

5. Test the Web Service Connection and Add a Web Connection

Test the web service connection by clicking the Test button at the bottom of the configuration page, then complete the Sign in instructions with a Microsoft Account that’s tied to your Snowflake database user, where the user is tied to a specified role in Snowflake:

If everything is appropriately configured, you should get a “Test Succeeded” message.

After you confirm that the web service works, click Apply to add the web connection to the service. Optionally, rename the connection to a unique one that can be easily referenced to the intended account or simply the Snowflake database, for example, DMarsden_SnowflakeAzure_Connection. Then click Ok.

6. Complete the Configuration, then Test the Database Connection

Finish configuring the Database Connection by providing a connection name and providing your Snowflake connection parameters, then test the connection.

Account Name
- Your organization's Snowflake account identifier
Warehouse
- The Snowflake virtual warehouse you want to use
Database
- The database containing your target schema
Schema
- The schema containing your database resources
Role
- A role already assigned to the database user through the Security Integration. The role must have sufficient privileges to access the resources defined in the Authorization Scope you set in Step 4.

These connection parameters are case and whitespace-sensitive.

Test the connection. If everything is configured correctly, and you get a successful test of the database connection, you can optionally move on to Part 3: Publish and Configure to FME Flow or continue to Part 2 to configure the client credentials grant type.

Part 2: Configuring Client Credentials Grant in FME Form

This type of authentication is most recommended for FME Flow as it’s typically used in secure server-to-server web service connections, where FME Flow is a “private/secure” client. The web connection must still be configured within FME Form before it can be uploaded and used in FME Flow.

1. Open FME Workbench, then add a Snowflake Database Connection

Start a new FME Workbench session, then from the top ribbon, navigate to:

Tools > FME Options > Database Connections

Click on the + symbol, then Add to select Snowflake as the Database Connection:

2. Add a Web Connection

Next, you will create a new web connection by first setting the Authentication parameter to OAuth Connection, then selecting Add Web Connection to open the Add Web Connection dialog box:

In the Add Web Connection dialog, click on the Web Service drop-down list and select Microsoft Azure Active Directory (Client Credentials).

The required parameters for the web service are shown in red and need to be obtained from Azure.

3. Obtain the OAuth Web Service Parameters from Azure

Obtain the following values from the Azure app registration:

Token Endpoint URL
Scope
Client ID
Client Secret

From the Overview tab, click Endpoints to get the Token Endpoint URL. Use the endpoint named "OAuth 2.0 token endpoint (v2)".

The value for Scope is found under Manage> API permission page. Click on the application permission granted to your app registration (not the delegated permission, if you have multiple) and copy the value.

Since this is an Azure client credentials grant type, there is no user consent step, so the default scope should be used in the FME. This tells the Microsoft identity platform to issue a token with the direct admin-granted application permissions you have configured in your app registration. See Microsoft's documentation for more information.

Replace the scope with .default.

For example, if this is the scope in Azure:

api://80886434-57ab-1234-a705-54321f052c00/session:role:CUSTSOLS_RL

Replace session:role:CUSTSOLS_RL with .default.

api://80886434-57ab-1234-a705-54321f052c00/.default

From the Overview tab, get the Client ID. The client secret is only accessible right after it is first created. It should have been saved when the secret was first created as part of the Azure configuration. If not, you will need to create a new one.

4. Configure the Web Connection and Test

Fill in the values for the web connection and rename the connection to a unique and easily identifiable one, like: Snowflake_OAuth_DB

Click OK, then fill in the Database Connection Parameters and optionally rename the database connection to make it easily identifiable to the user/client, for example: Snowflake_DB_DMarsden.

Lastly, click Test to see if the web and Snowflake connection is correctly configured.

Once you get a successful connection, you can move on to Part 3: Upload and Configure to FME Flow.

Part 3: Upload and Configure to FME Flow

This section will walk you through uploading and configuring this database and web connection for FME Flow.

1. Upload and View the Database Connection

Right-click on your newly created Snowflake database connection and select Upload:

This will prompt you to upload to your Flow instance:

In your Flow instance web browser UI, navigate to

Connections & Parameters > Database Connections

Double-click on your database connection to view and optionally edit the Snowflake connection details, including the authentication type and OAuth connection.

If you configured an OAuth web connection using the Authorization Code grant, as described in Part 1, continue to the next step. Otherwise, there are no further steps.

2. Configure the OAuth Auth Code Connection for FME Flow

Go to Connections & Parameters > Web Connections

On the Web Connections page, select the Manage Web Services button to configure the connection for Flow:

Click on the web service name to open the Service Details page.

Here, fill in the values for:

Client Secret: Your Azure app registration's Client Secret
Redirect URI: The redirect URI from the Azure app registration's API Permissions page
- This URI must be formatted for your Flow installation
- It must be formatted like: https://<your FME Flow URL>/fmeoauth/

Finally, save the details to begin using this database connection in FME Flow.

Troubleshooting

If you're experiencing issues, first re-check the steps in the Snowflake documentation and see if you can obtain a token outside of FME. The Snowflake documentation has instructions on how to do this. See the links in the Azure Configuration section.

I receive the error “The role requested in the connection or the default role if none was requested in the connection ('ROLE_NAME') is not listed in the Access Token or was filtered.” when running a workspace or job.

The role was not added to the Azure client app registration. See Snowflake’s documentation linked in the Azure Configuration section.

If you’re using the client credentials grant type, make sure to choose “Application Permissions” as the type when granting the api permission to the client app registration.

My OAuth web connection works, but when I use it in my Snowflake database connection and test, I receive the error “Incorrect username or password was specified“

If you’re using the client credentials grant type, make sure that you have created a Snowflake system user with the same ‘sub’ value extracted from the token, as per Snowflake’s documentation linked in the Azure Configuration section.

If you're using the authorization code grant type, try purging temporary files in FME Workbench (Utilities > Purge Temporary Files). Then, reauthorize the Azure web connection and retest the Snowflake database connection.

Additional Resources

Snowflake: Add Database Connection: FME Snowflake database documentation

Search