Troubleshooting Azure Active Directory Integrated Authentication in Azure SQL

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

Integrated authentication provides a secure and easy way to connect to Azure SQL Database and SQL Managed Instance. It leverages hybrid identities that coexist both on traditional Active Directory on-premises and in Azure Active Directory.

 

At the time of writing Azure SQL supports Azure Active Directory Integrated authentication with SQL Server Management Studio (SSMS) either by using credentials from a federated domain or via a managed domain that is configured for seamless single sign-on for pass-through and password hash authentication. More information here Configure Azure Active Directory authentication - Azure SQL Database & SQL Managed Instance & Azure Synapse Analytics | Microsoft Docs

 

We recently worked on an interesting case where our customer was getting the error “Integrated Windows authentication supported only in federation flow” when trying to use AAD Integrated authentication with SSMS.

SSMS-error-integrated-flow.png

 

Recently they have migrated from using ADFS (Active Directory Federation Services) to SSSO for PTA (Seamless Single Sign-on for Pass-through Authentication). To troubleshoot the issue, we performed the following checks.

 

Validating setup for SSSO for PTA

 

  1. Ensure you are using the latest version of Azure AD Connect
  2. Validate the Azure AD Connect status with the Azure portal https://aad.portal.azure.com
  3. Verify the below features are enabled
    • Sync Status
    • Seamless single sign-on
    • Pass-through authentication

AAD-Connect-status.png

 

Testing Seamless single sign on works correctly using a web browser

 

Follow the steps here and navigate to https://myapps.microsoft.com Be sure to either clear the browser cache or use a new private browser session with any of the supported browsers in private mode.

If you successfully signed in without providing the password, you have tested that SSSO with PTA is working correctly.

 

Now the question is. Why the sign in is failing with SSMS?

For that we turned to grab a capture using Fiddler

 

Collecting a Fiddler trace

 

The following link has a set of instructions on how to go about setting up Fiddler classic to collect a trace. Troubleshooting problems related to Azure AD authentication with Azure SQL DB and DW - Microsoft Tech Community

 

  1. Once Fiddler is ready, I recommend that you pre-filter the capture by process as to only capture traffic that is originating from SSMS. That would prevent capturing traffic that is unrelated to our troubleshooting.
  2. Clear the current session if there are any frames that were captured before setting the filter
  3. Reproduce the issue
  4. Stop the capture and save the file

When we reviewed the trace, we saw a few interesting things

Fiddler-frames.png

We can only see a call to login.windows.net which is one of the endpoints that helps us use Azure Active Directory authentication.

 

For SSSO for PTA we would expect to see subsequent calls to https://autologon.microsoftazuread-sso.com which were not present in the trace.

 

This Azure AD URL should be present in the Intranet zone settings, and it is rolled out by a group policy object in the on premises Active Directory.

 

A key part on the investigation was finding that the client version is 1.0.x.x as captured on the Request Headers. This indicates the client is using the legacy Active Directory Authentication Library (ADAL)

Fiddler-client-details.png

 

Why is SSMS using a legacy component?

 

The SSMS version on the developer machine was the latest one so we needed to understand how the application is loading this library. For that we turned to Process Monitor (thanks Mark Russinovich)

 

We found that SSMS queries a key in the registry to find what DLL to use to support the Azure Active Directory Integrated authentication.

 

Procmon.png

Using the below PowerShell cmdlets, we were able to find the location of the library on the filesystem

 

Set-Location -Path HKLM: Get-ItemProperty -Path SOFTWARE\WOW6432Node\Microsoft\MSADALSQL | Select-Object -Property TargetDir

 

adalsql.png

 

Checking on the adalsql.dll details we confirmed this is the legacy library

adalsql-props.png

 

As SSMS is a 32 bit application it loads the DLL from the SysWOW64 location. If your application is 64 bit you may opt to check the registry key HKLM:\SOFTWARE\Microsoft\MSADALSQL 

 

A clean install of the most recent version of SSMS creates a different DLL with the most up to date library

adal.png

adal-props.png

 

In this case the developer machine ended up having up that registry location modified and pointing to the legacy client (adalsql.dll). As the newer DLL (adal.dll) was already installed on the system the end user simply made the change to use the adal.dll on the registry.

 

It is important to be aware of this situation. Installing older versions of software like SSMS, SSDT (SQL Server Data Tools), Visual Studio etc. may end up modifying the registry key and pointing to the legacy ADAL client.

 

Cheers!

 

 

One Reply to “Troubleshooting Azure Active Directory Integrated Authentication in Azure SQL”

  1. I got ADFS, and got everything working. It works with MFA, but if I try using the azure ad integrated login I get the error:
    ADAL received an empty response from the server during a WIA flow and could not continue. (System.Data)

    Tried using fiddler, but didn’t get anything helpful out of the trace, only to results with status 200.
    Any idea how to debug this?

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.