Azuredatastudio: Azure Active Directory - Universal with MFA support results in connection error (Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.) (AAD)

[SvenAelterman]

Issue Type: Bug

1. Create a new connection.
2. Enter the connection information:
   a. Type = Microsoft SQL Server
   b. Server = FQDN to Azure SQL DB or Azure SQL DB failover group
   c. Authentication type = Azure Active Directory - University with MFA support
   d. Account = an AAD account
   e. Database = (actually, trying to list fails silently)
3. Click Connect.

Error message shows:

System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. at System.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) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.<>c__DisplayClass40_0.<TryGetConnection>b__1(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__DisplayClass28_0.<<OpenAsync>b__0>d.MoveNext() in D:\a\1\s\src\Microsoft.SqlTools.ServiceLayer\Connection\ReliableConnection\ReliableSqlConnection.cs:line 303 --- 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 521 ClientConnectionId:d30ba148-2926-4c6e-973e-f5adb537d525 Error Number:18456,State:1,Class:14

Note: this happens with an account that's already listed or even after just adding a new account.

Azure Data Studio version: azuredatastudio 1.4.5 (887f4e8985023602e599cf86bdb44df48bb28569, 2019-02-12T06:20:08.633Z)
OS version: Windows_NT x64 10.0.17763

System Info

|Item|Value|
|---|---|
|CPUs|Intel(R) Core(TM) i7-8650U CPU @ 1.90GHz (8 x 2112)|
|GPU Status|2d_canvas: unavailable_software
checker_imaging: disabled_off
flash_3d: unavailable_software
flash_stage3d: unavailable_software
flash_stage3d_baseline: unavailable_software
gpu_compositing: unavailable_software
multiple_raster_threads: enabled_on
native_gpu_memory_buffers: disabled_software
rasterization: unavailable_software
video_decode: unavailable_software
video_encode: unavailable_software
webgl: enabled_readback
webgl2: unavailable_off|
|Memory (System)|15.88GB (6.29GB free)|
|Process Argv|C:\Users\\AppData\Local\Programs\Azure Data Studio\azuredatastudio.exe|
|Screen Reader|no|
|VM|67%|

Extensions (2)

Extension|Author (truncated)|Version
---|---|---
import|Mic|0.5.0
sql-vnext|Mic|0.10.2

[BroMarduk]

I saw this same issue when using the System installer and not running as 'Administrator' and attempting to connect to Azure SQL with an Azure AAD account. Running if installed using the User installer or running as Admin when using the System installer did not result in the error.

[iamwyza]

Same, what I found was that this would show up in the instance that got launched following an update as the installer would re-launch it as admin even when normally you'd launch it as non-admin. Likely the installer needs to be updated to re-launch it as the current user rather than the admin user used during installation.

[m60freeman]

I just got this now in SSMS attempting to connect to an Azure SQL Database.

[RobHudson72]

We are getting this in our environment as well. It is impacting 3 developers, one of whom runs Mac, and so SSMS isn't a great option for him. We are happy to try a beta build if available.

Version: 1.5.2 (user setup)
Commit: f74080c96310354bc92211f1826c0fdce78ca2f6
Date: 2019-03-22T06:25:26.627Z
VS Code 1.30.1
Electron: 2.0.12
Chrome: 61.0.3163.100
Node.js: 8.9.3
V8: 6.1.534.41
OS: Windows_NT x64 10.0.17134

[SvenAelterman]

Release 1.7.0 still has the same problem. I am sure I got this to work on a build a while back, but since 1.6.0 (fresh install on a new machine) nothing seems to be able to make this work.

[rossij]

(on Mac 1.7.0) after initial launch and binding to the Azure account it would not work (including the silent fail mentioned above to list the databases).

I tried different variations to no avail during that install. (MFA is mandatory for me)

But when I restarted the wizard from the Master page (which opens the identical wizard) it worked.

I suspect: Since I am on Mac I didn't relaunch (as admin really is not there) I think the creds are somehow not in the system initially (maybe the initial bind launch is different that if manually run?), but the rerun of the wizard works.

(after it worked once then it worked in any variation which is what leads me to believe that the password bind being available programatically is the issue... )

[MohsineC]

The exact same issue here on Windows.

Connecting to a SQL DW works fine on SSMS and Data Studio
Connecting to a SQL DB works fine on SSMS but not in Data Studio... not as a normal user and not as the server admin.

Version: 1.7.0 (user setup)
Commit: e1280022d69b651cfff04b30e830904575c8acda
Date: 2019-05-08T00:55:40.928Z
VS Code 1.33.1
Electron: 3.1.8
Chrome: 66.0.3359.181
Node.js: 10.2.0
V8: 6.6.346.32
OS: Windows_NT x64 10.0.17134

System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
at System.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)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.<>c__DisplayClass40_0.b__1(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 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__DisplayClass28_0.<b__0>d.MoveNext() in D:\a1\ssrc\Microsoft.SqlTools.ManagedBatchParser\ReliableConnection\ReliableSqlConnection.cs:line 303
--- End of stack trace from previous location where exception was thrown ---
at Microsoft.SqlTools.ServiceLayer.Connection.ConnectionService.TryOpenConnection(ConnectionInfo connectionInfo, ConnectParams connectionParams) in D:\a1\ssrc\Microsoft.SqlTools.ServiceLayer\Connection\ConnectionService.cs:line 521
ClientConnectionId:*******
Error Number:18456,State:1,Class:14

[ms1111]

(on Mac 1.7.0) after initial launch and binding to the Azure account it would not work (including the silent fail mentioned above to list the databases).

But when I restarted the wizard from the Master page (which opens the identical wizard) it worked.

Same here, also on Mac, 1.7.0.

[mmulhearn]

Seeing this in 1.8.0 user install as well. I can connect via SSMS just fine.

[MatthewSteeples]

Same as @mmulhearn - Seeing this on 1.8.0 on Windows, but only on one device. An upgraded version of ADS on another device that already had the connections saved has no issues connecting to the same services using AAD and MFA, so it looks like it's only affection creating new connections

[mmulhearn]

@MatthewSteeples I found that closing everything out and opening an instance As Administrator worked. And you're right; this only affects new connections.

[DanniJuhl]

Using the 1.9.0 (User Setup) Azure Data Studio I still get Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' errors, when using Universal with MFA Support.

However, if I use the exact same authentication method in SSMS 18.2, there's no issues. It prompts me for the AAD login as it should (guest user in the destination tenant), and then I connect succesfully.

I can't remember if this matches a release or not, but I started seeing this issue after enabling MFA on my account.

[addp009]

I am also having the same problem. Using User Setup of Azure Data Studio 1.9.0. As a guest user in AAD authenticated with MFA, I get failed login 'NT AUTHORITY\ANONYMOUS LOGON' errors. However, SSMS 18.2 works fine.

[kimhanse]

Bug is still present in Azure Data Studio 1.10.0 :frowning_face:

System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
   at System.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)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.<>c__DisplayClass40_0.<TryGetConnection>b__1(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__DisplayClass28_0.<<OpenAsync>b__0>d.MoveNext() in D:\a\1\s\src\Microsoft.SqlTools.ManagedBatchParser\ReliableConnection\ReliableSqlConnection.cs:line 303
--- 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 521
ClientConnectionId:a5f8c5b3-e417-42ea-8c3a-6b04ee482654
Error Number:18456,State:1,Class:14

[iamwyza]

@kimhanse - I had the same issue. I went in and deleted all the cached settings, values, etc from my AppData folder for Data Studio. After doing that and re-installing, it started accepting MFA tokens properly. (not ideal, I know, but it's better than nothing)

[kimhanse]

@iamwyza - Thanks for the suggestion, but it didn't help me.

I am running Debian Linux, and deleted all the settings doing:

rm -r ~/.config/azuredatastudio/

[sondreb]

I think the reason for this error is due to access to multiple tenants on the account you login with. Using an account that only have access to a single tenant works fine, but if user has access to multiple tenants, the anonymous dialog appear.

[SvenAelterman]

@sondreb I think you’re on to something.

[kimhanse]

@sondreb Yes, I have multiple tenants on my account. Good catch!

It is not really something I can change now, is there a way to work around this issue, perhaps by marking the tenant I want to use as default somewhere?

[MatthewSteeples]

@sondreb I can confirm that I've got access to multiple tenants from my account too

[mmulhearn]

@sondreb I also have access to multiple tenants.

[dwadezimmerman]

Any ideas or anyone come up with a work-around?

[testep02]

We are also having this same issue at my company. I am a DevOps engineer who uses Azure Data Studio on a Mac. My software engineers are using it on a Windows machine. We are all getting the exact same error. We all also have access to multiple tenants.

Is there a possible resolution to this? This is a major issue for us at this time.

[mikewo]

I am also having this issue. I can confirm that I have multiple tenants for my account. This makes adding new connections completely impossible. SSMS is not displaying the same problem. @kburtram Any update on this?

[moekhalil]

Same problem here.

[cole-gravity]

I'm glad I'm not the only one perplexed by this issue!
I cannot connect using the "Universal with MFA Support" option in any tools I use (typically DataGrip on Mac or Devart dbForge Studio on Windows), whether Windows or Mac. But if I use "Active Directory - Password" it works just fine. Unfortunately, Azure Data Studio doesn't offer that.

Like others, I also have multiple tenants.

[shand-obs]

Same problem. I have multiple tenants. This issue was opened on February 16 and 1.0 of the software was released September of 2018. I hate to be "that guy" but it's really hard to take this software seriously when it's called "Azure Data Studio" and doesn't work with Azure Active Directory properly over a year after release.

[blackmind]

Same Problem here.

[iricigor]

Same here, version 13.1, multiple tenants.

[naga-a30004408]

I was getting error while adding account with AD MFA in Data Studio. Error Description was not great like " [Object Object]". Also this error description appears only when i try to add Azure account in data studio instead of new connection to begin. When i try to add new connection and select MFA, redirection works but data studio unable to get account details, no accounts in drop down.

Solution (Temp): try to clear your browser cookies, and APP data folders (especially Azure Account, Tokens etc), and restart. this did the trick for me as data studio struggling to acquire already cached tokens . I have done MFA multiple times on same day for other applications. looks like data studio unable to get existing tokens, when try to get new tokens, browser is behind funny to authenticate as its already done earlier.

[aaomidi]

Some of the issues here are being tracked in #7619. Please let me know if the workaround in that issue fixes your problem.

[loopedimports]

I had this exact issue.

The workaround for me was before hitting "Connect"
to use the "Options" button to manually select the database you want to configure.

This allowed me to configure / access the database I needed

obviously this means you can only explore one database per server configuration using this method (which does not impact me personally)

Hopefully this is useful for other people too - at least while the issue is outstanding.

[marc-gmail-towersap]

I had the same issues, and I too am multi-tenant. Turns out I had to switch my Azure AD tenant from the wrong one (it picked) to the correct one, and poof, I'm in. This is failing me using SSMS (probably picking the wrong tenant there). I gotta learn this new Azure Data Studio, but at least I can connect to my azure sql server as an AD user (oddly, it worked every time using a sql server user login, both in ADS and in SSMS)

[osmant]

For those who still encounters the issue, I used to see Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' issue when I need to create a new connection from Connection Dialog window from scratch to a database from another AAD in which my work and school account was invited as Guest account.

I have troubleshooted the issue and committed the fix in https://github.com/microsoft/azuredatastudio/pull/8726

It seems like fix was included in the Insider Build and I no longer have problem in the insider build.

Can you please try the insider version and provide your feedback if the same fix resolves your issue as well?

Regards

[thomasvdb]

@osmant Just tried the Insiders build and this indeed fixes the issue. Awesome! Thanks!

[thomasvdb]

@osmant There is still one issue. If you leave Azure Data Studio open for about an hour without inactivity and then try to run a new query I get the 'NT AUTHORITY\ANONYMOUS' error again.

I then have to reconnect to the database to solve the issue.
A minor issue in my opinion as it's easy to workaround it.

[osmant]

Hi @thomasvdb,

Thanks for the feedback, I assume the accessToken generated to access must have expired after an hour. There must be an issue while refreshing the token after it is expired in 1 hr. I'll see if I can fix that as well.

Regards

[osmant]

Hi,

I was able to reproduce the issue. I realized that this time error is actually returned from query execution result and appeared in the Message pane. This indicates to me that underlying service that actually executes query against the database (SQLToolService) is returning the error from query result. I have not troubleshooted that service. I am just guessing SQLToolsService must be caching the auth token in its cache. Any time new query is sent from same query window, it is using the expired accessToken to execute the query.

I could not figure out where the fix needs to go. Since this gets a bit more complex than I originally thought, I would leave it to microsoft folks to get it completely fixed. Anyway, It is their radar for March release.

[MatthewSteeples]

Not sure whether this helps or not but SSMS also suffers from this "expired token" bug when using Azure AD auth

[DTronD]

After update to v1.15 I could no longer connect to existing Azure DB (Universal MFA) connections. I would get the (new?) Web browser authentication and its resulting "You're now connected and can close this window." But when I returned to Azure Data Studio, the errors regarding the token would be displayed.

This was the same when I tried to create a new connection.

Taking the advice of @iamwyza I deleted the token file (i.e. C:\Users\JoeBlow\AppData\Roaming\azuredatastudio\Azure Accounts\azureTokenCache-azurePublicCloud ) and tried connecting to the existing connection again. It worked.

[ataidecarlos]

Getting this issue when trying to connect to Azure SQL, user has two subscriptions. Seems very similar to https://github.com/microsoft/azuredatastudio/issues/7619, which is already closed.

[MatthewSteeples]

I have a problem similar to @DTronD with 1.15. I am a member of 3 directories, only 1 of which has any SQL databases in it. When I authorise I get the browser prompt and then the dropdown only shows me the 2 empty directories and I get a little error pop up about the subscription I actually want. Are there any logs produced that I can submit?

[aaomidi]

Please try the workaround https://github.com/microsoft/azuredatastudio/issues/9350#issuecomment-595510237

[terakota]

I just got this now in SSMS attempting to connect to an Azure SQL Database.

After your user is created on Azure AD, you have to accept invitation from azure site. It will be send to your e-mail. Else you will not be able connect to sql server nomater what rights you have on sql server or databases and you will get that Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
If you removed that e-mail as a spam or smth, Azure admin could resend it. On Azure Active Directory > All users > Source should be "External Azure Active Directory" or "Azure Active Directory" if its "Invited user" that mean user didn't exepted invitation.

[AdrienLH]

I am having the same problem here when trying to access my Azure Managed Instance with AAD account. My account has access to multiple tenant and subscription and the version that I am using is SSMS v18.4.

SQL login works fine

[aaomidi]

You have to add your account as the "Active Directory Admin" for the SQL Server on Azure to be able to login to your SQL Server with your Azure credentials.

Once you have that, this should be solved with the latest insiders build.

[trbenning]

@aaomidi You can only set one user as the admin, and that shouldn't be a requirement for accessing it with tools like Azure Data Studio.

[aaomidi]

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication-configure?tabs=azure-powershell This article might be helpful.

[aaomidi]

Also potentially

CREATE LOGIN [email protected] FROM EXTERNAL PROVIDER

[thomasvdb]

@trbenning You could also configure an Azure AD group as the admin. All users within that group have Admin access.

[trbenning]

Good suggestion about assigning a group as the admin.

What ultimately got this sorted out for our non-admin users is that it seemed to just be a case of needing to clear their cached credentials. Specifying the db name in the connection dialog was needed for users without a server login.

[DTronD]

Well at least I have .BAT to clear the Azure Token cache every few days when the error/warning pops up...because this issue (_ADS v.1.17.1(system setup_)) IS still occurring even though I am a member of the server admin group on the Azure SQL Database. Though it seems the name of the token has changed at least three time since I originally was made aware of the workaround (and that's what it is, a workaround) to delete the token cache. E.g. in the last week token name has gone from azure-AzurePublicCloud (back?) to azureTokenCache_azure_publicCloud.

And there now (according to #9350 ) is a command palette command...though that had a bit of a stumbled roll-out so confirm it's actually deleting the token file if you use that instead of a direct DEL command from CMD.

Open the command palette and run the Azure Accounts: Clear Azure Account Token Cache command

Version: 1.17.1 (system setup)
Commit: 814ce88c41e0daaca89afed8b184e795057a6f9d
Date: 2020-04-30T00:16:35.943Z
VS Code: 1.42.0
Electron: 7.1.11
Chrome: 78.0.3904.130
Node.js: 12.8.1
V8: 7.8.279.23-electron.0
OS: Windows_NT x64 10.0.18363