Rediger

Configure multi-factor authentication for SQL Server Management Studio and Azure AD

  • Artikkel

Applies to: Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics

This article shows you how to use Azure Active Directory (Azure AD) multi-factor authentication (MFA) with SQL Server Management Studio (SSMS). Azure AD MFA can be used when connecting SSMS or SqlPackageAzure SQL Database, Azure SQL Managed Instance and Azure Synapse Analytics. For an overview of multi-factor authentication, see Universal Authentication with SQL Database, SQL Managed Instance, and Azure Synapse (SSMS support for MFA).

Important

Databases in Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse 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.

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, Microsoft Azure now supports federation with Windows Server Active Directory, 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).

Connecting by using universal authentication with SSMS

The following steps show how to connect using the latest 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.

  1. To connect using Universal Authentication, on the Connect to Server dialog box in SQL Server Management Studio (SSMS), select Active Directory - Universal with MFA support. (If you see Active Directory Universal Authentication you are not on the latest version of SSMS.)

    Screenshot of the Connection Properties tab in the Connect to Server dialog in S S M S. "MyDatabase" is selected in the Connect to database dropdown.

  2. Complete the User name box with the Azure Active Directory credentials, in the format user_name@domain.com.

    Screenshot of the Connect to Server dialog settings for Server type, Server name, Authentication, and User name.

  3. If you are connecting as a guest user, you no longer need to complete the AD domain name or tenant ID field for guest users because SSMS 18.x or later automatically recognizes it. For more information, see Universal Authentication with SQL Database, SQL Managed Instance, and Azure Synapse (SSMS support for MFA).

    Screenshot of the Connection Properties tab in the Connect to Server dialog in S S M S. "MyDatabase" is selected in the Connect to database dropdown.

    However, If you are connecting as a guest user using SSMS 17.x or older, you must click Options, and on the Connection Property dialog box, and complete the AD domain name or tenant ID box.

    Screenshot of the Connection Properties tab in the Connect to Server dialog in S S M S.The option AD domain name or tenant ID property is filled in.

  4. Select Options and specify the database on the Options dialog box. (If the connected user is a guest user (i.e. joe@outlook.com), you must check the box and add the current AD domain name or tenant ID as part of Options. See Universal Authentication with SQL Database and Azure Synapse Analytics (SSMS support for MFA). Then click Connect.

  5. When the Sign in to your account dialog box appears, provide the account and password of your Azure Active Directory identity. 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 and password are filled in.

    Note

    For Universal Authentication with an account that does not require MFA, you connect at this point. For users requiring MFA, continue with the following steps:

  6. Two MFA setup dialog boxes might appear. This one time operation depends on the MFA administrator setting, and therefore may be optional. For an MFA enabled domain this step is sometimes pre-defined (for example, the domain requires users to use a smartcard and pin).

    Screenshot of the Sign in to your account dialog for Azure SQL Database and Data Warehouse with a prompt to set up additional security verification.

  7. The second possible one time dialog box allows you to select the details of your authentication method. The possible options are configured by your administrator.

    Screenshot of the Additional security verification dialog with options for selecting and configuring an authentication method.

  8. The Azure Active Directory sends the confirming information to you. When you receive the verification code, enter it into the Enter verification code box, and click Sign in.

    Screenshot of the Sign in to your account dialog for Azure SQL Database and Data Warehouse with a prompt to Enter a verification code.

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

Next steps