Run federated queries on Snowflake (Microsoft Entra ID)

This page describes how to set up Lakehouse Federation to run federated queries on Snowflake data that is not managed by Azure Databricks. To learn more about Lakehouse Federation, see What is Lakehouse Federation?

To connect to your Snowflake database using Lakehouse Federation, you must create the following in your Azure Databricks Unity Catalog metastore:

• A connection to your Snowflake database.
• A foreign catalog that mirrors your Snowflake database in Unity Catalog so that you can use Unity Catalog query syntax and data governance tools to manage Azure Databricks user access to the database.

This page covers how to run federated queries on Snowflake data using Microsoft Entra ID as external OAuth provider. It will cover both U2M (authorization code) and M2M (client credentials) flows.

For other authentication methods, see the following pages:

• Snowflake built-in OAuth
• OAuth with Okta
• OAuth access token
• PEM private key
• Basic authentication (username/password)

You can run federated queries on Snowflake using query federation or catalog federation.

In query federation, JDBC pushes the Unity Catalog query down to the external database. This is ideal for on-demand reporting or proof-of-concept work on your ETL pipelines.

In catalog federation, the Unity Catalog query runs directly against file storage. This approach is helpful for incremental migration without code adaptation or as a longer-term hybrid model for organizations that must maintain some data in Snowflake alongside their data registered in Unity Catalog. See Enable Snowflake catalog federation.

Before you begin

Workspace requirements:

• Workspace enabled for Unity Catalog.

Compute requirements:

• Network connectivity from your compute resource to the target database systems. See Networking recommendations for Lakehouse Federation.
• Azure Databricks compute must use Databricks Runtime 13.3 LTS or above and Standard or Dedicated access mode.
• SQL warehouses must be pro or serverless and must use 2023.40 or above.

Permissions required:

• To create a connection, you must be a metastore admin or a user with the CREATE CONNECTION privilege on the Unity Catalog metastore attached to the workspace.
• To create a foreign catalog, you must have the CREATE CATALOG permission on the metastore and be either the owner of the connection or have the CREATE FOREIGN CATALOG privilege on the connection.

Additional permission requirements are specified in each task-based section that follows.

What is external OAuth in Snowflake?

External OAuth is an authentication method that allows you to use external servers that use OAuth 2.0 for accessing Snowflake. Instead of a user or an application logging into Snowflake directly, they authenticate with the OAuth provider (e.g. Okta, Microsoft Entra ID, or PingFederate).

The OAuth provider then issues an access token, which Databricks presents to Snowflake. Snowflake then verifies the token's signature and claims with the configured security integration, granting access.

Determine an OAuth flow

To choose the correct OAuth flow for your Snowflake connector, you need to understand the two primary types: U2M (User-to-Machine) and M2M (Machine-to-Machine).

U2M is an authorization flow where the application (Azure Databricks) receives permission to act on behalf of the user. This flow is interactive, meaning the user is prompted to sign in when creating a foreign connection, and the application receives an access token scoped to the user's permissions.

M2M is a non-interactive flow used when an application needs to access resources directly without user involvement. In this flow, the application must be registered in the OAuth provider with a set of permissions, independent of any specific user.

In a Azure Databricks workspace, OAuth refers to U2M authentication, and OAuth Machine to Machine refers to M2M authentication.

Configure Microsoft Entra ID

The exact Entra configuration depends on the needs and policies of your company. The steps in this and following sections serve as a guide to get you to a functional OAuth setup, and represent a simple, working example, not an exhaustive Entra resource. Refer to Azure documentation for in-depth coverage of their platform and features.

Configure the OAuth resource application

The resource application represents Snowflake in Microsoft Entra ID and defines the permissions (scopes) that clients can request.

1. Sign in to Microsoft Azure Portal.
2. Navigate to Microsoft Entra ID, and note Tenant ID in the Overview section. We will refer to this value as <TENANT_ID> in the future.
3. Expand the Manage dropdown, navigate to App Registrations, and click New registration.
   1. Enter a descriptive name, for example, "Snowflake OAuth Resource".
   2. Select Accounts in this organizational directory only (Default Directory only - Single tenant) for the supported account types.
   3. Click Register.
4. In the Overview section of the newly created app registration, note Application (client) ID value, we will refer to it in the future as <RESOURCE_APP_ID>.
5. Navigate to Expose an API in the drawer on the left, and click Add near Application ID URI at the top. The default value should be of the format api://<RESOURCE_APP_ID> and it will function as the OAuth audience claim. You can instead set a value containing your organization's domain, like https://yourorg.onmicrosoft.com/<RESOURCE_APP_ID> (see Restrictions on identifier URIs of Microsoft Entra applications). Save the final value, we will refer to it as <SNOWFLAKE_APPLICATION_ID_URI> during the security integration setup.
6. (U2M only) In the Expose an API section, click Add a scope.
   1. Enter the name of your Snowflake role with the session:role: prefix as the scope name. For example, session:role:analyst.
   2. Select who can consent.
   3. Enter the admin consent display name, e.g. Account Admin.
   4. Enter the admin consent description, e.g. Can administer the Snowflake account.
   5. Finally, click Add scope.
