Unable to authenticate MFA for Conditional Access

Question

Hi there, I am currently setting up an Azure portal using a free account which I am currently on a Premium 2 trial for Active Directory. Originally I was able to access the database using the credentials of a user called JP01. This user is part of a Database Admin group which was assigned to a sql server as an AAD Admin. This all works fine.

However, I also added a Conditional Access Policy to whoever accesses Azure Sql Database must also perform MFA and receive grant access. For the most part this all works. I try to login, it denies me, sends me to a login page which calls my authenticator app and I am able to approve it. The problem is even after I authenticate, I am still denied access.

On Azure Data Studio I receive:
Login failed for user '<token-identified principal>'.

Programmatically I receive:
System.AggregateException: One or more errors occurred. (One or more errors occurred. (One or more errors occurred. (AADSTS50076: Due to a configuration change made by your administrator, or because you moved to a new location, you must use multi-factor authentication to access '022907d3-0f1b-48f7-badc-1ba6abab6d66'.
Trace ID: ae3b2ad2-eeaf-4f2e-8783-526808d5a900
Correlation ID: b7f45074-f8b6-4813-9f0c-e578d067872c
Timestamp: 2020-07-07 16:30:39Z)))

Azure Data Studio Log:
Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user '<token-identified principal>'.
at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at Microsoft.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance) at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool, SqlAuthenticationProviderManager sqlAuthProviderManager) at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionFactory.<>c__DisplayClass40_0.<TryGetConnection>b__1(Task1 _)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)
--- End of stack trace from previous location where exception was thrown ---
at Microsoft.SqlTools.ServiceLayer.Connection.ReliableConnection.ReliableSqlConnection.<>c__DisplayClass30_0.<<OpenAsync>b__0>d.MoveNext() in D:\a\1\s\src\Microsoft.SqlTools.ManagedBatchParser\ReliableConnection\ReliableSqlConnection.cs:line 314
--- End of stack trace from previous location where exception was thrown ---
at Microsoft.SqlTools.ServiceLayer.Connection.ConnectionService.TryOpenConnection(ConnectionInfo connectionInfo, ConnectParams connectionParams) in D:\a\1\s\src\Microsoft.SqlTools.ServiceLayer\Connection\ConnectionService.cs:line 549
ClientConnectionId:95f719ed-a055-47fb-bf29-1a3576539286
Error Number:18456,State:1,Class:14

11523-screen-shot-2020-07-07-at-12624-pm.png

Answer 1

Are you using Universal authentication for SQL Azure? Did you follow all the steps as mentioned here - https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-mfa-ssms-configure

Answer 2

@Jp-0599
If the token was generated prior to multi-factor authentication being enforced, then you will encounter this particular error. Also, the use of conditional access (e.g. the feature that excludes the requirement for MFA for particular IP range) can result in this particular error. So, if you are leveraging conditional access then there is a chance you might not be challenged for multi-factor authentication and that could result in errors like this due to the enforcement.

Do let us know if this helps and if there are any more queries around this, please do let us know so that we can help you further. Also, please do not forget to accept the response as Answer; if the above response helped in answering your query.