After wasting 4 hours of my day trying to do this, below are the steps that worked for me:

• as per the documentation, set your AD account as the Active Directory admin (follow the steps mentioned in the documentation here: https://learn.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication-configure under: Provision an Azure Active Directory administrator for your Azure SQL Database server).
• Install the latest version of SSMS on your machine (the 18 RC1 in my case). If you have an existing version installed, uninstall it and "try" to clean any left over registry keys, list here: Failed to parse XML blob ).
• Connect to your server using [Active Directory Integrated]. If you get the following error message : [Failed to parse XML blob], repeat step 2, or just install the latest version of SSMS on a different VM/Machine on your network (needs to be part of the same domain).
• Once connected, execute the following SQL (from the official documentation) CREATE USER [[email protected]] FROM EXTERNAL PROVIDER;

I cannot believe I wasted almost two working days trying to do something as simple as adding a user to db. This is beyond belief. (/rantover)

For me there was a trick where you do some steps in SSMS using Active Directory - Integrated and some steps using local SQL Authentication. Here's what worked for me:

I set the domain account to use for the "Active Directory admin" setting in the Azure Sql Server features screen. Then I was able to connect using SSMS running under this account.

Note: To simplify running SSMS as this other user I used runas: C:\Windows\System32\runas.exe /savecred /user:[email protected] "C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Ssms.exe"

Running as this user, I connected using the SSMS authentication option, "Active Directory - Integrated". From here I ran the following in the master db:

CREATE USER [[email protected]] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = dbo

Then I connected to same server in SSMS using local SQL Authentication, logging in with the account set as the "Server admin" for the Azure Sql Server instance. From here I ran alter role commands in master db:

ALTER ROLE dbmanager ADD MEMBER [[email protected]]
ALTER ROLE loginmanager ADD MEMBER [[email protected]]

Now I could go back to the to SSMS running as the AD Admin user and from there I could run CREATE USER commands as above but for other domain accounts:

CREATE USER [[email protected]] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = dbo

You can decide which database to run the above (e.g., master and your non-system databases).

The domain users can now log in using "Active Directory - Integrated". Note if you add a domain user that is configured for MFA, then for that user to log on using SSMS they should select the SSMS authentication option, "Azure Active Directory - Universal with MFA", and their username should be with an "@" not backslash.

For those of you using pyodbc instead of SSMS

1. go to Azure Portal,

2. find server,

3. 'Active Directory Admin',

4. Set Admin to [email protected],

5. Login to the db you want to grant access to:

   from pyodbc import connect,drivers
   
   conn = connect(driver=drivers()[0],
                  server='someserver.database.windows.net', 
                  database='somedb',
                  Authentication='ActiveDirectoryPassword',
                  user='[email protected]',
                  PWD='somepassword')
   cursor = conn.cursor()
   

6. Add a user to sql db

   cursor.execute("CREATE USER [[email protected]] FROM EXTERNAL PROVIDER;")
   conn.commit()
   

7. Grant read access:

   cursor.execute("EXEC sp_addrolemember 'db_datareader', '[email protected]';")
   conn.commit()
   

Overview

I had the same issue and resolved it using Azure CLI and sqlcmd. I could not make the sql management studio part work as it kept complaining about my device not being approved, however, the same worked with the command line tool!

Creating Azure AD Admin using Azure CLI

# Get objectId of the user you want to be admin
objectid=$(az ad user list --filter "userPrincipalName eq '[email protected]'" --query [0].objectId -o tsv)

# Setting user as admin
az sql server ad-admin create --display-name [email protected] --object-id $(objectid) --resource-group yourresourcegroup --server sqlservername --subscription "Subscription name or id"

Adding an AD-User to Azure SQL

I used the SQL command line tool on Ubuntu, which can be installed using the documentation.

# Installing SQL command line on Ubuntu 20.04
sudo curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
sudo curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install msodbcsql17
sudo ACCEPT_EULA=Y apt-get install mssql-tools

# Logging in to Azure SQL (-G means that you use Azure AD)
/opt/mssql-tools/bin/sqlcmd -U [email protected] -P yourpassword -S sqlservername.database.windows.net -d master -G

