Creating an External Function on Azure

This document shows one way to create an external function on Microsoft Azure.

These instructions assume that you are already familiar with Microsoft Azure administration. These instructions describe the general steps that you need to execute, but do not describe the Azure Portal user-interface in detail because the details could change.

In this Topic:

Prerequisites

You need:

  • An Azure AD (Active Directory) tenant.

  • An account in that Azure AD tenant. The account must have privileges to:

    • Create an Azure function app.

    • Create an Azure API Management service endpoint.

    • Register an Azure AD Application.

Preparing to Create an External Function on Azure

You should already have the following information:

Azure AD Tenant ID: _____________________________________

    This is a UUID.

If you do not already know your Azure AD tenant ID, you can find it by doing the following:

  1. Log into the Azure portal (http://portal.azure.com).

  2. In the “Azure services” icons near the top of the page, click on Azure Active Directory.

  3. In the menu on the left-hand side, look for the section titled Manage, then click on Properties under that.

    The Azure AD tenant ID is displayed in the Tenant ID field.

As you create your external function, you should record specific information (for example, the Azure function app name) that you enter so that you can use that information in subsequent steps. The worksheet below helps you track this information.

==============================================================================================
=========================================== Worksheet ========================================
==============================================================================================

----------------- Information about the Azure Function (remote service) ----------------------

Azure function app name................: _________________________________________

HTTP-Triggered Function name...........: _________________________________________

Azure function AD app registration name: _________________________________________

Azure function AD Application ID.......: _________________________________________

    (This is the "Application (client) ID" of the Azure AD app registration for the Azure function, and
    is used to fill in the "azure_ad_application_id" field in the CREATE API INTEGRATION command.)


------------ Information about the Azure API Management Service (proxy service) --------------

API Management service name......: __________________________________________

API Management API URL suffix....: __________________________________________

Snowflake Azure AD Application ID: __________________________________________
  (This is also called the "Application (client) ID".)
  This is the app ID for the Snowflake service principal.


---------------- Snowflake API Integration and External Function Information -----------------

API Integration Name.......: _______________________________________________

External Function Name.....: _______________________________________________

AZURE_MULTI_TENANT_APP_NAME: _______________________________________________

AZURE_CONSENT_URL..........: _______________________________________________

Step 1: Create the Remote Service (Azure Function)

In this tutorial, a remote service is implemented as an Azure function.

Create the Azure Function App

The first step is to create an Azure function app to serve as a container for the function(s) that you create later.

Microsoft’s instructions for creating an Azure function app are here:

You can create the function app by following Microsoft’s instructions (a link to those instructions is below). As you follow those instructions, remember the following:

Microsoft’s instructions for creating an Azure Function App are here:

Create an HTTP-Triggered Function

After you create your Azure function app (container), you need to create an Azure function in that container. This function acts as the remote service.

Microsoft allows Azure functions to be called (“triggered”) different ways. A Snowflake external function invokes a remote service via an HTTP POST command, so the Azure function you create must be an “HTTP triggered function”.

Microsoft documents how to create an HTTP-triggered function here:

Snowflake’s instructions below are similar, but include additional details and sample code, and suggest a different authorization level than Microsoft suggests.

Before executing the instructions below, you should be on the “Function App” screen. The name of your Azure function app should be displayed, typically near the upper left corner of the screen.

  1. In the left-hand side menu tree, look for the section titled Functions. In that section, click on the item labeled Functions to add a function.

  2. Click on the + Add button.

  3. Select HTTP trigger from the list of potential triggers on the right.

  4. Enter the name to use for your HTTP-triggered function.

    Record this name in the worksheet field titled “HTTP-Triggered Function name”.

  5. Enter the Authorization level.

    Snowflake recommends choosing Function as the authorization level.

    For more information about possible authorization levels, see: https://docs.microsoft.com/en-us/azure/azure-functions/functions-bindings-http-webhook-trigger?tabs=csharp#configuration

  6. Click on the button titled Create Function.

    This takes you to a screen that shows the function name and, below that, the word “Function”.

  7. In the tree menu on the left-hand side, click on “Code + Test”.

  8. Replace the default code with your own code.

    Here is a sample JavaScript “echo” function, which simply returns the rows that it was passed.

    The function reads each row, then copies that row to the output (results). The row number is also included in the output. The output is returned as part of a multi-level dictionary.

    Normally, the function returns HTTP code 200. If no rows are passed to the function (i.e. if the request body is empty), the function returns error code 400.

    module.exports = async function(context, request) {
        context.log('JavaScript HTTP trigger function processed a request.');
    
        if (request.body) {
            var rows = request.body.data;
            var results = [];
            rows.forEach(row => {
                results.push([row[0], row]);
            });
    
            results = {data: results}
            context.res = {
                status: 200,
                body: JSON.stringify(results)
            };
       }
       else {
           context.res = {
               status: 400,
               body: "Please pass data in the request body."
           };
       }
    };
    
  9. Click on the button titled Save above the code.

  10. You can test the code by clicking on the “Test/Run” button to test your Azure Function.

    You need a payload (data) for the test. A sample payload is below:

    {
         "data": [ [ 0, 43, "page" ], [ 1, 42, "life, the universe, and everything" ] ]
    }
    

Set the Authorization Requirements for the Azure Function App

When an external function is called, Snowflake sends an HTTP POST command to the proxy service (e.g. the Azure API Management service), which relays the POST to the remote service (e.g. the Azure function).

Each of these two steps should have authorization requirements, so you typically specify:

  • The authorization needed to call the API Management service.

  • The authorization needed to call functions in the Azure function app that contains your Azure function.

This section describes how to require authorization for your Azure function app. (The API Management service is created later, so its authorization requirements are also specified later.)

When Snowflake authenticates with your Azure function app, Snowflake uses OAuth client credential grant flow with Azure AD.

(More details about the client credential grant flow are here: https://docs.microsoft.com/en-us/azure/active-directory/azuread-dev/v1-oauth2-client-creds-grant-flow .)

This client credential flow requires an Azure AD app registration that represents the Azure function app.

This section has the instructions for creating the Azure AD app registration for the Azure function. For example, you can set your Azure Function app to require Azure AD authentication. To configure authorization via Azure AD, you must:

  • Create an Azure AD app registration, which is an Azure AD-based entity that represents an identity or resource identifier (i.e. what you want to protect).

  • Associate the Azure AD app registration with the Azure function app for which you want to require authentication.

Note

For Azure functions, the fastest way to create an Azure AD app registration is by enabling Azure AD Authentication for the service, as documented below. If you are using a remote service other than an Azure function, use the App Registrations page to create a new Azure AD app registration for your remote service. For more details about app registration, see the Microsoft documentation here:

Before you execute the steps below, you should be on the “Function App” screen for your Azure function app.

  1. In the left-hand menu pane, look for the section named Settings and click on Authentication / Authorization under that.

    If the left-hand margin shows the “Developer” menu (with “Code + Test”, “Integration”, etc.), if your screen shows a slider bar, try sliding to the left to return to the “Function App” or “App Service” section, and then look for Settings.

  2. Find the button titled App Service Authentication and change it from Off to On.

  3. Find the drop-down menu titled “Action to take when request is not authenticated” and select the value “Log in with Azure Active Directory”.

  4. Under “Authentication Providers”, select “Azure Active Directory”.

Proceed to the next section to configure authentication via Azure Active Directory (AD).

Authentication via Azure Active Directory

At this point, you should be on the “Azure Active Directory Settings” screen.

  1. Change the Management mode button from Off to either Express or Advanced. (The following instructions assume that you chose Express.)

  2. To create the Azure AD app registration for your Azure function app, follow the steps below:

    1. Find Management mode and select either Create New AD App or Select Existing AD App.

      For this demonstration, select Create New AD App unless you already have an Azure AD app registration that you want to use.

    2. By default, the Azure AD app registration name is the same as the Azure function app name. This name should appear in the “Create App” field. You can change this name if you want.

      Record the Azure AD app registration name in the “Azure function AD app registration name” field of the worksheet.

    3. Click on the OK button on the bottom left of your window. This creates an Azure AD app registration, and returns you to the App Service Authentication / Authorization screen.

    4. Verify that the button named App Service Authentication is On.

    5. Click the Save button, which is near the upper left corner of the main pane (to the right of the menu pane).

    Most of the preceding steps are also documented in the Azure documentation, which you can read for additional details:

Now that your Azure AD app is registered, the next step is to verify that the app is listed under “App registrations”.

  1. Open a new browser tab and go to http://portal.azure.com, then click on App Registrations.

    If this is not visible, then search for “App Registration” in the Microsoft Azure search window near the top of the screen.

    You should now be on the “App registrations” screen.

    You should see two tabs, one titled “All applications” and one titled “Owned applications”.

  2. Select the “All applications” tab if it is not already selected.

  3. Click on the name of the Azure AD app registration that you just created for your Azure function app.

    Note

    You can use the search bar to search for your Azure AD app by name. To do so, enter the first few characters of the name. The search bar assumes that you are typing the leading part of the name; it does not search for the specified substring everywhere in the function names.

    This should take you to the “App registrations” screen.

  4. On the App registrations screen that describes the Azure AD App for your Azure Function app, you should see the name of your Azure AD App.

  5. Find the field named “Application (client) ID”.

    Record this in the worksheet in the field named “Azure Function App AD Application ID”.

Step 2: Create and Configure the Proxy Service (Azure API Management service)

Snowflake does not send data (HTTP POST requests) directly to the remote service (e.g. Azure function). Instead, Snowflake sends the data to a proxy service that relays the data from Snowflake to the remote service, and from the remote service back to Snowflake.

On Azure, Snowflake External Functions support Azure API Management as the proxy service.

Create the API Management Service

To create the API Management service, follow Microsoft’s instructions (a link to the instructions is included below).

As you execute those instructions, remember to do the following:

  • Record the API Management Service name as the “API Management service name” in the worksheet above.

Microsoft’s instructions for creating an API Management service are:

Deploying the API Management service can take 30 minutes or more. When deployment completes, you should see a message similar to “Your deployment is complete”.

After deployment completes, click the Go to resource button.

Import the API that Contains the Azure Function

After you create the API Management service (proxy service), the next step is to import and publish the Azure function app that contains the APIs (function(s)) to call through that API Management service.

  1. Follow Microsoft’s instructions for importing and publishing an Azure function (a link to the instructions is below).

    As you execute those instructions, remember the following:

    • One of these steps requires that you specify an option for Product. For this demonstration, choose Starter rather than Unlimited. (For a production system, you might choose differently.)

    • Record the following value(s) in the worksheet:

      • Record the API URL suffix in the worksheet above in the “API Management API URL suffix” section.

    Below is a link to Microsoft’s instructions. This page includes instructions for other tasks, as well as importing APIs. For this demonstration, you typically need only the instructions for importing an Azure function app as a new API.

    After completing the steps listed in the Azure documentation to import an Azure function app, you should be back on the API Management service page.

  2. In the Settings tab, if the Subscription Required checkbox has a checkmark, then uncheck it unless you want to require a subscription.

    (If you don’t see the Subscription section, scroll down.)

  3. Click the Save button.

Note

You should set security policies on the Azure API Management service. You can set the security policy now, or you can finish creating the external function first and sanity test the external function before setting the security policy on the Azure API Management service. To simplify debugging, these instructions finish creating and sanity testing the external function first.

Step 3: Create an API Integration Object in Snowflake

Prerequisites

You need the following:

  • Your Azure Function App AD Application ID from the worksheet.

  • Your Azure AD Tenant ID.

Compose the CREATE API INTEGRATION statement

  1. Open up (if you haven’t already) a Snowflake session, typically a GUI session.

  2. Use a Snowflake role with ACCOUNTADMIN privileges or a role with the CREATE INTEGRATION privilege, for example:

    use role has_accountadmin_privileges;
    
  3. Type the CREATE API INTEGRATION command to create an API integration. The command should look similar to the following:

    create or replace api integration <integration_name>
        api_provider = azure_api_management
        azure_tenant_id = '<tenant_id>'
        azure_ad_application_id = '<azure_application_id>'
        api_allowed_prefixes = ('<url>')
        enabled = true;
    

    tenant_id

    Your Azure AD Tenant ID.

    As an alternative, you can use your domain (e.g. my_company.onmicrosoft.com).

    <azure_application_id>

    Set this to the Azure Function App AD Application ID in the worksheet.

    api_allowed_prefixes

    This field allows you to restrict the URLs to which this API integration can be applied.

    Usually, this field holds the URL of the proxy service (e.g. the URL of the Azure API Management service). To narrow this further, you can append the API Management API URL suffix. If you include the API Management API URL suffix, then the URL in the api_allowed_prefixes field should look similar to:

    https://<api-management-service-name>.azure-api.net/<api_url_suffix>

    For example:

    where:

    • https://my-api-management-svc.azure-api.net is the URL of the API Management service.

    • my-api-url-suffix is the name of the API Management API URL suffix that you recorded in the worksheet.

    This should match the base URL and suffix from the API Management service Settings tab in your imported API. You can copy from there if it is convenient.

    For more details, see CREATE API INTEGRATION.

  4. In the worksheet slot titled “API Integration Name”, record the name of the API integration that you created. You need the API Integration name when you execute the CREATE EXTERNAL FUNCTION command later.

  5. Execute the CREATE API INTEGRATION command you typed above.

Step 5: Create the External Function in Snowflake

From inside Snowflake, execute the command CREATE EXTERNAL FUNCTION. The command looks similar to the following:

create or replace external function <function_name>(<parameters>)
    returns variant
    api_integration = <api_integration_name>
    as '<azure_function_URL>'
    ;

function_name

The name of the function.

parameters

The function’s parameters, if any. The parameters should correspond to the parameters of the remote service (e.g. Azure function). The parameter names do not need to match, but the data types need to be compatible.

api_integration_name

The API integration name that you recorded earlier in the “API Integration name” field in the worksheet.

azure_function_URL

The URL should be:

https://<API Management service name>.azure-api.net/<api_url_suffix>/<http_triggered_function_name>

api_url_suffix

The API Management API URL Suffix from the worksheet.

http_triggered_function_name

The Triggered Function name from the worksheet.

In the worksheet, record the name of the external function that you created in the slot titled “External Function Name”.

Step 6: Test the External Function

Test the external function by calling it. For example, execute a SQL command similar to the following:

select my_external_function(42);

The returned value should be similar to:

[0, 42]

where 42 is the returned value and 0 is the row number of the returned value.

Step 7: Set the Security Policy on the Azure API Management Service (Proxy Service)

The previous steps allow your imported APIs (and thus your Azure function) to be called not only by Snowflake, but also by other authenticated clients, such as applications that are in your Azure AD tenant or that have a service principal in your Azure AD tenant. If you want to allow only Snowflake to call the Azure function, you must force an additional authorization step: token validation.

When Snowflake tries to access the API Management service, Snowflake presents a JWT access token obtained from Azure AD. The API Management service can either validate the JWT or pass it through without validation. To require the API Management service to validate the token, you can add a validate-JWT policy that specifies the rules for validating the token.

Note

If you prefer to use role-based validation in your validate-JWT policy, follow the link below to assign a role to the Snowflake service principal:

Create a validate-JWT Policy that Specifies that Snowflake Can Call the Azure Function

This section shows how to specify a policy for validating a JSON Web Token (JWT) that authorizes Snowflake to call your Azure function. The validation policy (“validate-JWT policy”) validates the following two claims in the JWT:

  • The Snowflake service principal application ID (the “requestor AppID” or just “appid”).

  • The target application App ID (the “audience ID” or just “aud”) of the Azure function.

For more information about claims in JSON Web Tokens (JWTs) issued by Azure Active Directory, see:

The following steps configure the imported API to use a JSON Web Token.

  1. Go to the “API Management service” screen.

  2. Select your API Management service.

  3. Find the “APIs” section in the left-hand column, then click on the “APIs” option under that.

  4. In the column that contains “All APIs”, click on the name of the API Management service for which you want to add a security policy.

  5. Look for “In-bound Processing”.

    1. Click on + Add policy.

    2. Click on validate-jwt.

    3. Fill in the Header name with the value Authorization.

    4. Add validation for the JWT (JSON Web Token) provided by Snowflake for accessing the Azure Function:

      1. Look for Required claims and click on + Add claim.

      2. Fill in the Name field with “aud” (short for “audience”).

      3. Within the required claim, Look for Values and click on +Add value.

        Add the UUID that you copied to the azure_ad_application_id field in the CREATE API INTEGRATION command. This is recorded in the “Azure Function App AD Application ID” field of the worksheet.

    5. Add a separate “claim” for Snowflake:

      1. Click on + Add claim again:

      2. Fill in the Name field with the literal string “appid”.

      3. Within the claim, click on + Add value and add the Snowflake app id.

        If you do not already have the Snowflake app id, you can get it by performing the following steps:

        1. In the worksheet, find the AZURE_MULTI_TENANT_APP_NAME that you filled in earlier.

        2. In the Azure Portal search box, look for “Enterprise Applications”.

          This takes you to the “Enterprise applications | All applications” screen.

        3. In that screen, search for the AZURE_MULTI_TENANT_APP_NAME.

          The search box is not clearly labeled; look for a wide field that does NOT have a label.

          If you do not find an exact match for the AZURE_MULTI_TENANT_APP_NAME, then search again using only the first several characters of this name (if the name contains an underscore, then do not include the underscore or any characters after the underscore).

        4. Copy the Application ID (which has the format of a UUID) into the Values field.

  6. Paste the following into Open ID URLs:

    https://login.microsoftonline.com/<tenant ID>/.well-known/openid-configuration

    Replace the tenant ID with your Azure AD Tenant ID, which you recorded in the section titled Preparing to Create an External Function on Azure, just above the worksheet.

  7. Click on Save.

  8. Test the change by calling the external function again.

Restrict the IP Addresses from which Azure Function (Remote Service) Calls are Accepted (Optional)

In addition to specifying a validate-JWT policy (or using role-based authorization), you can add still more security by adding IP address restriction so that only the API Management service’s IP is allowed to access the Azure function app that contains your Azure function. For information about restricting IP addresses, see:

Troubleshooting

This section contains troubleshooting information specific to Azure.

For general troubleshooting information, see doc:general external functions troubleshooting document </sql-reference/external-functions-troubleshooting>.

Timeouts

There are many possible causes of timeouts. On Azure, one of the possible causes is that the Azure function app was not written to scale properly. Ensure that you are following the Azure guidelines for writing scalable functions .

For more information about troubleshooting scalability and performance issues, see the doc:general external functions troubleshooting document </sql-reference/external-functions-troubleshooting>.

Request failed for external function <function_name>. Error: 401 ‘{ “statusCode”: 401, “message”: “Access denied due to missing subscription key. Make sure to include subscription key when making requests to an API.” }’

You might need to turn off the subscription requirement of the API Management service.

Request failed for external function <function_name>. Error: 401 ‘{ “statusCode”: 401, “message”: “Invalid JWT.” }’

  • You might not have finished setting the security policy on the Azure API Management service. For example, you might have:

    • Created but not edited the JWT.

    • Omitted one or more of the required claims/values. For example, you might have specified the claim for Snowflake but not the remote service (Azure function), or vice versa.

  • You might have used an invalid open ID URL.

SQL execution error: Failed to obtain Azure Active Directory access token.

Try the following steps:

  • Verify that the Snowflake service principal has access to your Azure AD tenant.

  • Verify that the tenant ID and the Azure AD application ID are correct.

    Note that whitespace, including leading and trailing whitespace (e.g. blanks), is significant in ID field(s). Check for incorrect leading or trailing whitespace.