Azure Active Directory only authentication for Azure SQL

Published Jun 08 2021 07:29 AM 5,982 Views
Microsoft

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.

 

Picture1.06.04.21.png

 

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).

Picture2.png

 

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 Datab...

 

3 Comments
Occasional Visitor

can not create a login on managed instance under account  server admin ( sql login ) 

CREATE login  [xxx@www.www.www] FROM EXTERNAL PROVIDER 
GO

error : Msg 102, Level 15, State 48, Line 2 Incorrect syntax near 'PROVIDER'.

 

Do i have to enable Enabling Azure AD-only  to get this working ??

however , i can run following in the user database 

CREATE user  [xxx@www.www.www] FROM EXTERNAL PROVIDER.

so create user is fine but not login .. 

Please help

Microsoft

I will look into it. 

In the meantime could you please try to create an AAD login using an AAD admin.

- - - - -

I did check the MI executing create login [aad_user] from external provider
and it works for me for both server admin and AAD admin. In both cases I can create an AAD login. Please contact our support organization to investigate your case.

BTW do not enable AAD-only auth since it disables a database access to a server admin.

- - - - 

One more.

Just checking. Did you run your command in master or in the database?
You have to run your command in master DB.

 

Microsoft

Please read the update part for this feature at the end of this blog announcing an extended functionality 

