Skip to main content

Run federated queries on Microsoft SQL Server

This article describes how to set up Lakehouse Federation to run federated queries on SQL Server data that is not managed by Databricks. To learn more about Lakehouse Federation, see What is Lakehouse Federation?.

To connect to your SQL Server database using Lakehouse Federation, you must create the following in your Databricks Unity Catalog metastore:

  • A connection to your SQL Server database.
  • A foreign catalog that mirrors your SQL Server database in Unity Catalog so that you can use Unity Catalog query syntax and data governance tools to manage Databricks user access to the database.

Lakehouse Federation supports SQL Server, Azure SQL Database, and Azure SQL Managed Instance.

Before you begin​

Workspace requirements:

  • Workspace enabled for Unity Catalog.

Compute requirements:

  • Network connectivity from your compute resource to the target database systems. See Networking recommendations for Lakehouse Federation.
  • Databricks compute must use Databricks Runtime 13.3 LTS or above and Standard or Dedicated access mode.
  • SQL warehouses must be pro or serverless and must use 2023.40 or above.

Permissions required:

  • To create a connection, you must be a metastore admin or a user with the CREATE CONNECTION privilege on the Unity Catalog metastore attached to the workspace.
  • To create a foreign catalog, you must have the CREATE CATALOG permission on the metastore and be either the owner of the connection or have the CREATE FOREIGN CATALOG privilege on the connection.

Additional permission requirements are specified in each task-based section that follows.

  • If you plan to authenticate using OAuth, register an app in Microsoft Entra ID for Databricks. See the following section for details.

(Optional) Register an app in Microsoft Entra ID for Databricks​

If you want to authenticate using OAuth, follow this step before you create a SQL Server connection. To authenticate using a username and password instead, skip this section.

note

Only the User-to-Machine OAuth method is supported. Machine-to-Machine (service principal) authentication is not supported.

  1. Sign in to the Azure portal.
  2. In the left navigation, click Microsoft Entra ID.
  3. Click App registrations.
  4. Click New registration. Enter a name for the new app and set the redirect URI to https://<workspace-url>/login/oauth/azure.html.
  5. Click Register.
  6. In the Essentials box, copy and store the Application (client) ID. You'll use this value to configure the application.
  7. Click Certificates & secrets.
  8. On the Client secrets tab, click New client secret.
  9. Enter a description for the secret and an expiration (the default setting is 180 days).
  10. Click Add.
  11. Copy the generated value for the client secret.
  12. Click API permissions.
  13. Click Add a permission.
  14. Select Azure SQL Database and click user_impersonation under Delegated permissions.
  15. Click Add permissions.

Create a connection​

A connection specifies a path and credentials for accessing an external database system. To create a connection, you can use Catalog Explorer or the CREATE CONNECTION SQL command in a Databricks notebook or the Databricks SQL query editor.

note

You can also use the Databricks REST API or the Databricks CLI to create a connection. See POST /api/2.1/unity-catalog/connections and Unity Catalog commands.

Permissions required: Metastore admin or user with the CREATE CONNECTION privilege.

  1. In your Databricks workspace, click Catalog icon Catalog.

  2. At the top of the Catalog pane, click the Add or plus icon Add icon and select Add a connection from the menu.

    Alternatively, from the Quick access page, click the External data button, go to the Connections tab, and click Create connection.

  3. On the Connection basics page of the Set up connection wizard, enter a user-friendly Connection name.

  4. Select a Connection type of SQL Server.

  5. Select an Auth type of OAuth or Username and password (basic authentication).

  6. (Optional) Add a comment.

  7. Click Next.

  8. On the Authentication page, enter the following connection properties for your SQL Server instance. Properties that are specific to the authentication method you selected are preceded by the Auth type in parentheses.

    • Host: Your SQL server.
    • (Basic authentication) Port
    • (Basic authentication) trustServerCertificate: Defaults to false. When set to true, the transport layer uses SSL to encrypt the channel and bypasses the certificate chain to validate trust. Leave this set to the default unless you have a specific need to bypass trust validation.
    • (Basic authentication) User
    • (Basic authentication) Password
    • (OAuth) Authorization Endpoint: Your Azure Entra authorization endpoint in the format https://login.microsoftonline.com/<tenant-id>/oauth2/v2.0/authorize.
    • (OAuth) Client ID from the app you created.
    • (OAuth) Client secret from the client secret you created.
    • (OAuth) OAuth scope: Enter the following value with no modifications: https://database.windows.net/.default offline_access.
    • (OAuth) Click Sign in with Azure Entra ID. Enter your Azure username and password. After you're redirected to the Authentication page, the authorization code is populated in the UI.

  9. Click Create connection.

  10. (Basic authentication) On the Connection details page, specify the following:

    • Trust server certificate: This is deselected by default. When selected, the transport layer uses SSL to encrypt the channel and bypasses the certificate chain to validate trust. Leave this set to the default unless you have a specific need to bypass trust validation.
    • Application intent: The application workload type when connecting to a server.

  11. Click Next.

  12. On the Catalog basics page, enter a name for the foreign catalog. A foreign catalog mirrors a database in an external data system so that you can query and manage access to data in that database using Databricks and Unity Catalog.

  13. Click Create catalog.

  14. On the Access page, select the workspaces in which users can access the catalog you created. You can select All workspaces have access, or click Assign to workspaces, select the workspaces, and then click Assign.

  15. Change the Owner who will be able to manage access to all objects in the catalog. Start typing a principal in the text box, and then click the principal in the returned results.

  16. Grant Privileges on the catalog. Click Grant:

    1. Specify the Principals who will have access to objects in the catalog. Start typing a principal in the text box, and then click the principal in the returned results.
    2. Select the Privilege presets to grant to each principal. All account users are granted BROWSE by default.
      • Select Data Reader from the drop-down menu to grant read privileges on objects in the catalog.
      • Select Data Editor from the drop-down menu to grant read and modify privileges on objects in the catalog.
      • Manually select the privileges to grant.
    3. Click Grant.

  17. Click Next.

  18. On the Metadata page, specify tags key-value pairs. For more information, see Apply tags to Unity Catalog securable objects.

  19. (Optional) Add a comment.

  20. Click Save.

note

(OAuth) The Azure Entra ID OAuth endpoint must be accessible from Databricks control plane IPs. See Databricks clouds and regions.

Create a foreign catalog​

note

If you use the UI to create a connection to the data source, foreign catalog creation is included and you can skip this step.

A foreign catalog mirrors a database in an external data system so that you can query and manage access to data in that database using Databricks and Unity Catalog. To create a foreign catalog, you use a connection to the data source that has already been defined.

To create a foreign catalog, you can use Catalog Explorer or the CREATE FOREIGN CATALOG SQL command in a Databricks notebook or the SQL query editor. You can also use the Databricks REST API or the Databricks CLI to create a catalog. See POST /api/2.1/unity-catalog/catalogs and Unity Catalog commands.

Permissions required: CREATE CATALOG permission on the metastore and either ownership of the connection or the CREATE FOREIGN CATALOG privilege on the connection.

  1. In your Databricks workspace, click Catalog icon Catalog to open Catalog Explorer.

  2. At the top of the Catalog pane, click the Add or plus icon Add icon and select Add a catalog from the menu.

    Alternatively, from the Quick access page, click the Catalogs button, and then click the Create catalog button.

  3. Follow the instructions for creating foreign catalogs in Create catalogs.

Supported pushdowns​

The following pushdowns are supported on all compute:

  • Filters
  • Projections
  • Limit
  • Functions: partial, only for filter expressions. (String functions, Mathematical functions, Data, Time and Timestamp functions, and other miscellaneous functions, such as Alias, Cast, SortOrder)

The following pushdowns are supported on Databricks Runtime 13.3 LTS and above, and on SQL warehouse compute:

  • Aggregates
  • The following Boolean operators: =, <, <=, >, >=, <=>
  • The following mathematical functions (not supported if ANSI is disabled): +, -, *, %, /
  • The following miscellaneous operators: ^, |, ~
  • Sorting, when used with limit

The following pushdowns are not supported:

  • Joins
  • Windows functions

Data type mappings​

When you read from SQL Server to Spark, data types map as follows:

SQL Server type

Spark type

bigint (unsigned), decimal, money, numeric, smallmoney

DecimalType

smallint, tinyint

ShortType

int

IntegerType

bigint (if signed)

LongType

real

FloatType

float

DoubleType

char, nchar, uniqueidentifier

CharType

nvarchar, varchar

VarcharType

text, xml

StringType

binary, geography, geometry, image, timestamp, udt, varbinary

BinaryType

bit

BooleanType

date

DateType

datetime, datetime, smalldatetime, time

TimestampType/TimestampNTZType

*When you read from SQL Server, SQL Server datetimes are mapped to Spark TimestampType if preferTimestampNTZ = false (default). SQL Server datetimes are mapped to TimestampNTZType if preferTimestampNTZ = true.

Last updated on