7. (M2M only) Navigate to App roles, and click Create app role.
   1. Display name: enter a name, it can match the role value, in our case, PUBLIC.
   2. Allowed member types: choose Applications.
   3. Value: select the scope, we will use session:role:PUBLIC.
   4. Description: enter a description - ideally you should describe the level of access the role provides.
   5. Do you want to enable this app role? Make sure your role is enabled by selecting the checkmark.
   6. Click Apply.

Create an OAuth client application

The client application represents the client (Azure Databricks) which will connect to Snowflake in order to obtain tokens.

1. Navigate to App Registrations again and click New registration.
   1. Enter a descriptive name, for example, "Snowflake OAuth Client".
   2. For supported account types, select Accounts in this organizational directory only (Default Directory only - Single tenant).
   3. Click Register.
2. In the Overview section, copy and save the Application (client) ID field. This value will be referred to as <CLIENT_ID> in the future when setting up connection in Azure Databricks.
3. (M2M only) Click on the link next to Managed application in local directory. There, copy and save the value in the Object ID field. In future steps we will refer to this as <CLIENT_OBJECT_ID> - it will be required to create a mapping between the client and security integration in Snowflake.
4. Navigate back and expand the Manage dropdown. There, navigate to Certificates & secrets, then click on New Client Secret.
   1. Choose a description, e.g. Azure Databricks Client Secret
   2. Choose secret expiration according to your needs. Once the secret expires, you will need to create a new one in Entra and update the connection in Azure Databricks.
   3. Click Add below.
   4. Once added, copy and securely store the value in the Value column - you will not be able to see it again after leaving the page. We will refer to this value as <CLIENT_SECRET> when creating a connection in Azure Databricks.
5. Navigate to API permissions, and click Add a permission.
   1. Go to the APIs my organization uses tab.
   2. Search for the resource app you have created in the previous section and click on it.
   3. (U2M only) Click the large Delegated permissions button, and select the role you have created in the previous step under Permissions.
   4. (M2M only) Click the large Application permissions button, and select the role you have created in the previous step under Permissions.
   5. Click Add permissions.
6. Finally, click Grant Admin consent for Default Directory.
7. (U2M only) Last step is setting up a redirect URI. When you initiate the Entra login process from Azure Databricks, the redirect URL tells Entra which Azure Databricks page to get back to if the login was successful.
   1. In the Manage dropdown, navigate to the Authentication section.
   2. Click on Add a platform and choose Web application.
   3. Enter a redirect URI in the https://<YOUR_DATABRICKS_WORKSPACE>/login/oauth/snowflake.html format.
   4. Click Configure.

Create a security integration in Snowflake

This step configures a security integration in Snowflake so that it can communicate securely with Entra ID, validate Entra ID tokens, and provide the appropriate data access based on the role associated with the OAuth access token.

User-to-Machine flow

1. Connect to your Snowflake account as a user with the ACCOUNTADMIN role.

2. Run the CREATE SECURITY INTEGRATION command. For example:

   CREATE SECURITY INTEGRATION <ENTRA_U2M_SECURITY_INTEGRATION_NAME>
   TYPE = EXTERNAL_OAUTH
   ENABLED = TRUE
   EXTERNAL_OAUTH_TYPE = AZURE
   EXTERNAL_OAUTH_ISSUER = 'https://sts.windows.net/<TENANT_ID>/'
   EXTERNAL_OAUTH_JWS_KEYS_URL = 'https://login.microsoftonline.com/<TENANT_ID>/discovery/v2.0/keys'
   EXTERNAL_OAUTH_AUDIENCE_LIST = ('<SNOWFLAKE_APPLICATION_ID_URI>')
   EXTERNAL_OAUTH_TOKEN_USER_MAPPING_CLAIM = 'email'
   EXTERNAL_OAUTH_SNOWFLAKE_USER_MAPPING_ATTRIBUTE = 'EMAIL_ADDRESS';
   

   The example uses the email mapping claim and attribute. This requires that your Snowflake user email matches the email in the Azure tenant.

   ALTER USER <SNOWFLAKE_USER> SET EMAIL = '<YOUR_EMAIL>';
   

   It is possible to use different claims, depending on your needs.

Machine-to-Machine flow

1. Connect to your Snowflake account as a user with the ACCOUNTADMIN role.

2. Run the CREATE SECURITY INTEGRATION command. For example:

   CREATE OR REPLACE SECURITY INTEGRATION <ENTRA_M2M_SECURITY_INTEGRATION_NAME>
   TYPE = EXTERNAL_OAUTH
   ENABLED = TRUE
   EXTERNAL_OAUTH_TYPE = AZURE
   EXTERNAL_OAUTH_ISSUER = 'https://sts.windows.net/<TENANT_ID>/'
   EXTERNAL_OAUTH_JWS_KEYS_URL = 'https://login.microsoftonline.com/<TENANT_ID>/discovery/v2.0/keys'
   EXTERNAL_OAUTH_AUDIENCE_LIST = ('<SNOWFLAKE_APPLICATION_ID_URI>')
   EXTERNAL_OAUTH_TOKEN_USER_MAPPING_CLAIM = 'sub'
   EXTERNAL_OAUTH_SNOWFLAKE_USER_MAPPING_ATTRIBUTE = 'login_name';
   

   You will need to create a new non-human user in Snowflake to represent the client connecting to the database. This step is required for Snowflake to be able to verify and map a user to the Client App representing Azure Databricks in Azure Entra ID.

   CREATE OR REPLACE USER <ENTRA_M2M_CLIENT_USER>
   LOGIN_NAME = '<CLIENT_OBJECT_ID>'
   DEFAULT_ROLE = 'PUBLIC';
   

Create a connection

A connection specifies a path and credentials for accessing an external database system. To create a connection, you can use Catalog Explorer or the CREATE CONNECTION SQL command in an Azure Databricks notebook or the Databricks SQL query editor.

Permissions required: Metastore admin or user with the CREATE CONNECTION privilege.

User-to-Machine flow

1. In your Azure Databricks workspace, click Catalog.

2. At the top of the Catalog pane, click the Add icon and select Add a connection from the menu.

   Alternatively, from the Quick access page, click the External data > button, go to the Connections tab, and click Create connection.

3. On the Connection basics page of the Set up connection wizard, enter a user-friendly Connection name.

4. Select a Connection type of Snowflake.

5. For Auth type, select OAuth from the drop-down menu.

6. (Optional) Add a comment.

7. Click Next.

8. Enter the following authentication and connection details for your Snowflake warehouse.

   • Host: For example, snowflake-demo.east-us-2.azure.snowflakecomputing.com

   • Port: For example, 443

   • User: For example, snowflake-user

   • Authorization endpoint: https://login.microsoftonline.com/<TENANT_ID>/oauth2/v2.0/authorize

   • Client secret: The client secret that you saved when you created the security integration.

   • Client ID: The client ID that you saved when you created the security integration.

   • OAuth scope: api://<RESOURCE_APP_ID>/.default offline_access

   • OAuth provider: Azure Entra ID

   • Sign in with Microsoft Entra ID: Click and sign in to Snowflake using your Microsoft credentials.

     Upon successful sign-in, you are directed back to the Set up connection wizard.

9. Click Create connection.

10. On the Catalog basics page, enter a name for the foreign catalog. A foreign catalog mirrors a database in an external data system so that you can query and manage access to data in that database using Azure Databricks and Unity Catalog.

11. (Optional) Click Test connection to confirm that it works.

12. Click Create catalog.

13. On the Access page, select the workspaces in which users can access the catalog you created. You can select All workspaces have access, or click Assign to workspaces, select the workspaces, and then click Assign.

14. Change the Owner who will be able to manage access to all objects in the catalog. Start typing a principal in the text box, and then click the principal in the returned results.

15. Grant Privileges on the catalog. Click Grant:

    1. Specify the Principals who will have access to objects in the catalog. Start typing a principal in the text box, and then click the principal in the returned results.
    2. Select the Privilege presets to grant to each principal. All account users are granted BROWSE by default.
       • Select Data Reader from the drop-down menu to grant read privileges on objects in the catalog.
       • Select Data Editor from the drop-down menu to grant read and modify privileges on objects in the catalog.
       • Manually select the privileges to grant.
    3. Click Grant.

16. Click Next.

17. On the Metadata page, specify tags key-value pairs. For more information, see Apply tags to Unity Catalog securable objects.

18. (Optional) Add a comment.

19. Click Save.

Machine-to-Machine flow - Catalog Explorer

1. In your Azure Databricks workspace, click Catalog.

2. At the top of the Catalog pane, click the Add icon and select Add a connection from the menu.

   Alternatively, from the Quick access page, click the External data > button, go to the Connections tab, and click Create connection.

