This article is contributed. See the original author and article here.
We announce a new feature called Azure Active Directory only authentication for Azure SQL (hereafter “AAD-only auth”). This feature is in public preview and is supported for all Azure SQL SKUs such as Azure SQL Database, Azure Synapse Analytics, Managed Instance (MI) and Hyperscale.
Following the SQL on-premises feature that allows the disabling of SQL authentication and enables only Windows authentication, we developed a similar feature for Azure SQL that allows only Azure AD authentication and disables SQL authentication in the Azure SQL environment.
Behavior in detail
When “AAD-only auth” is active (enabled), it disables SQL authentication, including SQL server admin as well as SQL logins and users, and allows only Azure AD authentication for the Azure SQL server and MI. SQL authentication is disabled at the server level (including all databases) and prevents any authentication (connection to the Azure SQL server and MI) based on any SQL credentials.
Although SQL authentication is disabled, the creation of new SQL logins and users is not blocked. Neither the pre-existing nor newly created SQL accounts will not be allowed to connect to the server. In addition, enabling the AAD-only auth does not remove existing SQL login and user accounts, but it disallows these accounts to connect to Azure SQL server and any database created for this server.
We support PowerShell, CLI commands, Rest APIs, ARM templates, as well as the Azure portal for SQL Database to enable or disable the AAD-only auth feature. The Azure portal for MI is currently not supported. For more on details on this feature and available interfaces, see the AAD-only-authentication.
Permissions required to enable/disable this feature
To enable or disable the AAD-only auth feature, one of the following built-in roles are required for the AAD users executing these operations: subscription owner, contributor or co-administrator. The required permissions can also be customized by creating custom roles. For more information on Azure built-in roles, see https://docs.microsoft.com/en-us/azure/role-based-access-control/built-in-roles. To allow Azure AD users with lower privileges to set/unset the AAD-only auth feature, the existing built-in role SQL Security Manager was modified to allow these operations for SQL DB and MI. The two Azure SQL built-in roles, SQL Server Contributor (used for SQL DB) and SQL Managed Instance Contributor (used for MI), do not have that permission. This role-separation helps in implementing Separation of Duties, where users who can create an Azure SQL server or create an Azure AD admin, such as SQL Server contributor or SQL Managed Instance Contributor, cannot enable nor disable security features such as AAD-only auth.
Enabling/disabling the AAD-only auth feature using the Azure portal
After assigning an Azure AD user a role discussed above such as SQL Security Manager, the AAD-only auth feature can be enabled using the Azure portal by checking the feature box and saving its action (see below). The AAD-only auth feature using the Azure Portal is currently supported only for SQL DB, and not for MI.
Note that the Azure AD admin must be set for this server to check the feature box.
Once the feature is enabled, any attempt to login to this server using SQL authentication fails with an error message indicating the cause of the failure (see below).
Similarly, the feature box can be unchecked allowing both Azure AD and SQL authentication. In this case, repeating the SQL login using the SSMS example above will succeed.
- AAD-only auth is supported at the Azure SQL server level
- This means that when this mode is enabled, all databases that belong to this server can only be accessed using Azure AD authentication
- Enabling AAD-only auth does not remove existing SQL logins or SQL users based on these logins. They continue being stored in SQL metadata, but cannot be used for SQL authentication
- Even though the AAD-only auth is enabled, with proper SQL permissions for Azure AD users, SQL logins and SQL users can be created. However, the authentication process to connect to Azure SQL using such logins/users will fail
- Azure AD users with proper permissions can impersonate existing SQL users
- Impersonation continues working between SQL authentication users even though the AAD-only auth feature is enabled. This is consistent to the way impersonation works today where even disabled users can be impersonated
Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.