Use a managed identity to run a continuous export job

Applies to: ✅ Microsoft FabricAzure Data Explorer

A continuous export job exports data to an external table with a periodically run query.

The continuous export job should be configured with a managed identity in the following scenarios:

  • When the external table uses impersonation authentication.
  • When the query references tables in other databases.
  • When the query references tables with an enabled row level security policy.

A continuous export job configured with a managed identity is performed on behalf of the managed identity.

In this article, you learn how to configure a system-assigned or user-assigned managed identity and create a continuous export job using that identity.

Prerequisites

Configure a managed identity

There are two types of managed identities:

  • System-assigned: A system-assigned identity is connected to your cluster and is removed when the cluster is removed. Only one system-assigned identity is allowed per cluster.

  • User-assigned: A user-assigned managed identity is a standalone Azure resource. Multiple user-assigned identities can be assigned to your cluster.

Select one of the following tabs to set up your preferred managed identity type.

  1. Follow the steps to Add a user-assigned identity.

  2. In the Azure portal, in the left menu of your managed identity resource, select Properties. Copy and save the Tenant Id and Principal Id for use in the following steps.

    Screenshot of Azure portal area with managed identity IDs.

  3. Run the following .alter-merge policy managed_identity command, replacing <objectId> with the managed identity object ID from the previous step. This command sets a managed identity policy on the cluster that allows the managed identity to be used with continuous export.

    .alter-merge cluster policy managed_identity ```[
        {
          "ObjectId": "<objectId>",
          "AllowedUsages": "AutomatedFlows"
        }
    ]```
    

    Nota

    To set the policy on a specific database, use database <DatabaseName> instead of cluster.

  4. Run the following command to grant the managed identity Database Viewer permissions over all databases used for the continuous export, such as the database that contains the external table.

    .add database <DatabaseName> viewers ('aadapp=<objectId>;<tenantId>')
    

    Replace <DatabaseName> with the relevant database, <objectId> with the managed identity Principal Id from step 2, and <tenantId> with the Microsoft Entra ID Tenant Id from step 2.

Set up an external table

External tables refer to data located in Azure Storage, such as Azure Blob Storage, Azure Data Lake Gen1, and Azure Data Lake Gen2, or SQL Server.

Select one of the following tabs to set up an Azure Storage or SQL Server external table.

  1. Create a connection string based on the storage connection string templates. This string indicates the resource to access and its authentication information. For continuous export flows, we recommend impersonation authentication.

  2. Run the .create or .alter external table to create the table. Use the connection string from the previous step as the storageConnectionString argument.

    For example, the following command creates MyExternalTable that refers to CSV-formatted data in mycontainer of mystorageaccount in Azure Blob Storage. The table has two columns, one for an integer x and one for a string s. The connection string ends with ;impersonate, which indicates to use impersonation authentication to access the data store.

    .create external table MyExternalTable (x:int, s:string) kind=storage dataformat=csv 
    ( 
        h@'https://mystorageaccount.blob.core.windows.net/mycontainer;impersonate' 
    )
    
  3. Grant the managed identity write permissions over the relevant external data store. The managed identity needs write permissions because the continuous export job exports data to the data store on behalf of the managed identity.

    External data store Required permissions Grant the permissions
    Azure Blob Storage Storage Blob Data Contributor Assign an Azure role
    Data Lake Storage Gen2 Storage Blob Data Contributor Assign an Azure role
    Data Lake Storage Gen1 Contributor Assign an Azure role

Create a continuous export job

Select one of the following tabs to create a continuous export job that will run on behalf of a user-assigned or system-assigned managed identity.

Run the .create-or-alter continuous-export command with the managedIdentity property set to the managed identity object ID.

For example, the following command creates a continuous export job named MyExport to export the data in MyTable to MyExternalTable on behalf of a user-assigned managed identity. <objectId> should be a managed identity object ID.

.create-or-alter continuous-export MyExport over (MyTable) to table MyExternalTable with (managedIdentity=<objectId>, intervalBetweenRuns=5m) <| MyTable