3. On the Connection basics page of the Set up connection wizard, enter a user-friendly Connection name.

4. Select a Connection type of Snowflake.

5. For Auth type, select OAuth Machine to Machine from the drop-down menu.

6. (Optional) Add a comment.

7. Click Next.

8. Enter the following authentication details for your connection.

   • Host: For example, snowflake-demo.east-us-2.azure.snowflakecomputing.com
   • Port: For example, 443
   • Openid metadata endpoint: https://login.microsoftonline.com/<TENANT_ID>/v2.0/.well-known/openid-configuration
   • Client secret: The client secret that you saved when you created the security integration.
   • Client ID: The client ID that you saved when you created the security integration.
   • OAuth scope: api://<RESOURCE_APP_ID>/.default

9. Click Next.

10. Enter the following connection details for your connection:

    • Snowflake warehouse: The name of the warehouse you want to use.
    • (Optional) Proxy host: Host of the proxy used to connect to Snowflake. You must also select Use proxy and specify Proxy port.
    • (Optional) Use proxy: Whether to connect to Snowflake using a proxy server.
    • (Optional) Proxy port: Port of the proxy used to connect to Snowflake. You must also select Use proxy and specify Proxy host.
    • (Optional) Snowflake role: The default security role to use for the session after connecting.

11. Click Create connection.

12. On the Catalog basics page, enter a name for the foreign catalog. A foreign catalog mirrors a database in an external data system so that you can query and manage access to data in that database using Azure Databricks and Unity Catalog.

13. (Optional) Click Test connection to confirm that it works.

14. Click Create catalog.

15. On the Access page, select the workspaces in which users can access the catalog you created. You can select All workspaces have access, or click Assign to workspaces, select the workspaces, and then click Assign.

16. Change the Owner who will be able to manage access to all objects in the catalog. Start typing a principal in the text box, and then click the principal in the returned results.

17. Grant Privileges on the catalog. Click Grant:

    1. Specify the Principals who will have access to objects in the catalog. Start typing a principal in the text box, and then click the principal in the returned results.
    2. Select the Privilege presets to grant to each principal. All account users are granted BROWSE by default.
       • Select Data Reader from the drop-down menu to grant read privileges on objects in the catalog.
       • Select Data Editor from the drop-down menu to grant read and modify privileges on objects in the catalog.
       • Manually select the privileges to grant.
    3. Click Grant.

18. Click Next.

19. On the Metadata page, specify tags key-value pairs. For more information, see Apply tags to Unity Catalog securable objects.

20. (Optional) Add a comment.

21. Click Save.

Machine-to-Machine flow - SQL

Run the following command in a notebook or the Databricks SQL query editor.

CREATE CONNECTION <connection-name>
TYPE SNOWFLAKE
OPTIONS (
  host '<hostname>',
  port '443',
  sfWarehouse '<warehouse-name>',
  client_id '<client-id>',
  client_secret '<client-secret>',
  openid_metadata_endpoint '<oidc-metadata-endpoint>',
  oauth_scope 'session:role:PUBLIC'
);

You can then create a foreign catalog using the new connection by running the following command:

CREATE FOREIGN CATALOG <catalog-name>
USING CONNECTION <connection-name>
OPTIONS (database = '<database>');

Case-sensitive database identifiers

The database field of the foreign catalog maps to a Snowflake database identifier. If the Snowflake database identifier is not case-sensitive, the casing you use in the foreign catalog <database-name> is preserved. However, if the Snowflake database identifier is case-sensitive, you must wrap the foreign catalog <database-name> in double quotes to preserve the case.

For example:

• database is converted to DATABASE

• "database" is converted to database

• "database""" is converted to database"

  To escape a double quote, use another double quote.

• "database"" results in an error because the double quote is not escaped correctly.

For more information, see Identifier requirements in the Snowflake documentation.

Supported pushdowns

The following pushdowns are supported:

• Filters
• Projections
• Limit
• Joins
• Aggregates (Average, Corr, CovPopulation, CovSample, Count, Max, Min, StddevPop, StddevSamp, Sum, VariancePop, VarianceSamp)
• Functions (String functions, Mathematical functions, Data, Time and Timestamp functions, and other miscellaneous functions, such as Alias, Cast, SortOrder)
• Windows functions (DenseRank, Rank, RowNumber)
• Sorting

Data type mappings

When you read from Snowflake to Spark, data types map as follows:

Limitations

• The Snowflake OAuth endpoint must be accessible from Azure Databricks control plane IPs. See Outbound IPs from Azure Databricks control plane. Snowflake supports configuring network policies at the security integration level, which allows for a separate network policy that enables direct connectivity from the Azure Databricks control plane to the OAuth endpoint for authorization.