تحرير

مشاركة عبر

Authenticate external tables with managed identities

  • مقالة

An external table is a schema entity that references data stored outside the Azure Data Explorer database. External tables can be defined to reference data in Azure Storage or SQL Server and support various authentication methods.

In this article, you learn how to create an external table that authenticates with a managed identity.

Prerequisites

1 - Configure a managed identity for use with external tables

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 the preferred managed identity type.

  1. Follow the steps to Add a user-assigned identity to your cluster, and save the Object (principal) ID for later use.

  2. Run the .alter-merge policy managed_identity command. This command sets a managed identity policy on the cluster that allows the managed identity to be used with external tables. Replace <objectId> with the Object (principal) ID.

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

    Note

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

2 - Grant the managed identity external resource permissions

The managed identity must have permissions to the external resource in order to successfully authenticate.

Select the tab for the relevant type of external resource, and assign the required permissions.

The following table shows the required permissions by external resource. To import or query data from the external resource, grant the managed identity read permissions. To export data to the external resource, grant the managed identity write permissions.

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

3 - Create an external table

There are two types of external tables that support authentication with managed identities: Azure Storage external tables and SQL Server external tables.

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

To create an Azure Storage external table, do the following steps:

  1. Create a connection string based on the storage connection string templates. This string indicates the resource to access and its authentication information. Specify the managed identity authentication method.

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

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 ;managed_identity=system, which indicates to use a system-assigned managed identity for 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;managed_identity=system' 
)

Note

To authenticate with a user-assigned managed identity, replace system with the managed identity object ID.

Related content