Creating a Customizable 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:

Planning Your External Function on Azure


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.

  • If you plan to use an Azure Function on Linux as your remote service, then you must choose a valid combination of authentication and Azure pricing plan:

    • If you use the Premium or App Service pricing plan, you can create the Azure AD application from Azure’s Authentication/Authorization tab, and use Azure AD for authentication with the Azure Function service.

    • If you use the Consumption pricing plan, then:

  • A Snowflake account in which you have ACCOUNTADMIN privileges or a role with the CREATE INTEGRATION privilege.

You should already have the following information:

Azure AD Tenant ID: _____________________________________

    This is a `UUID <>`_ , which typically
    is formatted to look similar to 12345678-abcd-1234-efab-123456789012, where each non-dash character
    is a hexadecimal digit.

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 (

  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 App 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.
    This is in the form of a UUID.)

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

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

API Management API URL suffix....: __________________________________________

---------------- Information about the API Integration and External Function -----------------

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

AZURE_MULTI_TENANT_APP_NAME......: _______________________________________________

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

External Function Name...........: _______________________________________________

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.

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”.

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

(Microsoft explains how to create an HTTP-triggered function in the following documents:

However, Snowflake suggests using the custom instructions below.)

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:

  6. Click on the button titled Add.

    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.

    This function accepts and returns data in the same format (JSON) that Snowflake sends and reads. (For more information about data formats, see Remote Service Input and Output Data Formats .)

    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 =;
            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 Azure Function by providing sample data and clicking on the Test/Run button. You can paste the sample data below into the field named Body.

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

    The content of the output should be similar to the following:

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

