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.
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.
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.
Clear the current session if there are any frames that were captured before setting the filter
Reproduce the issue
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.
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
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.