Lessons Learned #552:10 Lines in an SSMS MSAL Trace That Tell You Almost Everything You Need to Know
In one of our support cases, a customer reported higher-than-expected connection times when connecting from SQL Server Management Studio (SSMS) to Azure SQL Database using Microsoft Entra authentication. The behavior was also not completely consistent. Sometimes the connection required interaction with the account selection experience. Other times, subsequent connections were noticeably faster. At first, there were several possible areas to investigate: Was Azure SQL taking too long to authenticate the user? Was there a networking, proxy, or firewall issue? Was access to login.microsoftonline.com being delayed or denied? Was Microsoft Entra authentication itself taking the time? Was Conditional Access or MFA involved? Was SSMS using the expected Microsoft Entra account? Was the delay happening before or after the access token was obtained? The important question became: Where exactly was the connection time being spent? Instead of treating the SSMS connection as a single operation, I enabled verbose MSAL tracing in SSMS and started following the authentication process. SSMS exposes the MSAL Output Window Trace Level under Tools → Options → Azure Services. The same options page exposes the Microsoft Entra authority, Azure SQL Database service principal name, and Web Account Manager settings. The resulting trace was large—hundreds of lines. But while analyzing it, I realized something useful: I didn't need to understand every line in the MSAL trace. A small number of search strings were enough to reconstruct almost the entire authentication story. These are the 10 things I learned to look for. 1. CorrelationId — First, make sure I am following the same authentication request One of the first things I learned was not to read the trace only by timestamp. MSAL performs several related operations: GetAccounts. AcquireTokenSilent. ReadAccountById AcquireTokenInteractive Broker operations Token cache operations Some can happen almost at the same time, and they don't necessarily share the same correlation ID. For example, in one reproduction, the silent authentication attempt had one correlation ID, while the subsequent interactive request used another one. So my first search became: CorrelationId I learned not to calculate latency by simply subtracting two nearby timestamps. First I identify: Operation + CorrelationId + Start / End and only then interpret the timing. This becomes particularly valuable if the logs later need to be correlated with identity-service investigations. MSAL exposes the correlation ID specifically to piece together an authentication flow. 2. ApiId — Was SSMS trying silent or interactive authentication? My second search became: ApiId In the first connection attempt I found: ApiId - AcquireTokenSilent That immediately told me that SSMS was not initially trying to display an authentication UI. It first attempted to obtain the token silently. But later in the same reproduction I found: ApiId - AcquireTokenInteractive Now the flow was becoming clearer: SSMS ->AcquireTokenSilent -> Silent authentication cannot continue -> AcquireTokenInteractive This was my first important lesson from the case: An SSMS Microsoft Entra connection isn't necessarily interactive from the beginning. MSAL can first try silent authentication and only switch to an interactive mechanism if necessary. Microsoft documents MsalUiRequiredException precisely for situations in which a non-interactive acquisition cannot continue without user interaction—for example because sign-in, MFA, consent, or another requirement must be satisfied. 3. Authority — Where was the authentication request actually going? The next thing I wanted to know was whether the delay could be associated with reaching Microsoft Entra. I searched for: Authority - The trace showed: Authority - https://login.microsoftonline.com/<tenant-id>/ This single line gives us two very important pieces of information: https://login.microsoftonline.com/ -> Microsoft Entra tenant -> Identity authority That means that before blaming Azure SQL, I can establish which identity endpoint SSMS/MSAL is using. This is also where a frequently reported problem such as: "It looks like access to login.microsoftonline.com is denied." can be investigated much more precisely. If I see something such as: MsalServiceException StatusCode: 403 AADSTS.... then I need to inspect the Microsoft Entra response and its error code. If I see a DNS, TLS, proxy, connection timeout, or similar exception, the network path to the identity endpoint becomes much more relevant. Those are very different problems. MSAL distinguishes client/library errors (MsalClientException), token-provider/service responses (MsalServiceException), and scenarios requiring interaction (MsalUiRequiredException). Network failures that MSAL doesn't handle are propagated to the application. One thing I would not conclude from a 403 alone is: "The firewall is blocking login.microsoftonline.com." The ErrorCode, AADSTS code, StatusCode, and ResponseBody together are much more useful than the HTTP status alone. 4. Scopes — Which resource was SSMS requesting the token for? Another line that became essential was: Scopes - Our trace showed: Scopes - https://database.windows.net//.default This helped me separate two parts of the connection that are easy to mix together: Microsoft Entra Authority login.microsoftonline.com -> issues / obtains identity token Token requested for database.windows.net -> Azure SQL The Authority tells me where authentication is being performed. The scope tells me for which resource the token is being requested. SSMS documents https://database.windows.net/ as the Azure SQL Database service principal name used when obtaining a Microsoft Entra token. That gave me another troubleshooting rule: Don't treat a Microsoft Entra token acquisition problem and an Azure SQL authorization problem as the same thing. 5. LoginHint — What identity did SSMS suggest? The next search gave us one of the most interesting findings of the investigation: LoginHint In one reproduction we had: LoginHint provided: True and later: LoginHint - user@contoso.com In another reproduction, a different login hint was supplied, and MSAL eventually returned: MsalUiRequiredException ErrorCode: no_account_for_login_hint with the explanation that no account in the token cache matched that login hint. At first sight, it is tempting to interpret: no_account_for_login_hint as: Incorrect username But the other traces showed me that this interpretation is too simplistic. LoginHint is better understood as: "Try to locate or preselect this identity" It doesn't necessarily mean: "This is already the resolved MSAL account" . That distinction turned out to be very important. 6. Account provided and GetAllAccounts — LoginHint and Account are not the same thing This became perhaps my favorite finding from the investigation. I started searching for: GetAllAccounts and: Account provided In one trace we had: GetAllAccounts ... found 1 accounts but: LoginHint provided: True Account provided: false MSAL nevertheless ended with: no_account_for_login_hint This originally looked contradictory. There was an account in the cache, but MSAL said there was no account for the login hint. It isn't contradictory. What I learned was: An account exists ≠ An account matching this LoginHint exists Then I captured another scenario: LoginHint provided: False Account provided: Account username: user@contoso.com Now SSMS/MSAL wasn't trying to resolve a textual login hint. It already had a concrete account. I started thinking of the two cases like this: LoginHint -> "Try this identity" Account provided -> "Use this resolved identity" This distinction can be especially useful when investigating aliases, UPNs, multiple Windows accounts, cached identities, or cross-tenant accounts. The same error can also have another explanation Another reproduction made the lesson even clearer. This time the trace started with: GetAllAccounts ... found 0 accounts Found 0 RTs and 0 accounts SSMS attempted: LoginHint provided: True Account provided: false and again received: ErrorCode: no_account_for_login_hint But this time the UPN itself was valid. The cache simply contained no suitable account. So another important lesson was: Never diagnose no_account_for_login_hint from the error text alone. Check GetAllAccounts, LoginHint, and Account provided together. 7. ErrorCode, AADSTS, StatusCode — The error tells me where to investigate next At this point I realized that one of the fastest troubleshooting searches was simply: ErrorCode together with: AADSTS StatusCode ResponseBody MsalUiRequiredException MsalServiceException MsalClientException For example: MsalUiRequiredException ErrorCode: no_account_for_login_hint immediately tells me that I am still dealing with token acquisition/account resolution. I haven't reached the point where Azure SQL database permissions would explain this particular failure. This became my mental decision table: Evidence in the trace Where I would investigate first no_account_for_login_hint LoginHint / cached account / account resolution MsalUiRequiredException Why silent authentication requires interaction authentication_canceled Interactive UI / broker / user cancellation AADSTSxxxxx Microsoft Entra authentication or policy MsalServiceException Token provider/service response MsalClientException Client/library/device side DNS/TLS/proxy exception Connectivity to identity service Access token obtained successfully Move the investigation beyond token acquisition 8. Broker, WAM, authorization_type — Who was really authenticating me? This was another part that changed how I read SSMS authentication traces. I searched for: Broker WAM RuntimeBroker auth_flow authorization_type and found: Broker is configured followed by: Using Windows account picker and: Calling SignInInteractivelyAsync The telemetry then reported: auth_flow: Broker authorization_type: Interactive So the authentication path was more accurately represented as: SSMS -> MSAL -> Windows Web Account Manager -> Account / authentication broker -> Microsoft Entra WAM is a Windows component that MSAL can use as an authentication broker. It can integrate with accounts already known to Windows and provide SSO and account-selection capabilities. But another trace showed: authorization_type: WindowsIntegratedAuth during a silent acquisition. That taught me something else: The same SSMS connection target does not necessarily follow the same identity path every time. Account state, cache state, WAM, tenant, identity configuration, MFA, Conditional Access, and whether interaction is required can all affect the path. 9. AccessToken returned — This is the line that changes the investigation Eventually, I found the line that I consider one of the most useful in the entire trace: AccessToken returned: True In our successful authentication we also had: AccessToken Type: Bearer and later: === Token Acquisition finished successfully source: Broker This became a very useful troubleshooting boundary for me: Identity troubleshooting: Account LoginHint MSAL WAM MFA / CA Authority Token acquisition -> AccessToken returned: True -> Azure SQL Token validation Principal resolution Database authentication Authorization Permissions If the token has been successfully obtained, asking: "Why can't MSAL authenticate?" is probably no longer the most useful question. Now I want to know: "What happens when this token is presented to Azure SQL?" That doesn't prove that the complete SQL connection will succeed, but it gives us a very useful point at which to change troubleshooting direction. 10. DurationTotalInMs — Finally, I could see where the connection time was going This was the original reason for the support investigation. The customer reported high connection times. So eventually the most important searches became: DurationTotalInMs DurationInHttpInMs DurationInCacheInMs request_duration time_in_queue_ms Microsoft defines DurationTotalInMs as the total time spent by MSAL acquiring a token, including network and cache operations. DurationInHttpInMs represents time spent in HTTP calls made by MSAL to the identity provider, and DurationInCacheInMs measures cache activity. And now we finally had evidence from our reproduction. The first token acquisition reported: DurationTotalInMs: 2185 DurationInCacheInMs: 0 DurationInHttpInMs: 0 But the broker telemetry contained another very interesting metric: time_in_queue_ms: 2152 and request_duration: 2183 That changed the quality of the diagnosis completely. Instead of saying: "SSMS authentication seems to take around two seconds." I could say: The captured token acquisition took approximately 2.18 seconds, and approximately 2.15 seconds were represented as broker queue time in the MSAL/WAM telemetry. That is actionable evidence. Putting the 10 searches together By the end of the investigation, this became the sequence I would use when somebody sends me an SSMS MSAL trace: Search for What I want to know CorrelationId Am I following the same authentication operation? ApiId Silent or interactive token acquisition? Authority Which Microsoft Entra endpoint and tenant? Scopes Which resource is the token intended for? LoginHint Which identity did SSMS suggest? Account provided / GetAllAccounts Is an identity already resolved or cached? ErrorCode / AADSTS Why did the authentication step fail? Broker / WAM / authorization_type Which authentication mechanism actually handled the request? AccessToken returned / source: Was a usable token obtained, and from where? DurationTotalInMs and related metrics Where was the authentication time spent? With those ten searches, a trace containing hundreds of lines becomes much easier to read. Appendix – Sanitized MSAL Trace Sample The following extract is a sanitized and condensed version of the MSAL trace used in this investigation. User names, tenant IDs, correlation IDs, account identifiers, and other PII have been replaced with placeholders. The goal is to provide a practical sample where you can apply the 10 searches described in this article. First connection ==== GetAccounts started ==== GetAllAccounts ... found 0 accounts Found 0 RTs and 0 accounts in MSAL cache === AcquireTokenSilent Parameters === LoginHint provided: True Account provided: false Authority - https://login.microsoftonline.com/<tenant-id>/ Scopes - https://database.windows.net//.default ApiId - AcquireTokenSilent CorrelationId - <correlation-id-1> === Token Acquisition (SilentRequest) started === MsalUiRequiredException ErrorCode: no_account_for_login_hint No account was found in the token cache having this login hint. MSAL then moved to interactive authentication: === InteractiveParameters Data === LoginHint provided: True Prompt: select_account Authority - https://login.microsoftonline.com/<tenant-id>/ Scopes - https://database.windows.net//.default ApiId - AcquireTokenInteractive LoginHint - user@contoso.com CorrelationId - <correlation-id-2> Broker is configured Using Windows account picker Calling SignInInteractivelyAsync The broker successfully obtained the token: auth_flow: Broker authorization_type: Interactive time_in_queue_ms: 2152 request_duration: 2183 WAM response status success Successfully retrieved token AccessToken returned: True AccessToken Type: Bearer === Token Acquisition finished successfully === source: Broker DurationTotalInMs: 2185 DurationInCacheInMs: 0 DurationInHttpInMs: 0 Second connection A few seconds later, the account was already available: GetAllAccounts ... found 1 accounts Returning 1 accounts === AcquireTokenSilent Parameters === LoginHint provided: False Account provided: Account username: user@contoso.com Authority - https://login.microsoftonline.com/<tenant-id>/ Scopes - https://database.windows.net//.default ApiId - AcquireTokenSilent CorrelationId - <correlation-id-3> This time the broker completed the request silently: Acquiring token silently authorization_type: WindowsIntegratedAuth auth_flow: AT request_duration: 2 WAM response status success Successfully retrieved token AccessToken returned: True === Token Acquisition finished successfully === source: Broker DurationTotalInMs: 13 DurationInCacheInMs: 0 DurationInHttpInMs: 0 What changed? First connection No account → LoginHint → AcquireTokenSilent → no_account_for_login_hint → Interactive / WAM → Token → 2185 ms Second connection Resolved Account → AcquireTokenSilent → WAM → Token → 13 ms These extracts contain the main strings I now look for when reviewing an SSMS MSAL trace: CorrelationId ApiId Authority Scopes LoginHint Account provided / GetAllAccounts ErrorCode / AADSTS Broker / WAM AccessToken returned DurationTotalInMs
Trapped in an Authenticator Loop
Dear Community, please help! I am trapped in an Authenticator Loop. I've got a microsoft workplace account, but if I want to log in, I have to type in a code from the microsoft authenticator app. I downloaded the app, but in order to use it, I have to log into my account and in order to do so, I also have to type in a code from the authenticator app, which I don't get, because to get the code I would have to log into the authenticator app, what I would need a code for... No matter which link I click, I can't open anything before I enter the code, which I can't get. I am using teams on mac, either on a firefox browser or on the desktop app and the authenticator on an Iphone. Please don't just tell me "don't use teams on a mac", this wasn't my choice. Unfortunately, my emplyer's IT support also is chronically unavailable. So is here anyone who could help me? I've already gone through the usual deleting the app, using another browser etc. options. Best Lukas
Need to Restore PST Files to Office 365 Mailboxes - What's the Best Approach
Hey everyone, I have a task coming up where I need to restore several PST files back into Office 365 mailboxes. Haven't done this before at this scale and honestly not sure where to begin. I've looked at Microsoft's native import service through Purview but I have a few concerns: Some of the PST files are quite large — not sure how well it handles that I need to restore only specific folders for some users, not the entire PST I'm worried about data consistency after the restore Would prefer something that doesn't require too many admin roles or complex setup For those who have done PST to Office 365 restores — what approach worked best for you? Any tools, tips, or things to watch out for that you wish you knew before starting?
Microsoft Teams and Authenticator lockdown
I’m having a very frustrating issue with Microsoft Teams. I am able to log in to teams using my normal personal email and account ONLY ON THE WEB BROWSER. From the web browser, I am not able to access the business channel that I am in. When attempting to log in on the downloaded mobile Teams App, I am sent directly to Microsoft Authenticator, which after waiting several hours, waiting even a full day, is still displaying a message about repeated verification attempt and to wait and try again later. I have accessed Microsoft support chat and they had no help for me. I need the authenticator lockdown to refresh and it will not.Authenticator não funciona
Tenho um e-mail corporativo (sou o adm único, nao tem ti) que faço login na conta do powerbi, contudo, meu celular com o authenticator foi perdido e já nao tenho mais acesso, por este motivo, nao consigo mais logar na conta pq ele sempre direciona para o authenticador que nao tenho mais acesso e não abre a possibilidade de receber o codigo por outro meio, (sms ou email). ja tentei vários recursos (chat, telefone do suporte) para recuperar a conta e nao consigo. Todas as opções direcionam para o autenticador. solicito a microsoft que dê uma solução, resetando o autenticador anterior para que eu possa acessar a conta e incluir métodos alternativos de desbloqueio, ou sugira outra solução.Unexpected button behaviour when using the prompt=create parameter in Entra External ID user flows
Hi, In a recent workload, I'm assisting a client to implement Entra External ID for streamlined authorization as well as single sign-on for associated registered external facing third-party applications for external customer users through their Entra External ID identity, as well as assisting the client with auth branding and other UI customizations, and preparing Entra ID federation custom OIDC providers and configuration for enabling SSO with organizational internal and remote work- and school accounts etc. The sign-up / create account experience is of importance to the client, as a rather substantial amount of users are expected to sign-up via self-service sign-up following having received an invite via another app. To ensure that the user lands on the create account view, in order to minimize the number of steps and actions the users need to take to get there, the prompt=create parameter is used to pre-select the sign-up/register experience in the user flows UI. Having stress-tested the user flows and experience recently, we noticed that in a specific scenario, some UI elements behave in a manner that could be described as unexpected, and even though a workaround has mitigated it to some extent, the behaviour of some elements could probably be improved a bit to ensure an even more consistent user experience in the built-in user flows. Specifically, if the user flow is invoked with the prompt parameter set, the user lands on the create account screen as expected, however, unless custom CSS modification is applied, the Back button that would typically be there, as if having arrived there from the initial sign-in screen where it’s also displayed. If pressing the Back button displayed on the Create account view when having navigated there with the prompt=create set in the /authorize request, pressing the button seemingly doesn’t have any impact or result in any action, one can click it, but nothing happens. I'd suspect it's perhaps a "remnant" from if the flow is invoked without any prompt parameter set, or with prompt=login set, but when prompt=create is set, there is no state/page history in the UI to navigate back to, as no previous page has been displayed or rendered yet and added to the history, and the Back button click thus doesn’t have any effect. In general, I think buttons that don't have any tangible action should not be displayed, also, if prompt=login is set, the Back button isn't shown then either on the very initial user flow view shown then, so it would seem the built-in user flows actually already follow such approach in fact, but not when the prompt=create has been set, thus causing some inconsistency in the UI that users could notice unless custom CSS styling is applied. The expected behaviour for our business case would be that if prompt=create is set, then, similar to the prompt=login, no Back button should be displayed, or, if shown, it should result in some navigation (perhaps something like javascript.go(-1), but that could/would be dependent on from where the user arrived, e.g., going back a step in the browser history won't work if the user clicked a link from an invite email ) and preferably not be non-actionable. Furthermore, on the topic of the Back button and its behaviour when the prompt=create parameter is set, there is another case at which we observed some unexpected button behaviour as well, that could probably benefit from some attention. At a specific second scenario, it seems that the Continue button is displayed without providing any action, and when clicking the Back button then, clicking it seemingly resets the "create account", state likely set by the prompt=create initially, and instead switches the flow back to the sign-in state, which can at least affect the display text of some subsequent buttons shown in later steps in the UI. The user arrives at a built-in Entra External ID user flow /authorize endpoint, with the prompt=create query parameter set The user triggers the OTP challenge by entering an email address to verify the email address The user receives the OTP code but enters it incorrectly, i.e., doesn't copy the full code length of eight digits, and instead enters/pastes six of the eight code digits. The UI then shows an error message that the code could not be used/validated, and the email address field is displayed again with the email address that was used for the verification attempt prefilled. If the user clicks the Continue button at this stage, nothing happens. If the user clicks the Back button instead (given that it’s not hidden), it shows the same view one more time, with the Back and Continue buttons at the bottom. However, if the user clicks the Continue button this time, it works and a new code is sent. On the next screen, where the newly sent code can be entered, instead of a button named "Continue", it will now instead show a button with the text "Sign-in" (it would seem like the create account state has gotten lost somewhere along the way at this point, perhaps when the back button in the step 5 above was pressed). If one omits the prompt=create parameter, or when using prompt=login, things seem to work fine, the above occurs when the prompt=create is set. The reason why is the prompt=create is used is due to a business requirement to try to minimize the steps, especially in conjunction with the registration/sign-up, as far a possible when signing up. We have opted for the built-in user flows, not the self-hosted native authentication UI pages, for this project, and then as I understand it, it is the prompt=create parameter that can/should be used to enable the user to land directly on the registration page without having to navigate there manually via the sign-up link, that is otherwise shown on the initial sign-in page. It would thus be great if the prompt=create parameter could have some attention (or perhaps if a dedicated sign-up user flow type could be added potentially, even though that would perhaps warrant some update to the user flow app linking as well, as my understanding is that one user flow can be linked to one app registration at a time currently) to avoid that some buttons become unresponsive when the parameter is used, or falls back to sign-in, to improve the built-in user flows further, as the prompt=create fulfils the business requirement well otherwise. If there would be any further/follow-up questions on the above, e.g., to clarify the requirement, or further explain the reproduction steps and behaviour observed, or anything else, please tell, and I'll ensure to get back as soon as possible. Also, would someone have some input on potential other/additional ways to pre-select the create account/sign-up experience, that would naturally be much appreciated as well, thanks! Regards Kristoffer
Not able to do an account recovory when 2FA is enabled and Passkeey is half set up
I am not able to log into my personal account. I have tried account recovery, but I have 2FA set up so I go a message saying this is ignored. I have rest the password using 2FA but when I try to login the message is that I can not use password to login. I beleive my partern loged me out of the account and tried to log in to her account but instead started Passkey set up. I have needed to login with my company account even to raise this post. Any help would be great.
