Authenticate external tables with managed identities

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.

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.

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.

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.

To import or query data from the Cosmos DB database, grant the managed identity read permissions. To learn more, see Permissions.

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.chinacloudapi.cn/mycontainer;managed_identity=system' 
)

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.chinacloudapi.cn,1433;Authentication="Active Directory Managed Identity";User Id=123456789;Initial Catalog=MyDatabase;'
)

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.cn: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.cn:443/;Database=MyDatabase;Collection=MyCollection;Authentication="Active Directory Managed Identity";'
)
with 
(
   sqlDialect = "CosmosDbSQL"
)