Unable to connect to azure sql db using MFA

Question

Hello -

I am the admin of azure sql db.

I am trying to create different users/Sec Groups which have been created in Entra ID.

Sec groups are created on Entra Id.

I am trying to create them on azure sql db and assign roles to each of them.

When I try to login myslef as admin of this sql db using MFA I get this error message for some reason.

It asks me to login into the entra id and I select the account that I am registered but on another windows shows this: "Authentication complete. You can return to the application. Feel free to close this browser tab."

When I try to connect I get this error message.

Login failed for user '<token-identified principal>'. (Framework Microsoft SqlClient Data Provider)

------------------------------

For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error

------------------------------

Error Number: 18456

Severity: 14

State: 1

Line Number: 65536

Not seen this before.

Please can some one help!

Thanks

Xhev

Answer 1

Hello **Xhevahir Mehalla
**It looks like you’re running into two common issues when using MFA against an Azure SQL Database:

1. Your client tool (SSMS) doesn’t fully support the “Azure AD – Universal with MFA” flow
2. The Azure AD principal (user or group) hasn’t actually been provisioned in the database

Kindly please try to verify the below,

1. Upgrade your client to a recent SSMS
   • Install SSMS 22 (or at least SSMS 18.6+). Older SSMS versions use the “classic” auth dialog and won’t complete the Entra MFA flow correctly.
   • SSMS 22 installs side-by-side, so you won’t lose your existing setup.
2. Configure Azure AD admin on your SQL server
   • In the Azure Portal, go to your SQL server resource → Active Directory admin → set yourself (or an Entra group) as the AD admin.
   • This step ensures that MFA-based logins are allowed at the server level.
3. Connect with the correct auth method
   • In SSMS’s Connect dialog, set Authentication = “Azure Active Directory – Universal with MFA” (sometimes labeled “Microsoft Entra MFA”).
   • Complete the browser-based MFA prompt; once it says “Authentication complete,” SSMS should finish connecting.
4. Create your Entra users/groups in the database
   • Connect as your AD admin, then run for each security group:

          CREATE USER [******@contoso.com] FROM EXTERNAL PROVIDER;
     

   • Assign roles, for example:

          ALTER ROLE db_datareader ADD MEMBER [******@contoso.com];
     

5. Verify and retry
   • After that, try connecting as a member of that group. You should no longer see the 18456 error.

https://learn.microsoft.com/en-us/azure/azure-sql/database/active-directory-interactive-connect-azure-sql-db?view=azuresql
https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-overview?view=azuresql

If this doesn't help, please share your more insights for further investigation.