Azure SQL - How to add AD users to SQL Pool Databases

Problem: I am trying to add active directory users & groups (synced from on-prem to Azure AD) to SQL databases that exist in a SQL elastic pool on Azure in the same tenant. I do not want to use SQL Server Authentication (integrated). It seems that I am not able to log in with an AD account (required) to add additional AD accounts.

The on premise directory is synced to Azure AD. Tenant is configured and working with O365 email and we now have several SQL databases that exist in our tenant in an elastic pool.

Note: this is not a managed SQL instance. When I manage the elastic pool or database directly, I do not have the menu option Active Directory admin available, as outlined here.

My AD administrator account is added to elastic pool Access control (IAM) as an owner.

Using SSMS to connect to database in cloud, it is possible to connect to database using SQL Server Authentication. When I try to CREATE USER [username@mytenant.com] FROM EXTERNAL PROVIDER I receive error Principal 'username@mytenant.com' could not be created. Only connections established with Active Directory accounts can create other Active Directory users.

The reason for this, also outlined here, is "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."

Using SSMS again and connecting with Active Directory - Universal with MFA support as username@mytenant.com, on login attempt I receive error Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456). Same results when I configure Connect to database as DBName and AD domain name or tenant ID as mytenant.onmicrosoft.com. I have the same outcome even when using the initial tenant administrator account that we created when setting up environment.

So my question at the end of the day - How do I add an AD user synced from on prem to an Azure SQL database (or elastic pool) so that I can login with that user and begin adding additional AD users to the database?

I am clearly missing something fundamental and just need a point in the right direction. Thanks for any assistance provided.

First create an Active Directory Admin for the pool.

1. Select your existing Elastic Pool in the portal
2. From “Overview”, click on your “Server Name”
3. Select “Active Directory Admin” -> “Set Admin”. You can set a single user or an AD Group as the Administrator.

You can also do this from the CLI:

> az sql server ad-admin create --object-id <Object-Id-OfUserOrGroup>
    -s <Database-Name>
    -g <Resource-Group>
    -u <NameOrEmailAkaDisplayName>

Now, you should now be able to log in to a database in the Elastic Pool with your AD Admin login via SSMS. In this case you can log in via “Active Directory Password Authentication”, but you can also use “Universal Authentication” or “Integrated Authentication”.

Once you’re logged in as the AD Administrator, you can create a new SQL Server USER that corresponds to an AD Group. Please consider I’ve already created an AD group called “SQL Developers” so I can map it to an Azure SQL Server database and add it to db_datareader fixed role like this:

CREATE USER [SQL Developers] FROM EXTERNAL PROVIDER 
ALTER ROLE db_datareader ADD MEMBER [SQL Developers]