Databricks SQL

Required connection details

Agile Data Engine supports Databricks SQL. The following details are required per target instance configuration:

*Agile Data Engine requires Unity Catalog.

Configuration

To ensure proper isolation between environments (e.g. DEV, TEST, PROD), follow these best practices:

• Create separate service principals per environment
  This ensures independent authentication and access control scopes across environments.

• Create separate catalogs per environment
  For example: clouddw_dev, clouddw_test, clouddw_prod. This enables logical and permission-based separation of data assets.

• Create separate SQL Warehouses per environment
  For example ade_wh_dev, ade_wh_prod. Control size, auto-stop, and other settings, and track costs at the environment level.

• Recommended: Create a separate Databricks Workspace per environment
  While Unity Catalog enables soft isolation within a single workspace, separate workspaces provide stronger boundaries and simplify environment-specific configuration, access, and audit logging.

Environment setup

Perform provisioning using appropriately authorized administrator identities. Catalog creation requires CREATE CATALOG on the metastore. Keep these provisioning permissions separate from the permissions granted to ADE for routine operation.

• Create SQL warehouses per environment. Open SQL Warehouses > Create SQL warehouse and create a warehouse such as ade_wh_dev. Configure warehouse type, size, auto-stop, and scaling limits for the workload. Add environment tags for cost tracking. See SQL warehouse configuration.

• Create catalogs and schemas per environment. Using an authorized identity and a SQL warehouse, run:

CREATE CATALOG IF NOT EXISTS clouddw_dev;

CREATE SCHEMA IF NOT EXISTS clouddw_dev.public;

This example assumes metastore-level managed storage exists. Otherwise, specify MANAGED LOCATION '<approved-storage-path>' when creating the catalog. The provisioning identity needs CREATE MANAGED STORAGE on the covering external location. See catalog creation requirements.

• Create permission groups per environment. Create an account-level group such as ade_dev through the Databricks account console or your identity-management process. Use account groups, rather than workspace-local groups, for Unity Catalog permissions. Repeat these steps for TEST and PROD. When workspaces share a metastore, restrict catalog access using workspace bindings. Separate workspaces alone do not prevent access to catalogs shared through that metastore.

Service principals

To authenticate Agile Data Engine with Databricks SQL, you must create credentials. Choose one of the following methods, depending on your environment and security requirements:

Option A: Microsoft Entra ID service principal

Authentication type: DATABRICKS_ENTRA_ID_SERVICE_PRINCIPAL

Create a service principal in Microsoft Entra ID, either via:

• Azure Portal, or

• Azure CLI, PowerShell, or Terraform

Save the following values:

• Client ID

• Tenant ID

• Client Secret

This method is recommended for Azure Databricks when using Entra ID for identity management.

Option B: Databricks service principal with OAuth M2M – Recommended

Authentication type: DATABRICKS_SERVICE_PRINCIPAL_OAUTH_M2M

Create a service principal directly within the Databricks workspace UI:

1. Go to Settings → Identity and access → Service principals

2. Click Add service principal

Save the following values:

• Client ID

• Client Secret

This method uses native OAuth tokens managed automatically by the Databricks SDK/driver. No PAT permissions required for the service principal. Recommended for new setups.

Option C: Databricks service principal with PAT generation – Alternative

Authentication type: DATABRICKS_SERVICE_PRINCIPAL

Same setup as Option B, but ADE generates short-lived Personal Access Tokens (PATs) via the Databricks API.

Use this method for backwards compatibility or when OAuth M2M is not available.

Option D: Personal access token (PAT) – For testing only

Authentication type: DATABRICKS_PERSONAL_ACCESS_TOKEN

For proof-of-concept (PoC) work and quick testing:

• Navigate to Settings → Developer → Access tokens

• Click Generate new token

Save the generated:

• Token string

This method is not recommended for production use.

Authentication checks

• Option A: Add or synchronize the Microsoft Entra service principal into Databricks using its application/client ID, and assign it to the target workspace. Creating the identity in Entra alone does not grant Databricks access.

• Option B: After creating the Databricks-managed service principal, generate an OAuth secret from its settings. Record the client ID, secret value, and expiration date. The secret is displayed only once. Follow the OAuth setup instructions.

• Option C: Verify that workspace settings and token permissions allow the service principal to create and use PATs.

• Option D: Permissions apply to the user who owns the token. Grant that user the required workspace, warehouse, and data access for testing. An Entra client secret and a Databricks OAuth secret are not interchangeable. Store credentials securely, share them only through approved secure channels, and rotate them before expiration.

Access grants

Assign the service principal to the appropriate workspace, enable its Databricks SQL access entitlement, and add it to the environment’s account group, for example ade_dev. Verify effective access, including inherited entitlements. See service-principal management. In the SQL warehouse’s Permissions settings, grant CAN USE to ade_dev. Warehouse access and Unity Catalog privileges are separate; both are required. Reserve CAN MANAGE for identities responsible for warehouse administration. As the relevant object owner or another principal authorized to grant access, grant privileges on the catalog and target schemas:

GRANT USE CATALOG ON CATALOG clouddw_dev TO ade_dev;

GRANT USE SCHEMA, CREATE TABLE, SELECT, MODIFY ON SCHEMA clouddw_dev.public TO ade_dev;

Repeat the schema grant for each ADE-managed schema. Schema-level SELECT and MODIFY apply to existing and future tables in that schema. Grant CREATE SCHEMA on the catalog only if ADE needs to create schemas. Have ADE create the objects it manages. For existing objects that ADE must alter or replace, arrange appropriate ownership or operation-specific authorization; SELECT and MODIFY alone do not authorize all deployment DDL. For source-only tables, grant parent catalog/schema usage and SELECT, without write access. See the Unity Catalog privileges reference.

External locations for loading source data

To enable ADE to orchestrate file loads from your cloud storage via Unity Catalog in Databricks, configure external locations in Databricks. See instructions in Databricks documentation.

GRANT READ FILES ON EXTERNAL LOCATION <external location name> TO ade_dev;

Grant WRITE FILES additionally only when ADE must write files, and CREATE EXTERNAL TABLE only when it must create external tables. Neither is required merely to read source files. Source-file access does not grant target-table access; file loads require both.

Network access

Restrict access to trusted networks. Request ADE’s tenant-specific outbound IP address through the ADE Support Portal, then configure Databricks ingress controls to permit ADE and other approved clients.

Where workspace IP access lists are used, remember that they affect the workspace, not only the ADE identity. Preserve required administrator and integration access when enabling restrictions. See Databricks network access controls.

For private connectivity, see Private Connectivity and VPN. Public-IP allowlists do not control Private Link traffic.

Connection validation

Configure the target instance through the Admin UI. Test through ADE using its configured identity and warehouse, rather than an administrator’s session. In DEV, perform a small deployment and load that exercises table creation, writing, reading, and alteration. Remove the disposable test objects afterward. If external file loading is configured, validate a representative source-file load as well.

Privilege summary