Note that the formatting might be different from what is shown above.

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: .)

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 app. 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.


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.), then if you have a scroll bar at the bottom of your screen, try sliding the scroll bar 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.

    If you cannot change settings on this Authentication / Authorization page, see the troubleshooting tip: Unable to Modify Settings for the Azure Function Under the Authentication/Authorization Menu .

  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 if it is not already selected.

  5. Click on Azure Active Directory, which should take you to the Azure Active Directory Settings screen.

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 first 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 the second Management mode button 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, then click on App registrations.

    If this is not visible, then search for “App registrations” 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.


    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”. (Make sure you copy the ID, not the Azure AD application name. The ID should contain a UUID.

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 (which might be titled Resource 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-40 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. Find and click on the Settings tab, which is next to the Design tab on the panel of the screen below your API’s revision number (e.g. REVISION 1).

  3. 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.)

  4. Click the Save button.


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 test the external function before setting the security policy on the Azure API Management service. To simplify debugging, these instructions finish creating and testing the external function first.

Step 3: Create an API Integration Object in Snowflake


You need the following:

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

  • Your Azure AD Tenant ID. (Instructions for finding your tenant ID are in the section titled Prerequisites.)

Compose the CREATE API INTEGRATION statement

  1. Open up (if you haven’t already) a Snowflake web interface session.

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

    use role accountadmin;
  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;


    This name must follow the rules for Object Identifiers.


    Your Azure AD Tenant ID.

    As an alternative, you can use your domain (e.g.


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


    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:


    You can read the <api-management-service-name> from the “API Management service name” in the worksheet. You can read the <api_url_suffix> from the “API Management API URL suffix” in the worksheet.

    For example, your URL should look similar to:

    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 field 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

Now go back to the Snowflake web interface (where you earlier typed the CREATE API INTEGRATION command).

  1. Type 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 '<invocation_URL>'


The name of the function. This name must follow the rules for Object Identifiers.


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.

If your Azure Function uses the sample JavaScript code provided above, then the parameters are an INTEGER and a VARCHAR.


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


This is the URL to which Snowflake sends the HTTP POST command in order to call the remote service. If the proxy service is the Azure API Management service and the remote service is an Azure Function, then the URL looks similar to the following:




The API Management API URL Suffix from the worksheet.


The HTTP-Triggered Function name from the worksheet.

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

  2. If you have not already executed the CREATE EXTERNAL FUNCTION command that you typed above, execute it now.

Step 6: Call the External Function

  1. If appropriate, grant USAGE privilege on the external function to one or more Snowflake roles so that those roles can call the external function. (A role must have USAGE or OWNERSHIP privileges on that external function.)

  2. Execute your function by calling:

    SELECT my_external_function(42, 'Adams');

    If you customized the function name in the CREATE EXTERNAL FUNCTION command, then replace “my_external_function” with the cutomized name.

    The returned value should be similar to:

    [0, 42, "Adams"]

    where 42, "Adams" 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.


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 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 Application ID in the Values field.

        If you do not already have the Snowflake Application ID, you can get it by performing the following steps (the Snowflake Application ID is in the Application ID field):

        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 enterprise applications search box does not have a label. Look for a wide field immediately above the list of enterprise applications. The box might say something similar to First 50 shown, to search all of your applications, enter a display name or the application ID.

          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).

  6. Paste the following into Open ID URLs:<tenant ID>/.well-known/openid-configuration

    Replace the tenant ID with your Azure AD Tenant ID, which you recorded in the section titled Prerequisites, 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:

Creating an Asynchronous Function on Azure

Asynchronous external functions are supported on Azure. The concepts on Azure are similar to the concepts on AWS. However, the AWS code sample cannot be used directly on Azure because you must use the corresponding Azure services like Azure Functions and Azure Blob Storage. Furthermore, the details of navigating the cloud platform’s user interface differ.

Troubleshooting External Functions on Azure

Platform-Independent Symptoms

Actual Return Values for a Data Type Do Not Match Expected Return Values

When passing arguments to or from an external function, ensure that the data types are appropriate. If the value sent can’t fit into the data type being received, the value might be truncated or corrupted in some other way.

For more details, see Ensure that Arguments to the External Function Correspond to Arguments Parsed By the Remote Service.

When you call your function from SQL, you get a message about row numbers being out of order

Possible Cause(s)

Remember that the row numbers you return should be monotonically ascending integers starting at 0. The input row numbers must also follow that rule, and each output should match the corresponding input row (e.g. the output in output row 0 should correspond to the input in input row 0).

Possible Solution(s)
  1. Make sure that the row numbers you return are the same as the row numbers you received, and that each output value uses the row number of the corresponding input. That should work. If it doesn’t, then it is possible that the input row numbers were not right, or that you did not return the rows in the correct order, so move on to step 2 below.

  2. Make sure that the output row numbers start from 0, increase by 1, and are in order.

When trying to call the external function, you get the message “Error parsing JSON: Invalid response”

Possible Causes(s)

The most likely cause is that the JSON returned by the remote service (e.g. AWS Lambda function) is not constructed correctly.

Possible Solution(s)

Make sure that you return an array of arrays, with one inner array returned for each input row received. Review the description of the output format at Data Format Received by Snowflake.

An error message saying that the format of the returned value is not JSON

Possible Cause(s)

One possible cause of this is that your return value includes double quotes inside the value.

Possible Solution(s)

Although JSON strings are delimited by double quotes, the string itself should not start and end with a quotation mark in most cases. If the embedded double quotes are incorrect, then remove them.

An error message saying that the function receiving the wrong number of rows

Possible Cause(s)

The remote service probably tried to return more or fewer rows than it received. (Remember, even though the function is nominally scalar, it might receive multiple rows in the “body” field of the “event” parameter, and should return exactly as many rows as it received.)

Possible Solution(s)

Make sure that the remote service returns one row for each row that it receives.

Platform-Specific Symptoms


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 Troubleshooting Scalability and Performance Issues .

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.

Unable to Modify Settings for the Azure Function Under the Authentication/Authorization Menu

When creating your Azure Function, you might be unable to modify settings for that function under the Authentication/Authorization Menu. This occurs if all of the following are true:

  • Your Azure Function is running on Linux rather than Microsoft Windows.

  • You plan to use Azure AD authentication/authorization for your Azure Function.

  • You are using Azure’s “consumption” pricing tier (rather than the “premium” pricing tier).

Azure AD authentication is not available on the Linux Consumption plan for Azure Functions. You must use an App Service plan or Premium plan in order to authenticate with Azure AD.

Possible solutions include the following: