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.
Feature details
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.
Tooling-support
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.
Limitations
- Azure AD-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 Azure AD-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 Azure AD-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 SQL 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 Azure AD-only auth feature is enabled. This is consistent to the way impersonation works today, where even disabled users can be impersonated.
New update
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.
For more information see Create server with Azure Active Directory only authentication enabled in Azure SQL - Azure SQL Database | Microsoft Docs.
Microsoft
