Trying to get AAD and Azure SQL Authentication Working

Question

I'm trying get integrated authentication working between my app, and azure SQL. The app is running on a VM that is joined to an Azure AD domain (Domain Services) on IIS.

I have followed this official MS document on setting up auth: https://learn.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication-configure (Note - we are not using managed instances for SQL).

1) The admin group has been added via the portal 2) The contained database user (also part of the admin group) has been created, per the doc. 3) The IIS application pool is running as the same user as well.

Attempts to connect to the site return this error:

[AdalException: Integrated Windows authentication supported only in federation flow.]
   ADALNativeWrapper.ADALGetAccessToken(String username, IntPtr password, String stsURL, String servicePrincipalName, ValueType correlationId, String clientId, Boolean* fWindowsIntegrated, Int64& fileTime) +829
   System.Data.SqlClient.<>c__DisplayClass2_0.<AcquireTokenAsync>b__0() +132
   System.Threading.Tasks.Task`1.InnerInvoke() +121
   System.Threading.Tasks.Task.Execute() +47

[AggregateException: One or more errors occurred.]
   System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions) +4323177
   System.Threading.Tasks.Task`1.GetResultCore(Boolean waitCompletionNotification) +12865803
   System.Threading.Tasks.Task`1.get_Result() +33
   System.Data.SqlClient.<>c__DisplayClass134_1.<GetFedAuthToken>b__0() +39
   System.Threading.Tasks.Task`1.InnerInvoke() +121
   System.Threading.Tasks.Task.Execute() +47

[AggregateException: One or more errors occurred.]

Our web.config is using this as a connection string:

name="LocalSqlServer" connectionString="Server=tcp:XXXXX;Initial Catalog=XXXXX;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication='Active Directory Integrated';" />

We're hoping to be able to remove any mention of plain-text passwords within our web.config, and azure authentication should be able to provide that.

Any help would be appreciated!

EDIT: In an attempt to start fresh, I migrated the application to another fresh Azure VM. This time, the process initially lead to this error:

Unable to load adalsql.dll (Authentication=ActiveDirectoryPassword). Error code: 0x2.

After installing the .dll, it then leads me to the same error I posted above. Not sure if this initial error could shed some light on the underlying problem.

Answer 1

The error message is "Integrated Windows authentication supported only in federation flow"

From the portal, if you select "Azure Active Directory" and then select "Custom Domain names"

Do you have a single line on the list with "Primary" Selected?

If that is the case, you could try adding a new custom domain, mark that as federated and then use a user from that domain for the integrated authentication.