Search code examples
azure-databricks

Azure Databricks SQL Execution API

I tried to create Databricks token through service principal instead of personal access token. Below is the code that I run:

host = 'host'
client_id = 'clientId'
oauth_secret = 'secretKey' 
endpoint = '/oidc/v1/token'
url = f'{host}{endpoint}'

data = {
  "grant_type": "client_credentials",
  "scope": "all-apis"
}

auth = (client_id, oauth_secret)
oauth_response = requests.post(url, data=data, auth=auth)
oauth_token = oauth_response.json()['access_token']

endpoint = '/api/2.0/token/create'
url = f'{host}{endpoint}'

headers = {
    "Authorization": f"Bearer {oauth_token}"
}
payload = {
    "comment": "TestTokenCreation"
}

response = requests.post(url, headers=headers, json=payload)
response.json()

The output that I got is:

{'token_value': 'dapixxxxxxxxxxx',
 'token_info': {'token_id': 'xxxxxxxxxxxxxxxxxxx',
  'creation_time': 1715055980619,
  'expiry_time': -1,
  'comment': 'TestTokenCreation'}}

I managed to get the token value as shown in the output above. I tried to use the token value as the authorization token to query the databricks warehouse data (Databricks SQL Execution API). However, I got the following error:

[INSUFFICIENT_PERMISSIONS] Insufficient privileges:
User does not have permission SELECT on table `brxxxx8`.`0xxxxxxx`.
User does not have permission USAGE on database `brxxxx8`. SQLSTATE: 42501

Since the token is generated using the service principal, I suspect is the service principal access issue. However, I already grant the Databricks SQL access to the service principal, and also grant the 'Can manage' permission for the warehouse to the service principal, yet the error still persist. Anyone know what's going on? Any help or advise will be greatly appreciated!

Error:

enter image description here

Solution

  • The error is because of the service principal you are using is not having select and usage permissions.

    To enable follow below steps.

    enter image description here

    Go To > Catalog > Your schema or database > Permissions and click on Grant.

    You will get the below option.

    enter image description here

    Here, search for your service principal and select.

    Then give necessary permissions and click on Grant.

    enter image description here

    Know make the statement execution post request, the query will execute.

    EDIT

    Select sql warehouse while adding permission.