# Creating a user (If using SSMS, you may experience problems)
1>CREATE USER [[email protected]] FROM EXTERNAL PROVIDER;
2>GO

You can change the default Entra ID (Active Directory) user that has db_owner access to the db via Azure:

1. Navigate to the Azure SQL Server (not the database itself)
2. Select the Microsoft Entra Admin user hyperlink on the Overview screen, or from the left hand menu, select Microsoft Entra ID under Settings
3. Click the Set admin option at the top
4. Add your user and apply changes
5. Access your SQL Server through SSMS
6. Grant access to other accounts as per normal

In order to add System Assigned the permission to access SQL you can use this SQL script (call it in powershell from the pipeline).

You need to provide the System Assigned Name & Application Id from the Azure Enterprise Application part (not the App registration: !)

This code avoid the need to have elevated permission on the Tenant, we are providing the app id & calcule the sid.

CREATE PROCEDURE [dbo].[GrantUserAssignedPermissionToDB]
  @Username  NVARCHAR(50),          -- "api-xxx-dev-ew1" (Enterprise Application/Name)
  @ApplicationId  UNIQUEIDENTIFIER -- 1234567-f680-44a5-a100-89101112cb (Enterprise Application/Application Id)
AS

DECLARE @sql NVARCHAR(MAX);
DECLARE @name NVARCHAR(50);

-- Search for the same user with a different appId, if one is found, delete the user by name
SELECT @name = name
FROM sys.database_principals
WHERE type = 'E' and name = @Username and CAST(CAST(sid as varbinary(16)) as uniqueidentifier) != @ApplicationId

IF @name IS NOT NULL
BEGIN
  -- An account with the same name but a different appId was found, we need to drop it (previous uai Idenity)
  SELECT 'DROP USER', @name

  -- Drop user (done systematically in case of OID change) AKS
  SET @sql = N'DROP USER IF EXISTS ' + QUOTENAME(@Username) + ';';
  EXEC sp_executesql @sql;
END

-- We check if the user doesn't already exist to avoid recreating it
SET @name = NULL;

SELECT @name = name
FROM sys.database_principals
WHERE type = 'E' and name = @Username and CAST(CAST(sid as varbinary(16)) as uniqueidentifier) = @ApplicationId

IF @name IS NULL
BEGIN

    -- Create user from Azure, add necessary permissions
    DECLARE @hexApplicationId NVARCHAR(34);
    SET @hexApplicationId = '0x' + CONVERT(NVARCHAR(32), CONVERT(VARBINARY(16), CONVERT(UNIQUEIDENTIFIER, @ApplicationId)), 2);
    SELECT 'ADD USER:', @Username, 'WITH SID:', @hexApplicationId

    -- Create user from Azure, add necessary permissions
    SET @sql = N'CREATE USER ' + QUOTENAME(@Username) + ' with sid = ' + @hexApplicationId + ', type = E;';
    --SET @sql = N'CREATE USER ' + QUOTENAME(@Username) + 'FROM EXTERNAL PROVIDER;';  -- SPN Need : Directory.read.all
    EXEC sp_executesql @sql;

    -- Add user to the db_datareader and db_datawriter roles
    EXEC sp_addrolemember 'db_datareader', @Username;
    EXEC sp_addrolemember 'db_datawriter', @Username;

END

SELECT 'After' as State, name, type, type_desc, CAST(CAST(sid as varbinary(16)) as uniqueidentifier) as appId, create_date , modify_date
from sys.database_principals
WHERE type = 'E'

1. set your intra ID account as admin as shown in previous answers.

2. Go to database and select query editor > login using account (not SQL authentication)

3. New query

4. Run the following query:

   CREATE USER [] FROM EXTERNAL PROVIDER; ALTER ROLE db_datareader ADD MEMBER []; ALTER ROLE db_datawriter ADD MEMBER [];

Note: replace with your actual one, keep the Square brackets !

Allow me to offer the solution. Don't use the portal as this cannot be done in the portal. Instead use SSMS. Connect to your database and then navigate to System Databases in the tree, right-click on Master and choose "New Query". Then, in this window you can run CREATE LOGIN and CREATE USER DDL operations, just don't include the USE MASTER line - since you are already in Master.