Azure AD Cross-Tenant login not working

Question

Hello Everyone, I have a developer Azure account with a SQL Server database that I would like to share access to users in my office with the new "Cross-Tenant access settings" feature in
Azure AD. I added the organization using the domain, and I set all the Inbound Access and Outbound Access settings to "All allowed' temporarily to test this. I also created a group with
access to the SQL Database in which I can add individual guests users and I'm able to login to the SQL Database. But when I tried to login with "Azure Active Directory - Integrated" in SSMS
it fails with error 18456, My question is how to give access to the entire organization without having to invite all users individually?

Thanks all,

Luis

Answer 1

Hi @Luis Thank you for Posting your query on Microsoft Q&A.
From my understanding you are not able to connect to Azure SQL DB from SSMS with AAD integrated and looks like you have added the necessary users to the Azure AD Group for access to the SQL DB. Please let me know if my understanding is not correct.

The error Microsoft SQL Server, Error: 18456 indicates that the user trying to login to SQL Server Management Studio is invalid. It is usually related to an AAD user which is not added on SQL DB that you are trying to connect (User DB or Master DB) or that the AAD user is not the AAD Server Admin.

Please check that you have added AAD user in Azure SQL DB. You can follow the steps mentioned here

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 at least the ALTER ANY USER permission. Then use the following Transact-SQL syntax:

CREATE USER <Azure_AD_principal_name> FROM EXTERNAL PROVIDER;
CREATE USER [bob@Company portal .com] FROM EXTERNAL PROVIDER;
CREATE USER [alice@fabrikam.onmicrosoft.com] FROM EXTERNAL PROVIDER;

Azure_AD_principal_name can be the user principal name of an Azure AD user or the display name for an Azure AD group.

It won't be possible to invite the whole organization unless you put all your organization's users into that one group.

Regards,
Oury