We’re announcing a new feature called Azure Active Directory only authentication for Azure SQL (hereafter referred to as “Azure AD-only auth”). This feature is in public preview and is supported for Azure SQL Database (SQL DB) and Azure SQL Managed Instance (MI). Following the SQL Server on-premises feature that allows the disabling of SQL authentication and enabling only Windows authentication, Azure SQL now allows only Azure AD authentication, and disables SQL authentication in the Azure SQL environment.
When “Azure AD-only auth” is active (enabled), SQL authentication is disabled, including for SQL server admin, as well as SQL logins and users. The feature 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 are not blocked. However, pre-existing and newly created SQL accounts will not be allowed to connect to the server. In addition, enabling the Azure AD-only auth feature does not remove existing SQL login and user accounts, but it denies these accounts from connecting 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 Azure AD-only auth feature. The Azure portal for MI is currently not supported. For more on details on this feature and available interfaces, see AAD-only-authentication.
Permissions required to enable/disable Azure AD-only auth
To enable or disable the Azure AD-only auth feature special permissions are required available to the high privileged built-in roles such as 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 enable/disable the Azure AD-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 the permission to enable or disable Azure AD-only auth. 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 Azure AD-only auth.
Enabling/disabling the Azure AD-only auth feature using the Azure portal
After assigning an Azure AD user a role discussed above, such as SQL Security Manager, the Azure AD-only auth feature can be enabled using the Azure portal by checking the feature box and saving its action (see below). The Azure AD-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.
As an extension to this feature we have also released a new functionality that is now part the public preview for the Aure AD-only auth allowing to provision an Azure SQL server with Azure AD-only enabled during a server creation.
In addition a server admin and a server password can be set by the system (set password to random) during a server provisioning.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.