Azure Active Directory only authentication for Azure SQL

Published 03-16-2021 05:53 AM 2,135 Views
Microsoft

 

Some of you have seen a blog on Azure AD only authentication (hereafter “AAD-only auth”) that was accidently published. With this blog we would like to correct the previous message and announce that this feature will be in a public preview in May and will support all Azure SQL SKUs such as Azure SQL Database, Azure Synapse Analytics and Managed Instance (MI).


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.

 

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.

3 Comments
Visitor

Good to know that this has been leaked too early.

I have been trying the whole week to make this work on my sql servers using infra as code.

It is already accessible in API's, but lead to errors when used as parameter.

New Contributor

Excellent! I like this enforced featured.

Does this mean that creating external data links (between azure sql db's), will also be possible with an AAD account?

Microsoft

We will release this feature for a public preview in May

%3CLINGO-SUB%20id%3D%22lingo-sub-2211696%22%20slang%3D%22en-US%22%3EAzure%20Active%20Directory%20only%20authentication%20for%20Azure%20SQL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2211696%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESome%20of%20you%20have%20seen%20a%20blog%20on%20Azure%20AD%20only%20authentication%20(hereafter%20%E2%80%9CAAD-only%20auth%E2%80%9D)%20that%20was%20accidently%20published.%20With%20this%20blog%20we%20would%20like%20to%20correct%20the%20previous%20message%20and%20announce%20that%20this%20feature%20will%20be%20in%20a%20public%20preview%20in%20April%20and%20will%20support%20all%20Azure%20SQL%20SKUs%20such%20as%20Azure%20SQL%20Database%2C%20Azure%20Synapse%20Analytics%20and%20Managed%20Instance%20(MI).%3C%2FP%3E%0A%3CP%3E%3CBR%20%2F%3EFollowing%20the%20SQL%20on-premises%20feature%20that%20allows%20the%20disabling%20of%20SQL%20authentication%20and%20enables%20only%20Windows%20authentication%2C%20we%20developed%20a%20similar%20feature%20for%20Azure%20SQL%20that%20allows%20only%20Azure%20AD%20authentication%20and%20disables%20SQL%20authentication%20in%20the%20Azure%20SQL%20environment.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhen%20%E2%80%9CAAD-only%20auth%E2%80%9D%20is%20active%20(enabled)%2C%20it%20disables%20SQL%20authentication%2C%20including%20SQL%20server%20admin%20as%20well%20as%20SQL%20logins%20and%20users%2C%20and%20allows%20only%20Azure%20AD%20authentication%20for%20the%20Azure%20SQL%20server%20and%20MI.%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%20is%20not%20blocked.%20Neither%20the%20pre-existing%20nor%20newly%20created%20SQL%20accounts%20will%20not%20be%20allowed%20to%20%60connect%20to%20the%20server.%20In%20addition%2C%20enabling%20the%20AAD-only%20auth%20does%20not%20remove%20existing%20SQL%20login%20and%20user%20accounts%2C%20but%20it%20disallows%20these%20accounts%20to%20connect%20to%20Azure%20SQL%20server%20and%20any%20database%20created%20for%20this%20server.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2211696%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20SQL%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAzure%20SQL%20Database%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAzure%20SQL%20Security%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2217667%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-2217667%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20to%20know%20that%20this%20has%20been%20leaked%20too%20early.%3C%2FP%3E%3CP%3EI%20have%20been%20trying%20the%20whole%20week%20to%20make%20this%20work%20on%20my%20sql%20servers%20using%20infra%20as%20code.%3C%2FP%3E%3CP%3EIt%20is%20already%20accessible%20in%20API's%2C%20but%20lead%20to%20errors%20when%20used%20as%20parameter.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2226992%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-2226992%22%20slang%3D%22en-US%22%3E%3CP%3EExcellent!%20I%20like%20this%20enforced%20featured.%3C%2FP%3E%3CP%3EDoes%20this%20mean%20that%20creating%20external%20data%20links%20(between%20azure%20sql%20db's)%2C%20will%20also%20be%20possible%20with%20an%20AAD%20account%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2307981%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-2307981%22%20slang%3D%22en-US%22%3E%3CP%3EWe%20will%20release%20this%20feature%20for%20a%20public%20preview%20in%20May%3C%2FP%3E%3C%2FLINGO-BODY%3E
Co-Authors
Version history
Last update:
‎Apr 29 2021 10:45 AM
Updated by: