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'll learn how to create an external table that authenticates with a managed identity.

Prerequisites

• An Azure Data Explorer cluster and database. Create a cluster and database.
• Database Admin permissions on the Azure Data Explorer database.

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.

User-assigned

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 following .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 from the previous step.

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

System-assigned

1. Follow the steps to Add a system-assigned identity to your cluster.

2. Run the following .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.

   .alter-merge cluster policy managed_identity ```[
       {
         "ObjectId": "system",
         "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.

Azure Storage

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

SQL Server

To import or query data from the SQL database, grant the managed identity table SELECT permissions. To export data to the SQL database, grant the managed identity CREATE, UPDATE, and INSERT permissions. To learn more, see Permissions.

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.

Azure Storage

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.

SQL Server

To create a SQL Server external table, do the following steps:

1. Create a SQL Server connection string. 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 sqlServerConnectionString argument.

Example

The following command creates MySqlExternalTable that refers to MySqlTable table in MyDatabase of SQL Server. The table has two columns, one for an integer x and one for a string s. The connection string contains ;Authentication="Active Directory Managed Identity";User Id=123456789, which indicates to use a user-assigned managed identity with object ID 123456789 to access the table.

.create external table MySqlExternalTable (x:int, s:string) kind=sql table=MySqlTable
( 
    h@'Server=tcp:myserver.database.windows.net,1433;Authentication="Active Directory Managed Identity";User Id=123456789;Initial Catalog=MyDatabase;'
)

Note

To authenticate with a system-assigned managed identity, remove ;User Id={object_id} and only specify ;Authentication="Active Directory Managed Identity".

Related content

• Managed identities overview
• Export data to an external table
• Continuous data export