CODEWITHSUNDEEP
azureazure-active-directoryazure-sql-databasewindows-authenticationazure-authentication
Alex1088
Alex1088

Reputation: 147

Trying to get AAD and Azure SQL Authentication Working

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.

Upvotes: 3

Views: 4640

Answers (2)

Rodrigo S. Teixeira
Rodrigo S. Teixeira

Reputation: 1

Recheck if the VM is on-premise or on-cloud because if your machine is on-prem and it is a joined AD, you should see it as a device in Azure Active Directory. Otherwise, when the machine is not integrated with ADFS, you will end up with the exception message "Integrated Windows authentication supported only in federation flow".

There are two possibles solutions: - Integrate the machine into ADFS - Use Active Directory Password with a valid account on Azure Active Directory.

In my scenario, I couldn't move the machine so I use an AD account.

Upvotes: 0

Shiraz Bhaiji
Shiraz Bhaiji

Reputation: 65391

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.

Upvotes: 1

Related Questions