Connect to Azure SQL database using SSMS via AAD MFA when account is guest of another directory

Question

I have a problem when connecting to an Azure SQL Database using SSMS under this scenario:

• Have a work laptop connected to work domain via VPN
• The database is in a different Azure tenant (tenant B) to my work Azure tenant (tenant A).
• My work account is set up via AAD in the work Azure tenant (A) and is a guest account in other tenant (B)
• Have the latest version of SSMS
• Granted user access to the Azure SQL database
• I connect via SSMS using the Azure Active Directory - Universal with MFA option
• Note all default options are set and have also tried setting the database

I know the database access has been correctly set up as I have successfully been able to connect in the above scenario when I use Azure Data Studio instead of SSMS, but only because I have the option to change "Azure AD tenant" property from "Tenant A" to "Default Directory".

I have been able to connect OK when using SSMS with my personal computer using the same account / method, except it is not using a work account and is not connected to the work domain.

Seems like my issue is that I cannot change the "Azure AD tenant" property in SSMS and it is using the tenant associated with my account. I have also tested with a number of different people and they have the same issue. I have also set the default Azure directory to tenant B in the Azure portal.

Does anyone have any suggestion how I can get this to work in SSMS?

Azure Data Studio connection

Thanks Tony

Answer 1

OK, I am answering my own question.

I have a work-around which involves:

1. Create a connection in Azure Data Studio and setting the Azure AD Tenant to "default directory"
2. In SSMS, connect to the Database using the Azure Data Studio connection in the Registered Servers pane

Not ideal, but it works.