How to properly authenticate Kusto using a Python client?

Question

I'm trying to test a connection between my node and Azure Data Explorer (ADX/ Kusto). I'm thinking to create a table on Kusto using a python script.

Please be aware that I'm not very familiar with any of this, hence the detailed steps below.

I'm following this quickstart guide on Microsoft docs.

Generate application ID and key

Using App Registrations service:

Create new registration (named kusto test):
Create a client secrets:

Create Kusto DB

From the cluster, create a database from the UI (called kusto-test)

Authorization

On the ADX cluster > Access control (IAM) > Add role assignment.

Python script

from azure.kusto.data.request import KustoClient, KustoConnectionStringBuilder
from azure.kusto.data.exceptions import KustoServiceError
from azure.kusto.data.helpers import dataframe_from_result_table

KUSTO_DATABASE = "kusto-test"
CLUSTER = "https://mynode.myregion.kusto.windows.net"

CLIENT_ID = "KUSTO_TEST_APP_ID" # From image above
CLIENT_SECRET = "KUSTO_TEST_PASS" # From image above

AUTHORITY_ID = "<insert here your tenant id>" #Got from https://login.windows.net/<YourDomain>/.well-known/openid-configuration/

KCSB_DATA = KustoConnectionStringBuilder.with_aad_application_key_authentication(
    CLUSTER, CLIENT_ID, CLIENT_SECRET, AUTHORITY_ID
)


KUSTO_CLIENT = KustoClient(KCSB_DATA)
CREATE_TABLE_COMMAND = ".create table StormEvents (StartTime: datetime, EndTime: datetime, EpisodeId: int, EventId: int, State: string, EventType: string, InjuriesDirect: int, InjuriesIndirect: int, DeathsDirect: int, DeathsIndirect: int, DamageProperty: int, DamageCrops: int, Source: string, BeginLocation: string, EndLocation: string, BeginLat: real, BeginLon: real, EndLat: real, EndLon: real, EpisodeNarrative: string, EventNarrative: string, StormSummary: dynamic)"

RESPONSE = KUSTO_CLIENT.execute_mgmt(KUSTO_DATABASE, CREATE_TABLE_COMMAND)

dataframe_from_result_table(RESPONSE.primary_results[0])

Expected:

Have the table successfully created on ADX.

Actual:

Getting the UnauthorizedDatabaseAccessException error.

azure.kusto.data.exceptions.KustoServiceError: (KustoServiceError(...), [{u'error': {u'code': u'Forbidden', u'@permanent': True, u'@message': u"Principal '....' is not authorized to access database 'kusto-test'.", ...}, u'message': u'Caller is not authorized to perform this action', u'@type': u'Kusto.DataNode.Exceptions.UnauthorizedDatabaseAccessException'}}])

Avnera Avnera · Accepted Answer · 2019-05-28T05:05:26

Adding an owner in the Azure portal "access control" only provides that entity with permission to manage the resource (also known as the 'control plane') and is not applicable to the permissions on the database itself (also known as the 'data plane').

To provide that application permission to operate in the data plane for example to run queries, create tables etc. you need to give it permission in the applicable database "Permissions" section:

How to properly authenticate Kusto using a Python client?

1 Answers