Azure Databricks SQL Execution API

Question

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:

Answer 1

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

To enable follow below steps.

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

You will get the below option.

Here, search for your service principal and select.

Then give necessary permissions and click on Grant.

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

EDIT

Select sql warehouse while adding permission.