azuredatastudio: Azure Active Directory - Universal with MFA support results in connection error (Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.) (AAD)
Issue Type: Bug
- Create a new connection.
- 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 = <Default> (actually, trying to list fails silently)
- 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® Core™ 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<snip>\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 |
About this issue
- Original URL
- State: closed
- Created 5 years ago
- Reactions: 13
- Comments: 52 (8 by maintainers)
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.
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.
@sondreb I can confirm that I’ve got access to multiple tenants from my account too
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.
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 You could also configure an Azure AD group as the admin. All users within that group have Admin access.
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.
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication-configure?tabs=azure-powershell This article might be helpful.
Not sure whether this helps or not but SSMS also suffers from this “expired token” bug when using Azure AD auth
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.
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
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.
Same problem here.
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?
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.
@sondreb I also have access to multiple tenants.
@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 I found that closing everything out and opening an instance As Administrator worked. And you’re right; this only affects new connections.
Same here, also on Mac, 1.7.0.
(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… )
I just got this now in SSMS attempting to connect to an Azure SQL Database.
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.