Data access configuration

  • Article

This article describes the data access configurations performed by Azure Databricks administrators for all SQL warehouses (formerly SQL endpoints) using the UI.

To configure all SQL warehouses using the REST API, see Global SQL Warehouses API.

Important

Changing these settings restarts all running SQL warehouses.

For a general overview of how to enable access to data, see Access control.

Requirements

You must be an Azure Databricks administrator to configure settings for all SQL warehouses.

Configure a service principal

To configure access for your SQL warehouses to an Azure Data Lake Storage Gen2 storage account using service principals, follow these steps:

  1. Register an Azure AD application and record the following properties:

    • Application (client) ID: An ID that uniquely identifies the Azure Active Directory application.
    • Directory (tenant) ID: An ID that uniquely identifies the Azure Active Directory instance (called directory (tenant) ID in Azure Databricks).
    • Client secret: The value of a client secret created for this application registration. The application will use this secret string to prove its identity.

  2. On your storage account, add a role assignment for the application registered at the previous step to give it access to the storage account.

  3. Create an Azure Key Vault-backed secret scope or a Databricks-scoped secret scope, and record the value of the scope name property:

    • Scope name: The name of the created secret scope.

  4. If using the Azure Key Vault, go to the Secrets section and create a new secret with a name of your choice. Use the “client secret” that you have obtained in Step 1 to populate the “value” field of this secret. Keep a record of the secret name that you just chose.

    • Secret name: The name of the created Azure Key Vault secret.

  5. If using a Databricks-backed scope, create a new secret using the Databricks CLI and use it to store the client secret that you have obtained in Step 1. Keep a record of the secret key that you entered at this step.

    • Secret key: The key of the created Databricks-backed secret.

    Note

    Optionally, you can create an additional Secret to store the client ID that you have obtained at Step 1.

  6. Click your username in the top bar of the workspace and select Admin Settings from the drop down.

  7. Click the SQL Warehouse settings tab.

  8. In the Data Access Configuration field, click the Add Service Principal button.

  9. Configure the properties for your Azure Data Lake Storage Gen2 storage account.

  10. Click Add.

    ADLS2 storage account

    You will see that new entries have been added to the Data Access Configuration textbox.

  11. Click Save.

Note

You can also edit the Data Access Configuration textbox entries directly.

Important

To set a configuration property to the value of a secret without exposing the secret value to Spark, set the value to {{secrets/<secret-scope>/<secret-name>}}. Replace <secret-scope> with the secret scope and <secret-name> with the secret name. The value must start with {{secrets/ and end with }}. For more information about this syntax, see Syntax for referencing secrets in a Spark configuration property or environment variable.

Configure data access properties for SQL warehouses

To configure all warehouses with data access properties:

  1. Click your username in the top bar of the workspace and select Admin Settings from the drop down.

  2. Click the SQL Warehouse Settings tab.

  3. In the Data Access Configuration textbox, specify key-value pairs containing metastore properties.

    Important

    To set a Spark configuration property to the value of a secret without exposing the secret value to Spark, set the value to {{secrets/<secret-scope>/<secret-name>}}. Replace <secret-scope> with the secret scope and <secret-name> with the secret name. The value must start with {{secrets/ and end with }}. For more information about this syntax, see Syntax for referencing secrets in a Spark configuration property or environment variable.

  4. Click Save.

You can also configure data access properties with the Databricks Terraform provider and databricks_sql_global_config.

Supported properties

The following properties are supported for SQL warehouses. For an entry that ends with *, all properties within that prefix are supported. For example, spark.sql.hive.metastore.* indicates that both spark.sql.hive.metastore.jars and spark.sql.hive.metastore.version are supported, as well as any other properties that start with spark.sql.hive.metastore.

For properties whose values contain sensitive information, you can store the sensitive information in a secret and set the property’s value to the secret name using the following syntax: secrets/<secret-scope>/<secret-name>.

  • spark.sql.hive.metastore.*
  • spark.sql.warehouse.dir
  • spark.hadoop.datanucleus.*
  • spark.hadoop.fs.*
  • spark.hadoop.hive.*
  • spark.hadoop.javax.jdo.option.*
  • spark.hive.*

For more information about how to set these properties, see External Hive metastore.