question

Jp-0599 avatar image
0 Votes"
Jp-0599 asked ·

Unable to authenticate MFA for Conditional Access

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, Action`1 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>b1(Task`1 )
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

11460-screen-shot-2020-07-07-at-13757-pm.png


11497-screen-shot-2020-07-07-at-12614-pm.png


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


11498-screen-shot-2020-07-07-at-12631-pm.png


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


azure-active-directoryazure-sql-databaseazure-ad-multi-factor-authenticationazure-ad-authentication
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Have you tried downgrading to Azure Data Studio v1.16 ? Looks like there's a Github thread that may be related to this issue and some are commenting that downgrading helped. https://github.com/microsoft/azuredatastudio/issues/10314

0 Votes 0 ·
JaiVerma-7010 avatar image
0 Votes"
JaiVerma-7010 answered ·

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

· 3 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I am using Azure Data Studio not SSMS I am able to be challenged and authenticate using AAD Universal MFA Support but I am still being denied access. I am using the Microsoft Authenticator a push notification for approval.

0 Votes 0 ·

Also I'm not sure if it is necessary or not but for my programmatic approach I am using a connection string to connect my Active Directory Identity. I am not using managed identities

0 Votes 0 ·
neray-MSFT avatar image
0 Votes"
neray-MSFT answered ·

@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.


· 3 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I have updated the pictures in my original question. I am being challenged and am able to approve but I am still being denied access. I am using a Sql Server Database and using a Sql Database type for my database.

0 Votes 0 ·

Btw I followed everything here, but I. also tried. the client apps because it didn't work.

https://docs.microsoft.com/en-us/azure/azure-sql/database/conditional-access-configure

0 Votes 0 ·

Also by now the token should have been refreshed right? As I have tried to login multiple times with verification through the Authenticator app

0 Votes 0 ·