Configure multi-factor authentication for SQL Server Management Studio and Azure AD
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 SqlPackage.exe to Azure 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).
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.
- 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.
- 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.)
- 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.
- 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.
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.
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.)
Complete the User name box with the Azure Active Directory credentials, in the format
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).
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.
Select Options and specify the database on the Options dialog box. (If the connected user is a guest user (i.e. firstname.lastname@example.org), 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.
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.
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:
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).
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.
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.
When verification is complete, SSMS connects normally presuming valid credentials and firewall access.
- For an overview of multi-factor authentication, see Universal Authentication with SQL Database, SQL Managed Instance, and Azure Synapse (SSMS support for MFA).
- Grant others access to your database: SQL Database Authentication and Authorization: Granting Access
- Make sure others can connect through the firewall: Configure a server-level firewall rule using the Azure portal
- When using Active Directory- Universal with MFA authentication, ADAL tracing is available beginning with SSMS 17.3. Off by default, you can turn on ADAL tracing by using the Tools, Options menu, under Azure Services, Azure Cloud, ADAL Output Window Trace Level, followed by enabling Output in the View menu. The traces are available in the output window when selecting Azure Active Directory option.