Skip to main content

Configure Microsoft Entra ID for SQL Server federation

This page describes how to configure Databricks Lakehouse Federation to run federated queries on Microsoft SQL Server using Microsoft Entra ID authentication. Both the user-to-machine (U2M) and machine-to-machine (M2M) OAuth flows are supported.

Supported OAuth flows​

  • U2M: Authenticate using a Microsoft account. The user is prompted to sign in using a redirect URI, and the access token is issued for the user.
  • M2M: Authenticate using a service principal. The access token is issued for the application instead of for a specific user.

In the Databricks workspace, OAuth refers to U2M authentication, and OAuth Machine to Machine refers to M2M authentication.

Before you begin​

Before you can run federated queries on SQL Server using Entra ID, you must have the following:

  • Access to an Azure subscription and permissions to register applications in Microsoft Entra ID.
  • Admin access to your SQL Server instance to create Entra principals.

Register an application in Microsoft Entra ID​

To create an application registration for authentication, do the following:

  1. Sign in to the Azure portal.
  2. Navigate to Microsoft Entra ID > App registrations > New registration.
  3. Enter a name for your app.
    • For U2M (OAuth), set the redirect URI to the following: https://<workspace-url>/login/oauth/azure.html
    • For M2M (Service Principal), leave the redirect URI empty.
  4. Click Register.
  5. Copy the Application (client) ID and the Directory (tenant) ID.
  6. Navigate to Certificates & secrets > New client secret.
  7. Save the generated secret value.

Assign permissions to the application​

To allow the application to authenticate to SQL Server, assign the required API permissions:

  1. Navigate to API permissions > Add a permission.
  2. Select Azure SQL Database > user_impersonation (Delegated permissions).
  3. For M2M, make sure that the app has the required permissions for service principal authentication.
  4. For M2M auth on Azure SQL Managed Instance, make sure you have assigned the managed instance identity to the "Directory Readers" role.

Create a service principal in SQL Server (M2M only)​

  1. Connect to your SQL Server instance using your Entra ID login credentials. You must have permissions to create a new user.

  2. Create a new login and user for the Entra app.

  3. Grant read permissions to the user.

    SQL
    CREATE LOGIN [<app_name>] FROM EXTERNAL PROVIDER;
    CREATE USER [<app_name>] FROM LOGIN [<app_name>];
    ALTER ROLE db_datareader ADD MEMBER [<app_name>];

For details and advanced scenarios, see the following pages in the Microsoft documentation:

Create a connection​

In the Databricks workspace, do the following:

  1. In the sidebar, click Catalog > Add > Add a connection.
  2. For Connection type, select SQL Server.
  3. For Auth type, select OAuth (U2M) or OAuth Machine to Machine (M2M).
  4. Enter the following connection properties:
    • Host: SQL Server hostname.
    • Port: SQL Server port.
    • User: For U2M, your Microsoft account user. For M2M, the service principal name.
    • Enter the Client ID and Client secret from the Entra app registration.
    • Enter the Authorization Endpoint:
      • U2M: https://login.microsoftonline.com/<tenant-id>/oauth2/v2.0/authorize
      • M2M: https://login.microsoftonline.com/<tenant-id>/oauth2/v2.0/token
    • For OAuth scope, enter https://database.windows.net/.default offline_access (U2M only).
  5. For U2M, click Sign in with Azure Entra ID and complete the authentication flow.
  6. Click Create connection and proceed to catalog creation.

Next steps​

Now that the connection to SQL Server has been created, see Run federated queries on Microsoft SQL Server to create a foreign catalog and query your data.

Last updated on