Data access configuration

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

To configure all SQL endpoints using the REST API, see Global SQL Endpoints API.

Important

Changing these settings restarts all running SQL endpoints.

For a general overview of how to enable access to data, see Databricks SQL security model and data access overview.

Requirements

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

Configure a service principal

To configure all endpoints to use an Azure service principal to access an Azure Data Lake Storage Gen2 storage account:

  1. Create an Azure AD application and service principal that can access resources. Record the following properties:

    • application-id: An ID that uniquely identifies the Azure Active Directory application.
    • directory-id: An ID that uniquely identifies the Azure Active Directory instance (called directory (tenant) ID in Azure Databricks).
    • client secret: The client secret created for the service principal.
  2. Create an Azure Key Vault-backed secret scope and save the client secret in the Azure key vault. Record the following properties:

    • scope-name: The name of the created secret scope.
    • secret-name: The name of the created secret.
  3. Click User Settings Icon Settings at the bottom of the sidebar and select SQL Admin Console.

  4. Click the SQL Endpoint Settings tab.

    Data access configuration

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

    Add service principal

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

  7. Click Add.

  8. Click Save.

Configure data access properties

To configure all endpoints with data access properties:

  1. Click User Settings Icon Settings at the bottom of the sidebar and select SQL Admin Console.

  2. Click the SQL Endpoint 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 Path value.

  4. Click Save.

Supported properties

The following properties are supported for SQL endpoints. 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 details on how to set these properties, see External Hive metastore.