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.<>c__DisplayClass48_0.b__0(Task1 _) at System.Threading.Tasks.ContinuationResultTaskFromResultTask2.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.<>c__DisplayClass30_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 (88c21b1, 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?

[JF-SR]

We have the same problem but it appears to be totally random. 6 people with fresh installations, all try to make a connection to a SQL database in Synapse for the first time following the same instructions to connect via Microsoft Entra. 3 successful, 3 failed. All get confirmation in browser that authentication is successful, but the receive message in ADS:
Microsoft.Data.SqlClient.SqlException (0x80131904): Failed to authenticate the user in Active Directory (Authentication=ActiveDirectoryInteractive).
Error code 0xinvalid_grant
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

But ADS never triggers the full multi-factor authentication process.

Any reply from Microsoft on this issue?

[aaron-fuentes-satec]

Same here, we have several users from the same network and configurations and only some are experiencing this issue.

I see the same issue here #17356 and here microsoft/vscode-mssql#17234.

We get a redirect to localhost:xxxx, then login.microsoftonline.com where the user logs in and it keeps on loading where it does not progress from there.