Using Azure Active Directory Multi-Factor Authentication

Applies to: Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics SQL Server on Azure VM Azure Arc-enabled SQL Server

Azure Active Directory (Azure AD) Multi-Factor Authentication is a security feature provided by Microsoft's cloud-based identity and access management service. Multifactor authentication (MFA) enhances the security of user sign-ins by requiring users to provide extra verification steps beyond a password. MFA is a supported authentication method for Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics, and SQL Server 2022 (16.x) and later versions.

This article provides a brief overview of the benefits of multifactor authentication, explains how to configure it with Azure AD, and shows how to use it to connect to a database with SQL Server Management Studio (SSMS).

Important

Databases in Azure SQL Database, Azure SQL Managed Instance, Azure Synapse, and SQL Server 2022 are referred to collectively in the remainder of this article as databases, and the server is referring to the server that hosts databases for Azure SQL Database and Azure Synapse.

Benefits of MFA

Azure AD Multi-Factor Authentication helps safeguard access to data and applications while meeting user demand for a simple sign-in process. MFA adds an extra layer of security to user sign-ins by requiring users to provide two or more authentication factors. These factors typically include something the user knows (password), something the user possesses (smartphone or hardware token), and/or something the user is (biometric data). By combining multiple factors, MFA significantly reduces the likelihood of unauthorized access.

Azure AD Multi-Factor Authentication provides all the benefits of Azure AD authentication described in the Azure AD authentication overview.

For the full list of authentication methods available, see What authentication and verification methods are available in Azure Active Directory?

Configuration steps

  1. Configure an Azure Active Directory - For more information, see Administering your Azure AD directory, Integrating your on-premises identities with Azure Active Directory, Add your own domain name to Azure AD, Federation with Azure AD, and Manage Azure AD using Windows PowerShell.
  2. Configure MFA - For step-by-step instructions, see What is Azure AD Multi-Factor Authentication?, Conditional Access (MFA) with Azure SQL Database and Data Warehouse. (Full Conditional Access requires a Premium Azure Active Directory. Limited MFA is available with a standard Azure AD.)
  3. Configure Azure AD Authentication - For step-by-step instructions, see Connecting to SQL Database, SQL Managed Instance, or Azure Synapse using Azure Active Directory Authentication.
  4. Download SSMS - On the client computer, download the latest SSMS, from Download SQL Server Management Studio (SSMS).

Note

In December 2021, releases of SSMS prior to 18.6 will no longer authenticate through Azure Active Directory with MFA.

To continue utilizing Azure Active Directory authentication with MFA, you need SSMS 18.6 or later.

Azure AD B2B support

Azure AD multifactor authentication also supports Azure AD B2B scenarios, which enable businesses to invite guest users to collaborate with their organization. Guest users can connect to databases either as individual users or members of an Azure AD group. For more information, see Create guest user in SQL Database, Azure Synapse, and SQL Managed Instance.

Connect using MFA in SSMS

The following steps show how to connect using multifactor authentication in the latest SSMS.

  1. To connect using MFA, on the Connect to Server dialog box in SSMS select Active Directory - Universal with MFA.

    Screenshot of the Connect to Server dialog in SSMS. "Azure Active Directory - Universal with MFA" is selected from the authentication dropdown window.

  2. Fill the Server name box with your server's name. Fill the User name box with your Azure AD credentials, in the format user_name@domain.com.

    Screenshot of the Connect to Server dialog settings in SSMS, with all fields filled in.

  3. Click Connect.

  4. When the Sign in to your account dialog box appears, it should be prepopulated with the User name you provided in step 2. No password is required if a user is part of a domain federated with Azure AD.

    Screenshot of the Sign in to your account dialog for Azure SQL Database and Data Warehouse. the account name is filled in.

  5. You'll be prompted to authenticate using one of the methods configured based on the MFA administrator setting.

  6. When verification is complete, SSMS connects normally, presuming valid credentials and firewall access.

Azure AD multifactor authentication is a supported authentication method for all SQL tools. For information on programmatically authenticating with Azure AD, see the Overview of the Microsoft Authentication Library (MSAL).

Next steps