Configure Azure SQL Managed Instance for Windows Authentication for Microsoft Entra ID

  • Article

Applies to: Azure SQL Managed Instance

This article describes how to configure a managed instance to support Windows Authentication for principals in Microsoft Entra ID (formerly Azure Active Directory). The steps to set up Azure SQL Managed Instance are the same for both the incoming trust-based authentication flow and the modern interactive authentication flow.

Prerequisites

The following prerequisites are required to configure a managed instance for Windows Authentication for Microsoft Entra principals:

Prerequisite Description
Az.Sql PowerShell module This PowerShell module provides management cmdlets for Azure SQL resources.

Install this module by running the following PowerShell command: Install-Module -Name Az.Sql
Microsoft Graph PowerShell Module This module provides management cmdlets for Microsoft Entra administrative tasks such as user and service principal management.

Install this module by running the following PowerShell command: Install-Module -Name Microsoft.Graph
A managed instance You may create a new managed instance or use an existing managed instance. You must enable Microsoft Entra authentication on the managed instance.

Configure Microsoft Entra authentication for Azure SQL Managed Instance

To enable Windows Authentication for Microsoft Entra principals, you need to enable a system-assigned service principal on each managed instance. The system-assigned service principal allows managed instance users to authenticate using the Kerberos protocol. You also need to grant admin consent to each service principal.

Enable a system assigned service principal

To enable a system assigned service principal for a managed instance:

  1. Sign in to the Azure portal.
  2. Navigate to your managed instance.
  3. Select Identity.
  4. Set System assigned service principal to On. Screenshot of the identity pane for a managed instance in the Azure portal, with 'System assigned service principal' set to 'On'.
  5. Select Save.

  1. Sign in to the Azure portal.

  2. Open Microsoft Entra ID.

  3. Select App registrations.

  4. Select All applications. Screenshot of the Microsoft Entra ID resource in the Azure portal with App registrations selected in the left pane.

  5. Select the application with the display name matching your managed instance. The name will be in the format: <managedinstancename> principal.

  6. Select API permissions.

  7. Select Grant admin consent.

    Screenshot from the Azure portal of the configured permissions for applications. The status for the example application is 'Granted for aadsqlmi'.

  8. Select Yes on the prompt to Grant admin consent confirmation.

Connect to the managed instance with Windows Authentication

If you have already implemented either the incoming trust-based authentication flow or the modern interactive authentication flow, depending on the version of your client, you can now test connecting to your managed instance with Windows Authentication.

To test the connection with SQL Server Management Studio (SSMS), follow the steps in Quickstart: Use SSMS to connect to and query Azure SQL Database or Azure SQL Managed Instance. Select Windows Authentication as your authentication type.

Dialog box from SQL Server Management Studio with a managed instance name in the 'Server Name' area and 'Authentication' set to 'Windows Authentication'.

Next steps

Learn more about implementing Windows Authentication for Microsoft Entra principals on Azure SQL Managed Instance: