We have been strugling with missing of that ODBC connector aswell. But I was able to create custom connector that can be used to call ADB SQL API. If this is something you are interested in here is how you can set it up.
- Create new application/service pincipal in Entra ID(Azure AD) and create secret for it (save it somewhere safe for later use)
- Provision that SP also to ADB
- Assign needed rights on the WS/SQL WH level
- In PowerApps create new custom connector
- On General tab fill in your ADB WS hostname and SQL API as base URL (/api/2.0/sql/statements/)
- On Security tab:
select Auth. type: OAuth 2.0
Identity provider: Azure Active directory
Mark/Tick Service principal support
Client ID: client ID of your service principal (you find that in Entra ID/Azure AD)
Client secret: fill created secret from step I. when you were creating SP
Authorization URL: https://login.microsoftonline.com
Tenant ID: common
Resource URL: 2ff814a6-3304-4ab8-85cb-cd0e6f879c1d (default ID for ADB in Entra ID/AAD)
Enable on-behalf-of login: false
Scope: fill in your ADB WS URL + SQL API example:https://adb-000000000000000.0.azuredatabricks.net/api/2.0/sql/statements/
Whole security tab example:
- On Definition tab fill the first section as you need for example:
In the request section click on Import from sample:
URL: ADB WS URL +/api/2.0/sql/statements/
Headers: Content-Type application/json
Body (can be modified as reqiured by your needs):
{
"warehouse_id": "your warehouse ID",
"catalog": "name of your catalog",
"schema": "name of your schema",
"statement": "your select statement for example select * from XY",
"wait_timeout": "how long should it wait in case your query is longer or your SQL WH is not running example: 60s"
}
- AI Plugin - can be skipped
- Code any custom code you need for example to modify the request or response (optional)
- Click create the connector
- Once connector was created click on Test tab and create new connection. There from drop down menu select Service principal connection:
- Fill in details about your SP and Tentant and click create:
If there was no issue with your setup you should be redirected to your connections and there will be your connection with state "Connected":
If you want to test the connection go back to custom connector you just created and edit your connetor by clicking on the pencil:
Go to the Test tab select correct connection you just crated:
Fill in details for your request and click "test operation":
Please note your ADB SQL WH should be running at that time or it will start it by that request. In case you have wait_timeout set too low it can return as pending state waiting for SQH WH to start :
If SQL WH is running you should recieve result of your query:
Hope this will help someone 🙂
Stanislav R.
