Connection Error using MFA

[vikarBCC]

Type: Bug

Microsoft.Data.SqlClient.SqlException (0x80131904): Failed to authenticate the user in Active Directory (Authentication=ActiveDirectoryInteractive). Error code 0xinvalid_grant AADSTS50078: Presented multi-factor authentication has expired due to policies configured by your administrator, you must refresh your multi-factor authentication to access '022907d3-0f1b-48f7-badc-1ba6abab6d66'. Trace ID: 84d108ea-0e5e-4bb4-b076-45474aae0200 Correlation ID: db68ebfb-a6ed-492d-bd3c-3845885d9882 Timestamp: 2023-09-08 05:15:01Z at Microsoft.Data.SqlClient.SqlInternalConnectionTds.GetFedAuthToken(SqlFedAuthInfo fedAuthInfo) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OnFedAuthInfo(SqlFedAuthInfo fedAuthInfo) 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) 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.<>cDisplayClass48_0.b_0(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 --- at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread) --- End of stack trace from previous location --- at Microsoft.SqlTools.ServiceLayer.Connection.ReliableConnection.ReliableSqlConnection.<>cDisplayClass30_0.<b_0>d.MoveNext() in //src/Microsoft.SqlTools.ManagedBatchParser/ReliableConnection/ReliableSqlConnection.cs:line 313 --- End of stack trace from previous location --- at Microsoft.SqlTools.ServiceLayer.Connection.ConnectionService.TryOpenConnection(ConnectionInfo connectionInfo, ConnectParams connectionParams) in /_/src/Microsoft.SqlTools.ServiceLayer/Connection/ConnectionService.cs:line 711 ClientConnectionId:1a28d02a-60b3-4aad-83c7-c0aec24ae4d5

Azure Data Studio version: azuredatastudio 1.45.1 (88c21b1725a3e79440027bdb7b5a55fb036be0e2, 2023-08-03T00:42:37.945Z) OS version: Windows_NT x64 10.0.19044 Restricted Mode: No Preview Features: Disabled Modes:

System Info

|Item|Value| |---|---| |CPUs|11th Gen Intel(R) Core(TM) i7-11370H @ 3.30GHz (8 x 3302)| |GPU Status|2d_canvas: enabled
canvas_oop_rasterization: disabled_off
direct_rendering_display_compositor: disabled_off_ok
gpu_compositing: enabled
multiple_raster_threads: enabled_on
opengl: enabled_on
rasterization: enabled
raw_draw: disabled_off_ok
video_decode: enabled
video_encode: enabled
vulkan: disabled_off
webgl: enabled
webgl2: enabled
webgpu: enabled| |Load (avg)|undefined| |Memory (System)|15.84GB (1.17GB free)| |Process Argv|| |Screen Reader|no| |VM|0%|

Extensions (23)

Extension|Author (truncated)|Version ---|---|--- sqlops-combine-scripts|Bat|2.0.1 eltsnap-simple-data-flow|bit|1.0.1 admin-pack|Mic|0.0.2 admin-tool-ext-win|Mic|0.1.3 agent|Mic|0.49.0 azcli|Mic|1.8.0 azuredatastudio-oracle|Mic|0.1.3 cms|Mic|0.9.3 dacpac|Mic|1.14.0 datavirtualization|Mic|1.12.0 import|Mic|1.5.5 managed-instance-dashboard|Mic|0.4.2 net-6-runtime|Mic|1.1.0 powershell|ms-|2022.7.2 profiler|Mic|0.12.2 query-history|Mic|0.5.3 schema-compare|Mic|1.20.0 sql-database-projects|Mic|1.2.0 sql-dw|Mic|0.0.1 sql-migration|Mic|1.4.9 extra-sql-script-as|pac|0.5.0 schema-visualization|R0t|0.8.2 simple-data-scripter|sea|0.1.6

[cheenamalhotra]

Hi @vikarBCC

As the error suggests, have you tried refreshing your account or adding it again?

[vikarBCC]

Hi @cheenamalhotra, Yes, I have tried refreshing account. Also, it works sometimes when I delete cached token files under ..\AppData\Roaming\azuredatastudio\Azure Accounts and close browser where I get MFA prompt. But even that doesn't work sometimes and I have to wait atleast for a day for the token to expire and re-authenticate again next day. We have MFA enabled in our organisation whereby we need to re-authenticate every 2 hours but token is cached somewhere and I am not prompted to enter security code in authenticator app everytime. I believe that's what causing this issue.

[philipnye]

I'm experiencing what I think is the same issue on v1.46.1 on Windows.

I agree that deleting cached tokens then refreshing accounts doesn't always work (at this stage ADS gives Connection error: User account <user name> not found in MSAL cache, please add linked account or refresh account credentials.). At that stage, attempting to delete cached tokens using the Azure Accounts: Clear Azure Accounts Token Cache command gives another, different error. But manually deleting accessTokenCache.local in C:\Users\{UserNameHere}\AppData\Roaming\azuredatastudio\Azure Accounts, manually restarting ADS, then refreshing account credentials allows me to connect without waiting 24 hours @vikarBCC

[cheenamalhotra]

I would recommend the same to clear C:\Users\{UserNameHere}\AppData\Roaming\azuredatastudio\Azure Accounts directory contents if a weird error like this occurs. The caches are synchronized by MSAL.NET and MSAL.JS, but sometimes it seems policies are not synchronized and error is not captured by MSAL.JS which should 'ideally' trigger re-authentication with error code AADSTS50078.

[vikarBCC]

Hi, Any update on the progress of this issue?

[chrisbatchler]

Any permanent solutions to this issue? We are getting the same issue when trying to connect to Azure SQL databases using Entra IDs.

Clearing cache or removing/readding accounts isn't a great user experience. If you try to connect to a DB and need to reauthenticate with MFA then shouldn't it bring up the standard MS login workflow?

[DavidClaszen]

I ran into this as well, and the usual fixes weren't working at all. I had first tried deleting all cached logins, accounts, re-adding accounts, deleting cache and cookies in Chrome, reinstalling Azure Data Studio, etc. etc. Nothing worked.

The only thing that ended up fixing it is when I pasted the reauthentication URL into a different browser:

• Try adding a new connection.
• Your browser opens to reauthenticate, for me that's Chrome.
• Ignore that, copy paste the URL you're getting for the reauthentication into Edge.
• Do the login in Edge.
• Now the connection suddenly works.

But no idea whether it's due to Chrome, my settings, or how Chrome talks to Azure Data Studio, or due to Edge. But if you run into these reauthentication issues, perhaps try different browsers or an incognito window.

But the behavior that I'm ending up with is still odd. For any other program that I use to connect to our SQL server with MFA, I need to go through actual, proper MFA, with an authenticator app. For Azure Data Studio, it hijacks the login from your browser, skips the MFA, and then you're just logged in? When I restart ADS, it even skips the browser step now. I mean, sure, it's convenient, but I can't help but think that's the source of all these problems, and practically it's like there's no real MFA at all.

So, basically, like Chris says; why not use the standard MS login workflow?