• 2 min read

GA for Azure AD authentication in SQL Database and SQL Data Warehouse

You can use Azure Active Directory Authentication to centrally manage user and group identities to authenticate with SQL Database and SQL Data Warehouse.

Starting immediately, Azure Active Directory (Azure AD) authentication is generally available in Azure SQL Database and Azure SQL Data Warehouse. Azure AD provides an alternative to SQL Authentication enabling centralized identity and group management. It enables a single sign-on experience using SQL Database and SQL Data Warehouse for federated domains. Azure AD can be used to authenticate against a growing number of Azure and other Microsoft services and helps customers prevent the proliferation of users and passwords. Other advantages include:

  • Greatly simplified permission management allowing customers to control database permissions via Azure AD groups without having to access any of the underlying databases.

  • Support for:

    • Azure AD managed and federated domains with user name/password. Password rotation is centralized and triggered automatically from Azure AD.

    • Integrated Windows Authentication for Azure AD federated domains and clients on domain-joined machines. This enables single sign-on across participating services. Integrated Windows authentication is also supported for remote connections using VPNs.

    • JSON Web Token (JWT) which allows you to perform Azure AD authentication for middle-tier applications against SQL Database (e.g., service accounts).

To use Azure AD Authentication, customers must configure an Azure AD administrator who can provision SQL contained users that are mapped to Azure AD identities. Creating an Azure AD administrator can be done via PowerShell, REST API, or the Azure portal.

The screenshot below shows an Azure portal AD administrator DBA representing an Azure AD group with rachelb@contososales.onmicrosoft.com  as its member having full server administrative access.

The next screen below shows an Azure AD SQL administrator (rachelb@contososales.onmicrosoft.com) connected to a SQL Database called ContosoSales. The sample T-SQL code in the query window on the right provisions a contained SQL user named SalesReps mapped to an Azure AD group also called SalesReps. As a result, all members of the AD group SalesReps (e.g., user joep@contososales.onmicrosoft.com shown in the connection window) will be able to connect to ContosoSales using their AD credentials (user name and password). Notice the new authentication option in SSMS called “Active Directory Password Authentication”.

Next steps: