1

I would like to use Azure SQL Database with Azure AD authentication only. I couldn't find any way to disable the sql authentication mode either the server admin login. Any hint?

Thank you, Alessandro

ing.alfano
  • 406
  • 1
  • 9
  • 18
  • Possible duplicate of [Azure SQL Server, is it possible to disable server admin (after creation)?](https://stackoverflow.com/questions/51431757/azure-sql-server-is-it-possible-to-disable-server-admin-after-creation) – David Browne - Microsoft Jan 22 '19 at 20:39
  • Avoid creation of database users instead of trying to avoid they can connect. CREATE TRIGGER CatchUserCreation ON DATABASE FOR CREATE_USER. – Alberto Morillo Jan 23 '19 at 02:29

2 Answers2

4

This is now possible as announced https://techcommunity.microsoft.com/t5/azure-sql/azure-active-directory-only-authentication-for-azure-sql/ba-p/2417673

Tick box in the portal as the screenshot from the article linked above enter image description here

Damien Pontifex
  • 1,222
  • 1
  • 15
  • 27
0

When we are creating a new Azure SQL server, we need these following values:

  1. Server name: Enter mysqlserver.
  2. Server admin login: Type azureuser.
  3. Password: Enter Azure1234567.
  4. Confirm Password: Retype the password.
  5. Location: Drop down and select any valid location.

Other words, when the Azure SQL Server is created, the SQL Server admin account is created. We can't connect to the Azure SQL Server without it. Other words, Azure SQL Server is created based on Azure SQL Server Authentication.

One of the benefits for using the Azure AD authentication is that it provides an alternative to SQL Server authentication.

Each Azure SQL server (which hosts a SQL Database or SQL Data Warehouse) starts with a single server administrator account that is the administrator of the entire Azure SQL server. If we want to use Azure AD Authentication, a second SQL Server administrator must be created, that is an Azure AD account.(Reference:Create an Azure AD administrator for Azure SQL server ).

Administrator structure:

enter image description here

We can understand like this, Azure AD authentication is a complement to SQL Server authentication.

We can remove Azure Active Directory administrator. Removing the Azure Active Directory administrator for Azure SQL server prevents any Azure AD authentication user from connecting to the server. If necessary, unusable Azure AD users can be dropped manually by a SQL Database administrator. enter image description here

And combine the blob David Browne mentioned, we can get the result: we can not disable the SQL Server Authentication.

Hope this can helps you.

Leon Yue
  • 15,693
  • 1
  • 11
  • 23