Blog Post

Azure SQL Blog
2 MIN READ

Azure AD pass-through and password hash authentication support for SQL DB, DW and Managed Instance

MirekSztajno's avatar
MirekSztajno
Icon for Microsoft rankMicrosoft
Apr 02, 2020

We are announcing support for Azure AD pass-through and password hash authentication for Azure SQL DB (single database and database pools), Managed Instance, and Azure Synapse (formerly SQL DW).

  • Azure AD password hash authentication is the simplest way to enable authentication for on-premises Active Directory users in Azure AD. Users are synchronized with Azure AD and password validation occurs in the cloud using the same username and password that is used in on-premises environments. No additional infrastructure is required.
  • Azure AD pass-through authentication provides a password validation mechanism that validate users directly with on-premises Active Directory, outside the cloud. Pass-through authentication does not require ADFS or other third-party federation services.
  • Each of these authentication methods can be configured by Azure AD Connect, allowing you to provision users in the cloud.

Azure AD pass-through and password hash authentication are both part of the AAD hybrid authentication solution. See the article, Choose the right authentication method for your Azure Active Directory hybrid identity solution. Both authentication methods support seamless single sign-on authentication, allowing users to authenticate with Azure SQL using integrated authentication with Windows credentials.

For information on setting up and synchronizing Azure AD hybrid identities, see the following articles:

Before connecting with Azure SQL, a proper Azure AD setup for Azure AD pass-through and password hash authentication must be executed, according to the above documentation. This will allow users to be synchronized with Azure AD and have access to the Azure portal. These initial steps are independent from the Azure SQL setup, and as prerequisites, must be executed prior to connecting with Azure SQL if you intend to use pass-through or password hash authentication.

Once the setup and password synchronization in Azure AD is completed, the status for each authentication method can be checked in the Azure portal using Azure AD connect.

The snapshot below shows the Azure AD connect status for pass-through authentication with seamless single sign-on enabled.

 

Once the setup is completed successfully for Azure AD, you can create users in Azure SQL that map to Azure AD principals. See the Azure AD administrator and Azure AD user creation section in Configure and manage Azure Active Directory authentication with SQL.

All three main authentication mechanisms: user-password, integrated, and interactive authentication (known also as universal with MFA), are supported for pass-though and password hash authentication using SQL tools. The same applies to authentication keywords used in the client applications. See SSMS and client application sections in Configure and manage Azure Active Directory authentication with SQL.  For integrated authentication, seamless single sign-on for pass-through and password hash must be enabled.

The snapshot below shows pass-through authentication with managed instance using SSMS integrated authentication executed for a user logged into an Azure VM with Windows domain enabled. The users are synchronized with Azure AD and setup for seamless single sign-on.

 

   For more information see Configure and manage Azure Active Directory authentication with SQL..

 

Updated Nov 09, 2020
Version 4.0
No CommentsBe the first to comment