When provisioning users from external Azure Active Directory instances that are federated with your Azure subscription, you need to use the underlying "guest" email address created for the Azure subscription, not the "actual" email address.
i.e. The Microsoft scenario mentioned here as "Imported members from other Azure AD’s who are native or federated domain members".
So, instead of:
CREATE USER [email@example.com] FROM EXTERNAL PROVIDER
One needs to use the following convention. This is Microsoft's way of storing a guest / federated user from another Azure active Directory.
CREATE USER [your.user_example.com#EXT#@<yourAzureSubscriptionPrefix>.onmicrosoft.com] FROM EXTERNAL PROVIDER
Alternatively, using groups makes this far more intuitive and manageable.
- Create a group (say
SqlUsersFromExternalDirectory) in the Azure subscription's default Azure Active Directory.
- Add the external users you want to access the SQL Warehouse or DB to to the group.
- Add the group as an external user in the target database
CREATE USER [SqlUsersFromExternalDirectory] FROM EXTERNAL PROVIDER
This works fine, the external users can then sign in, admins can GRANT permissions etc, etc.