Configure multi-factor authentication for SQL Server Management Studio and Azure AD
This topic shows you how to use Azure Active Directory multi-factor authentication (MFA) with SQL Server Management Studio. Azure AD MFA can be used when connecting SSMS or SqlPackage.exe to Azure SQL Database and SQL Data Warehouse. For an overview of Azure SQL Database multi-factor authentication, see Universal Authentication with SQL Database and SQL Data Warehouse (SSMS support for MFA).
This topic applies to Azure SQL server, and to both SQL Database and SQL Data Warehouse databases that are created on the Azure SQL server. For simplicity, SQL Database is used when referring to both SQL Database and SQL Data Warehouse.
- 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 Multi-Factor Authentication?, Conditional Access (MFA) with Azure SQL Database and Data Warehouse. (Full Conditional Access requires a Premium Azure Active Directory (Azure AD). Limited MFA is available with a standard Azure AD.)
- Configure SQL Database or SQL Data Warehouse for Azure AD Authentication - For step-by-step instructions, see Connecting to SQL Database or SQL Data Warehouse By Using Azure Active Directory Authentication.
- Download SSMS - On the client computer, download the latest SSMS, from Download SQL Server Management Studio (SSMS). For all the features in this topic, use at least July 2017, version 17.2.
Connecting by using universal authentication with SSMS
The following steps show how to connect to SQL Database or SQL Data Warehouse by using the latest SSMS.
To connect using Universal Authentication, on the Connect to Server dialog box, 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 and SQL Data Warehouse (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.
As usual for SQL Database and SQL Data Warehouse, you must click 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 SQL Data Warehouse (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 Azure SQL Database multi-factor authentication, see Universal Authentication with SQL Database and SQL Data Warehouse (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 an Azure SQL Database 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.