Troubleshooting Azure Active Directory Integrated Authentication in Azure SQL

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.

 

 

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

 

Additionally, to understand if your account will use a federation or managed flow you can go to the following AAD URL to query the user realm -> https://login.microsoftonline.com/GetUserRealm.srf?Login=User@Example.com (Make sure you replace User@Example.com with your actual user's principal name)

 

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

 

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)

 

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.

 

 

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

 

 

 

 

 

 

 

 

$keys = "SOFTWARE\Microsoft\MSADALSQL", "SOFTWARE\WOW6432Node\Microsoft\MSADALSQL"
foreach ($key in $keys) {
    if (Test-Path -Path "HKLM:\$key") {
        $file = Get-ItemProperty -Path "HKLM:\$key" | Select-Object -ExpandProperty TargetDir
        if (Test-Path -Path $file) {
            Get-Item -Path $file | Select-Object -Property Name -ExpandProperty VersionInfo
        }
        else { 
            Write-Warning -Message "File $file doesn't exist" 
        }
    }
    else {
        Write-Warning -Message "Registry key HKLM:\$key doesn't exist"
    }
}

 

 

 

 

 

 

 

 

 

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

 

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

 

 

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!