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. Authentication is done using a secret - a SAS URI for Azure Storage, or a username and password for of SQL Server - or using a Managed Identity. In this article, you'll learn how to create external tables that authenticate to Azure Storage with a user-assigned managed identity.

Note

This article shows how to create an external table over Azure Blob Storage. Managed identities can be used similarly with other types of Azure Storage resources, and with SQL Server. For more information about the connection strings used in these scenarios, see Connection Strings.

For more information on managed identities, see Managed identities overview.

Assign a managed identity to your cluster

To use managed identities with your cluster, you first need to assign the managed identity to your cluster. This assignment provides the cluster with permissions to act on behalf of the assigned managed identity.

In this article, we will use a user-assigned managed identity with the object ID: 802bada6-4d21-44b2-9d15-e66b29e4d63e.

Add a user-assigned identity using the Azure portal

  1. Sign in to the Azure portal.

  2. Create a user-assigned managed identity resource.

  3. Open an existing Azure Data Explorer cluster.

  4. Select Settings > Identity in left pane of portal.

  5. In the User assigned tab, select Add.

  6. Search for the identity you created earlier and select it. Select Add.

    Add user assigned identity.

Create a managed identity policy

Now that you've assigned a user-assigned managed identity to your cluster, define the managed identity policy, to allow the specific managed identity use the ExternalTable. The policy can either be defined in the cluster level or at a specific database level.

Enter the following policy alter command for the database level:

.alter database DatabaseName policy managed_identity ```
[
  {
    "ObjectId": "802bada6-4d21-44b2-9d15-e66b29e4d63e",
    "AllowedUsages": "ExternalTable"
  }
]
```

Note

To define the policy at the cluster level, replace database db with cluster.

Create an external table

The external table's authentication method is listed as part of the connection string provided in the command. To specify managed identity authentication for your external table, add the appropriate managed identity authentication suffix:

In this example, we're using user-assigned managed identities. Attach ;managed_identity=[managed-identity-object-id] to the end of the connection string:

https://StorageAccountName.blob.core.windows.net/Container;managed_identity=802bada6-4d21-44b2-9d15-e66b29e4d63e

Note

For system-assigned managed identities, you can use the reserved word system instead:
https://StorageAccountName.blob.core.windows.net/Container[/BlobName];managed_identity=system

This should give you the the following create external table command. Use this command to create the external table with your managed identity:

.create external table tableName (col_a: string, col_b: string)
kind = storage 
dataformat = csv (
'https://StorageAccountName.blob.core.windows.net/Container;managed_identity=802bada6-4d21-44b2-9d15-e66b29e4d63e'
)

Next steps