Skip to content

Latest commit

 

History

History
194 lines (127 loc) · 9.94 KB

external-tables-managed-identities.md

File metadata and controls

194 lines (127 loc) · 9.94 KB
Raw
title description ms.reviewer ms.topic ms.date
How to authenticate using managed identities with external tables in Azure Data Explorer
Learn how to use managed identities with external tables in Azure Data Explorer cluster.
itsagui
how-to
04/20/2023

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.

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

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.

Cosmos DB

To import or query data from the Cosmos DB database, grant the managed identity read 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 command to create the table. Use the connection string as the SqlConnectionString 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".

Cosmos DB

To create a Cosmos DB external table, do the following steps:

  1. Create a Cosmos DB 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 command to create the table. Use the connection string as the SqlConnectionString argument.

Example

User-assigned managed identity

The following command creates MyCosmosDbExternalTable that refers to data in MyCollection in database MyDatabase of Cosmos DB account mycosmos.

  • The connection string contains ;Authentication="Active Directory Managed Identity";User Id=123456789, which indicates to use a user-assigned managed identity with an object ID of 123456789 to access the table.
.create external table MyCosmosDbExternalTable (x:int, s:string) kind=sql
( 
    h@'AccountEndpoint=https://mycosmos.documents.azure.com:443/;Database=MyDatabase;Collection=MyCollection;Authentication="Active Directory Managed Identity";User Id=123456789;'
)
with 
(
   sqlDialect = "CosmosDbSQL"
)
System-assigned managed identity

The connection string contains only ;Authentication="Active Directory Managed Identity";, which indicates to use a system-assigned managed identity

.create external table MyCosmosDbExternalTable (x:int, s:string) kind=sql
( 
    h@'AccountEndpoint=https://mycosmos.documents.azure.com:443/;Database=MyDatabase;Collection=MyCollection;Authentication="Active Directory Managed Identity";'
)
with 
(
   sqlDialect = "CosmosDbSQL"
)

Related content