%3CLINGO-SUB%20id%3D%22lingo-sub-2417673%22%20slang%3D%22en-US%22%3EAzure%20Active%20Directory%20only%20authentication%20for%20Azure%20SQL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2417673%22%20slang%3D%22en-US%22%3E%3CP%3EWe%E2%80%99re%20announcing%20a%20new%20feature%20called%20Azure%20Active%20Directory%20only%20authentication%20for%20Azure%20SQL%20(hereafter%20referred%20to%20as%20%E2%80%9CAzure%20AD-only%20auth%E2%80%9D).%20This%20feature%20is%20in%20public%20preview%20and%20is%20supported%20for%20Azure%20SQL%20Database%20(SQL%20DB)%20and%20Azure%20SQL%20Managed%20Instance%20(MI).%20Following%20the%20SQL%20Server%20on-premises%20feature%20that%20allows%20the%20disabling%20of%20SQL%20authentication%20and%20enabling%20only%20Windows%20authentication%2C%20Azure%20SQL%20now%20allows%20only%20Azure%20AD%20authentication%2C%20and%20disables%20SQL%20authentication%20in%20the%20Azure%20SQL%20environment.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EFeature%20details%3CBR%20%2F%3E%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EWhen%20%E2%80%9CAzure%20AD-only%20auth%E2%80%9D%20is%20active%20(enabled)%2C%20SQL%20authentication%20is%20disabled%2C%20including%20for%20SQL%20server%20admin%2C%20as%20well%20as%20SQL%20logins%20and%20users.%20The%20feature%20allows%20only%20Azure%20AD%20authentication%20for%20the%20Azure%20SQL%20server%20and%20MI.%26nbsp%3B%20SQL%20authentication%20is%20disabled%20at%20the%20server%20level%20(including%20all%20databases)%20and%20prevents%20any%20authentication%20(connection%20to%20the%20Azure%20SQL%20server%20and%20MI)%20based%20on%20any%20SQL%20credentials.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAlthough%20SQL%20authentication%20is%20disabled%2C%20the%20creation%20of%20new%20SQL%20logins%20and%20users%20are%20not%20blocked.%20However%2C%20pre-existing%20and%20newly%20created%20SQL%20accounts%20will%20not%20be%20allowed%20to%20connect%20to%20the%20server.%20In%20addition%2C%20enabling%20the%20Azure%20AD-only%20auth%20feature%20does%20not%20remove%20existing%20SQL%20login%20and%20user%20accounts%2C%20but%20it%20denies%20these%20accounts%20from%20connecting%20to%20Azure%20SQL%20server%20and%20any%20database%20created%20for%20this%20server.%3C%2FP%3E%0A%3CP%3E%3CBR%20%2F%3E%3CSTRONG%3ETooling-support%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EWe%20support%20PowerShell%2C%20CLI%20commands%2C%20Rest%20APIs%2C%20ARM%20templates%2C%20as%20well%20as%20the%20Azure%20portal%20for%20SQL%20Database%20to%20enable%20or%20disable%20the%20Azure%20AD-only%20auth%20feature.%20The%20Azure%20portal%20for%20MI%20is%20currently%20not%20supported.%20For%20more%20on%20details%20on%20this%20feature%20and%20available%20interfaces%2C%20see%20%3CA%20href%3D%22https%3A%2F%2Faka.ms%2FAAD-only-authentication%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EAAD-only-authentication%3C%2FA%3E.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EPermissions%20required%20to%20enable%2Fdisable%20Azure%20AD-only%20auth%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3ETo%20enable%20or%20disable%20the%20Azure%20AD-only%20auth%20feature%20special%20permissions%20are%20required%20available%20to%20the%20high%20privileged%20built-in%20roles%20such%20as%20%3CSTRONG%3Esubscription%20owner%2C%20contributor%2C%20or%20co-administrator%3C%2FSTRONG%3E.%20The%20required%20permissions%20can%20also%20be%20customized%20by%20creating%20custom%20roles.%20For%20more%20information%20on%20Azure%20built-in%20roles%2C%20see%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Frole-based-access-control%2Fbuilt-in-roles%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Frole-based-access-control%2Fbuilt-in-roles%3C%2FA%3E.%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20allow%20Azure%20AD%20users%20with%20lower%20privileges%20to%20enable%2Fdisable%20the%20Azure%20AD-only%20auth%20feature%2C%20the%20existing%20built-in%20role%26nbsp%3B%3CSTRONG%3ESQL%20Security%20Manager%3C%2FSTRONG%3E%26nbsp%3Bwas%20modified%20to%20allow%20these%20operations%20for%20SQL%20DB%20and%20MI.%20The%20two%20Azure%20SQL%20built-in%20roles%2C%26nbsp%3B%3CSTRONG%3ESQL%20Server%20Contributor%3C%2FSTRONG%3E%26nbsp%3B(used%20for%20SQL%20DB)%20and%26nbsp%3B%3CSTRONG%3ESQL%20Managed%20Instance%20Contributor%3C%2FSTRONG%3E%26nbsp%3B(used%20for%20MI)%2C%20do%20not%20have%20the%20permission%20to%20enable%20or%20disable%20Azure%20AD-only%20auth.%20This%20role-separation%20helps%20in%20implementing%20separation%20of%20duties%2C%20where%20users%20who%20can%20create%20an%20Azure%20SQL%20server%20or%20create%20an%20Azure%20AD%20admin%2C%20such%20as%20SQL%20Server%20contributor%20or%20SQL%20Managed%20Instance%20Contributor%2C%20cannot%20enable%20nor%20disable%20security%20features%20such%20as%20Azure%20AD-only%20auth.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EEnabling%2Fdisabling%20the%20Azure%20AD-only%20auth%20feature%20using%20the%20Azure%20portal%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EAfter%20assigning%20an%20Azure%20AD%20user%20a%20role%20discussed%20above%2C%20such%20as%20SQL%20Security%20Manager%2C%20the%20Azure%20AD-only%20auth%20feature%20can%20be%20enabled%20using%20the%20Azure%20portal%20by%20checking%20the%20feature%20box%20and%20saving%20its%20action%20(see%20below).%20The%20Azure%20AD-only%20auth%20feature%20using%20the%20Azure%20portal%20is%20currently%20supported%20only%20for%20SQL%20DB%2C%20and%20not%20for%20MI.%3CBR%20%2F%3ENote%20that%20the%20Azure%20AD%20admin%20must%20be%20set%20for%20this%20server%20to%20check%20the%20feature%20box.%3C%2FP%3E%0A%3CDIV%20id%3D%22tinyMceEditorMirek%20Sztajno_0%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Picture1.06.04.21.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F286452i516CEBD297834857%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Picture1.06.04.21.png%22%20alt%3D%22Picture1.06.04.21.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOnce%20the%20feature%20is%20enabled%2C%20any%20attempt%20to%20login%20to%20this%20server%20using%20SQL%20authentication%20fails%20with%20an%20error%20message%20indicating%20the%20cause%20of%20the%20failure%20(see%20below).%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22Picture2.png%22%20style%3D%22width%3A%20496px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F286453i42FAAAA05BA4C5C9%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Picture2.png%22%20alt%3D%22Picture2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESimilarly%2C%20the%20feature%20box%20can%20be%20unchecked%20allowing%20both%20Azure%20AD%20and%20SQL%20authentication.%20In%20this%20case%2C%20repeating%20the%20SQL%20login%20using%20the%20SSMS%20example%20above%20will%20succeed.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ELimitations%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EAzure%20AD-only%20auth%20is%20supported%20at%20the%20Azure%20SQL%20server%20level%3C%2FLI%3E%0A%3CUL%3E%0A%3CLI%3EThis%20means%20that%20when%20this%20mode%20is%20enabled%2C%20all%20databases%20that%20belong%20to%20this%20server%20can%20only%20be%20accessed%20using%20Azure%20AD%20authentication%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CLI%3EEnabling%20Azure%20AD-only%20auth%20does%20not%20remove%20existing%20SQL%20logins%20or%20SQL%20users%20based%20on%20these%20logins.%20They%20continue%20being%20stored%20in%20SQL%20metadata%2C%20but%20cannot%20be%20used%20for%20SQL%20authentication%3C%2FLI%3E%0A%3CLI%3EEven%20though%20the%20Azure%20AD-only%20auth%20is%20enabled%2C%20with%20proper%20SQL%20permissions%20for%20Azure%20AD%20users%2C%20SQL%20logins%20and%20SQL%20users%20can%20be%20created.%20However%2C%20the%20authentication%20process%20to%20connect%20to%20Azure%20SQL%20using%20SQL%20logins%2Fusers%20will%20fail%3C%2FLI%3E%0A%3CLI%3EAzure%20AD%20users%20with%20proper%20permissions%20can%20impersonate%20existing%20SQL%20users%3C%2FLI%3E%0A%3CUL%3E%0A%3CLI%3EImpersonation%20continues%20working%20between%20SQL%20authentication%20users%20even%20though%20the%20Azure%20AD-only%20auth%20feature%20is%20enabled.%26nbsp%3BThis%20is%26nbsp%3Bconsistent%20to%20the%20way%20impersonation%20works%20today%2C%20where%20even%20disabled%20users%20can%20be%20impersonated.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FUL%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2417673%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20SQL%20Database%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAzure%20SQL%20Managed%20Instance%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAzure%20SQL%20Security%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2488051%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20Active%20Directory%20only%20authentication%20for%20Azure%20SQL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2488051%22%20slang%3D%22en-US%22%3E%3CP%3Ecan%20not%20create%20a%20login%20on%20managed%20instance%20under%20account%26nbsp%3B%20server%20admin%20(%20sql%20login%20)%26nbsp%3B%3C%2FP%3E%3CP%3ECREATE%20login%26nbsp%3B%20%5Bxxx%40www.%3CA%20href%3D%22http%3A%2F%2Fwww.www%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ewww.www%3C%2FA%3E%5D%20FROM%20EXTERNAL%20PROVIDER%26nbsp%3B%3CBR%20%2F%3EGO%3C%2FP%3E%3CP%3Eerror%20%3A%26nbsp%3BMsg%20102%2C%20Level%2015%2C%20State%2048%2C%20Line%202%20Incorrect%20syntax%20near%20'PROVIDER'.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20i%20have%20to%20enable%26nbsp%3B%3CSPAN%3EEnabling%20Azure%20AD-only%26nbsp%3B%20to%20get%20this%20working%20%3F%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Ehowever%20%2C%20i%20can%20run%20following%20in%20the%20user%20database%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3ECREATE%20user%26nbsp%3B%20%5Bxxx%40www.%3CA%20href%3D%22http%3A%2F%2Fwww.www%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ewww.www%3C%2FA%3E%5D%20FROM%20EXTERNAL%20PROVIDER.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Eso%20create%20user%20is%20fine%20but%20not%20login%20..%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EPlease%20help%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2509175%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20Active%20Directory%20only%20authentication%20for%20Azure%20SQL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2509175%22%20slang%3D%22en-US%22%3E%3CP%3EPlease%20read%20the%20update%20part%20for%20this%20feature%20at%20the%20end%20of%20this%20blog%20announcing%20an%20extended%20functionality%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2509173%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20Active%20Directory%20only%20authentication%20for%20Azure%20SQL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2509173%22%20slang%3D%22en-US%22%3E%3CP%3EI%20will%20look%20into%20it.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20the%20meantime%20could%20you%20please%20try%20to%20create%20an%20AAD%20login%20using%20an%20AAD%20admin.%3C%2FP%3E%0A%3CP%3E-%20-%20-%20-%20-%3C%2FP%3E%0A%3CP%3EI%20did%20check%20the%20MI%20executing%26nbsp%3Bcreate%20login%20%5Baad_user%5D%20from%20external%20provider%3CBR%20%2F%3Eand%20it%20works%20for%20me%20for%20both%20server%20admin%20and%20AAD%20admin.%20In%20both%20cases%20I%20can%20create%20an%20AAD%20login.%20Please%20contact%20our%20support%20organization%20to%20investigate%20your%20case.%3C%2FP%3E%0A%3CP%3EBTW%20do%20not%20enable%20AAD-only%20auth%20since%20it%20disables%20a%20database%20access%20to%20a%20server%20admin.%3C%2FP%3E%0A%3CP%3E-%20-%20-%20-%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOne%20more.%3C%2FP%3E%0A%3CP%3EJust%20checking.%20Did%20you%20run%20your%20command%20in%20master%20or%20in%20the%20database%3F%3CBR%20%2F%3EYou%20have%20to%20run%20your%20command%20in%20master%20DB.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Co-Authors
Version history
Last update:
‎Jul 01 2021 04:41 PM
Updated by: