What are the new login options for Sql Studio (v17) such as "Active Directory - Integrated"

Question

I've been using Sql and Sql Studio for many years, so I was surprised when I first saw these three new options in the authentication dialog...

• Active Directory - Universal with MFA support - MultiFactory Authentication)
• Active Directory - Password
• Active Directory - Integrated (seems redundant to Windows Authentication)

For probably over 15 years, we've only had the first two options (depending on if you enable "mixed mode" etc). So my real question is this:

What exactly are these new options, and how do they differ from Windows Authentication? And perhaps more specifically, what is the difference between Windows Authentication and the new Active Directory - Integrated?

I suspect they are related to Azure as I do not use Azure on my team, but was looking for a better explanation here on StatckOverflow but didn't fine one.

Answer 1

I found this article that does a great job at answering the question (but now that I went through the trouble I'll still post this here in case it helps others). It appears I was right, it is related to Azure. The UI wording is a bit confusing

Connect to Server (Database Engine) ...

Windows Authentication Microsoft Windows Authentication mode allows a user to connect through a Windows user account.

SQL Server Authentication When a user connects with a specified login name and password from a non-trusted connection, SQL Server performs the authentication itself by checking to see if a SQL Server login account has been set up and if the specified password matches the one previously recorded. If SQL Server does not have a login account set, authentication fails, and the user receives an error message. When possible, use Windows Authentication or Active Directory - Password authentication.

Active Directory - Universal with MFA support Active Directory - Universal with MFA is an interactive work flow that supports Azure Multi-Factor Authentication (MFA). Azure MFA helps safeguard access to data and applications while meeting user demand for a simple sign-in process. It delivers strong authentication with a range of easy verification options—phone call, text message, smart cards with pin, or mobile app notification—allowing users to choose the method they prefer. When the user account is configured for MFA the interactive authentication work flow requires additional user interaction through pop-up dialog boxes, smart card use, etc. When the user account is configured for MFA, the user must select Azure Universal Authentication to connect. If the user account does not require MFA, the user can still use the other two Azure Active Directory Authentication options. For more information, see SSMS support for Azure AD MFA with SQL Database and SQL Data Warehouse. If necessary, you can change the domain that authenticates the login, by clicking Options, selecting the Connection Properties tab, and then completing the AD domain name or tenant ID box.

Active Directory - Password Azure Active Directory Authentication is a mechanism of connecting to Microsoft Azure SQL Database by using identities in Azure Active Directory (Azure AD). Use this method for connecting to SQL Database if you are logged in to Windows using credentials from a domain that is not federated with Azure, or when using Azure AD authentication using Azure AD based on the initial or the client domain. For more information, see Connecting to SQL Database By Using Azure Active Directory Authentication.

Active Directory - Integrated Azure Active Directory Authentication is a mechanism of connecting to Microsoft Azure SQL Database by using identities in Azure Active Directory (Azure AD). Use this method for connecting to SQL Database if you are logged in to Windows using your Azure Active Directory credentials from a federated domain. For more information, see Connecting to SQL Database By Using Azure Active Directory Authentication.

In a nutshell, the bottom three options (starting with "Active Directory") are all relevant to Azure.