Azure SQL Database

Azure AD identities can be used for authentication to the Azure SQL database.

>   if you need to create a SQL Server Administrator based on an existing Azure AD Account.

you need to create a secondary SQL Server Administrator that is based on an Azure AD Account.

This is required before you can start creating users based on Azure AD Accounts in the SQL database.

Create an Azure AD administrator for Azure SQL server

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. A second SQL Server administrator must be created, that is an Azure AD account.

This principal is created as a contained database user in the master database. As administrators, the server administrator accounts are members of the db_owner role in every database, and enter each user database as the dbo user. For more information about the server administrator accounts.

Note :

1. When using Azure Active Directory with geo-replication, the Azure Active Directory administrator must be configured for both the primary and secondary servers. If a server does not have an Azure Active Directory administrator, then Azure Active Directory logins and users receive a "Cannot connect" to server error.

2. users that are not based on an Azure AD account ( including the Azure SQL server administrator account), cannot create Azure AD-based users, because they do not have permission to validate proposed database users with the Azure AD.


How to prepare the database to authenticate by using identities defined in their Azure AD tenant ?
How to create a container user based off Azure AD in a SQL database ?

when you create a contained user based on an Azure AD Account,
you have to use the clause of "EXTERNAL PROVIDER".

To create an Azure AD-based contained database user ( other than the server administrator that owns the database), connect to the database with an Azure-AD identity, as a user with atleast the ALTER ANY USER  permission.

The use the following Transact-SQL syntax:

CREATE USER   <Azure_AD_principal>    FROM EXTERNAL PROVIDER;


Reference:

https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?tabs=azure-powershell