Edit

Configure Azure SQL Managed Instance for Windows Authentication for Azure Active Directory

  • Article

This article describes how to configure a managed instance to support Windows Authentication for Azure AD principals. 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 Azure AD 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
Azure Active Directory PowerShell Module This module provides management cmdlets for Azure AD administrative tasks such as user and service principal management.

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

Configure Azure AD Authentication for Azure SQL Managed Instance

To enable Windows Authentication for Azure AD 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. Under 'System assigned service principal' the radio button next to the 'Status' label has been set to 'On'.
  5. Select Save.

  1. Sign in to the Azure portal.

  2. Open Azure Active Directory.

  3. Select App registrations.

  4. Select All applications. Screenshot of the Azure portal. Azure Active Directory is open. App registrations is selected in the left pane. App applications is highlighted in the right 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 Azure AD principals on Azure SQL Managed Instance: