Create and configure the Databricks integration

Create a Databricks connection

To create a Databricks connections, run the following command in the SQL editor:

CREATE CONNECTION <UC_CONNECTION_NAME>

TYPE SQLSERVER

OPTIONS (

host '<AZURE_SQL_SERVER>.database.windows.net',

port '1433',

user '<AZURE_SQL_USER>',

password '<AZURE_SQL_PASSWORD>',

trustServerCertificate 'false'

);

Databricks can now connect to the AIM Reporting Database through BI Gateway.

Create a foreign catalog in Databricks

You can also store the password in Databricks secret. To do so, perform the following steps:

1. Create a secret scope.

   databricks secrets create-scope <SECRET_SCOPE>

2. Store the database password.

   databricks secrets put-secret <SECRET_SCOPE> <SECRET_KEY>

3. Reference the secret in the connection by replacing the 'password' field with your secret in the connection.

   password '{{secrets/<SECRET_SCOPE>/<SECRET_KEY>}}'

To create a foreign catalog in Databricks, run the following command in the SQL editor:

CREATE FOREIGN CATALOG <FOREIGN_CATALOG_NAME>

USING CONNECTION <UC_CONNECTION_NAME>

OPTIONS (

database '<AZURE_SQL_DATABASE>'

);

The AIM Reporting Database is now visible in Databricks as a catalog.

Validate the catalog creation

Verify that Databricks can see the schemas exposed by the database by running the following command:

SHOW SCHEMAS IN <FOREIGN_CATALOG_NAME>;

Configure schema access

Databricks automatically discovers all schemas in the source database. To ensure users can access only approved AIM reporting data, you must explicitly grant access to the aim schema and revoke access to all other schemas using Unity Catalog permissions.

Grant access to the AIM reporting schema

To grant access to the AIM reporting schema, run the following command in the SQL editor:

GRANT USE CATALOG ON CATALOG <FOREIGN_CATALOG_NAME> TO <ENDUSER_GROUP>;

GRANT USE SCHEMA ON SCHEMA <FOREIGN_CATALOG_NAME>.aim TO <ENDUSER_GROUP>;

GRANT SELECT ON ALL TABLES IN SCHEMA <FOREIGN_CATALOG_NAME>.aim TO <ENDUSER_GROUP>;

Revoke access to other schemas

To revoke access to other schemas, run the following command in the SQL editor:

SELECT CONCAT(

'REVOKE USE SCHEMA ON SCHEMA <FOREIGN_CATALOG_NAME>.',

schema_name,

' FROM <ENDUSER_GROUP>;'

) AS sql_to_run

FROM <FOREIGN_CATALOG_NAME>.information_schema.schemata

WHERE LOWER(schema_name) <> 'aim'

ORDER BY schema_name;

Common schemas that are typically revoked include:

• db_accessadmin

• db_backupoperator

• db_datareader

• db_datawriter

• db_ddladmin

• db_denydatareader

• db_denydatawriter

• db_owner

• db_securityadmin

• dbo , guest

• INFORMATION_SCHEMA

• sys