ActiveDirectoryIntegrated auth in SSMS 19 hits MSAL throttling

[shueybubbles]

Describe the bug

See https://feedback.azure.com/d365community/idea/b4b0d281-c2a0-ed11-a81b-6045bd8615b0

SSMS creates multiple connections when using Object Explorer. Customers using Active Directory Integrated auth may get blocked by a throttling exception from MSAL.

Exception message:
Failed to authenticate the user NT Authority\Anonymous Logon in Active Directory (Authentication=ActiveDirectoryIntegrated).
Error code 0xinvalid_grant
Your app has been throttled by AAD due to too many requests. To avoid this, cache your tokens see https://aka.ms/msal-net-throttling. (Microsoft SQL Server, Error: 0)
 
My info:
SQL Server Management Studio                        19.0.20196.0+2666cdc9
SQL Server Management Objects (SMO)                        16.200.48035.0+151cabf12d40a9649e575b05b1e409956d432cc9
Microsoft T-SQL Parser                        16.0.22524.0+62eedb15cd3cde34e51c8fbbdf9b06e575ec912e
Microsoft Analysis Services Client Tools                        16.0.19970.0
Microsoft Data Access Components (MDAC)                        10.0.22000.1455
Microsoft MSXML                        3.0 6.0 
Microsoft .NET Framework                        4.0.30319.42000
Operating System                        10.0.22000
 
stack trace:
   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, Boolean isFirstTransparentAttempt, Boolean disableTnir)
   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, ServerCertificateValidationCallback serverCallback, ClientCertificateRetrievalCallback clientCallback, DbConnectionPool pool, String accessToken, SqlClientOriginalNetworkAddressInfo originalNetworkAddressInfo, Boolean applyTransientFaultHandling)
   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.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open()
   at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnectImpl()
   at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect()
   at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()

To reproduce

Use SSMS 19 Object Explorer with Active Directory Integrated

Expected behavior

I'd expect this auth mode to leverage the in-memory cache with AcquireTokenSilent to avoid this exception

Additional context
Add any other context about the problem here.
Please provide a fix for the 3.1 branch.

[lcheunglci]

Thanks @shueybubbles for bringing this issue to our attention. It does make sense that we should be caching the token to avoid spamming the auth server. I feel like it's related to the enhancement in #1895 but we'll discuss with the team and take a look at it soon.

[shueybubbles]

I recommend refactoring auth as part of 6.0, at which point you'll hopefully split the entire AAD provider implementation into its own package.
SSMS 19 has a more urgent need for a patch with 3.x to unblock people.

[lcheunglci]

We'll look into it.

[MikeHNJ]

Thanks, @shueybubbles, using AcquireTokenSilent sounds like that would also address my related issue where I'm not seeing throttling, but a lot of extra authentication flows making for a sluggish UI.
https://feedback.azure.com/d365community/idea/4b79c633-81a1-ed11-a81b-6045bd8615b0

[erinstellato-ms]

Hi @lcheunglci and @David-Engel - checking in to see if there is an ETA on when this fix might be available in a MDS 3.x that we could pick up for a SSMS 19 hotfix. We have users stating that SSMS is unusable for them, and we have to recommend they revert back to an 18.x build. Thanks in advance for any information!