Connect Azure SQL Database with Azure AD AAD interactive, Multi-Factor Authentication MFA

[esar]

Connect to Azure SQL Database with Azure AD also with Multi-Factor Authentication is a crucial feature nowadays. The tool should be configurable by interface tools so we can change the database within the same Azure Database server.

There is a workaround to use ODBC for this but it does not support caching credentials and that's why problematic to use. The credential prompt is appearing every time we run the workflow. With ODBC it's also required to have a separate DSN for every database in the same server.

To make it easy for users there should be a native connector for this feature. The user experience should be easy as it's in an azure data lake connector.

[onlyshiva]

Good Afternoon. Alteryx 2024.1 does not have the Microsoft Azure SQL Database --> Quick Connect. Can we get the steps for connection in this Latest version please?

[renat_isch]

Hi Onlyshiva, that's not exactly correct. The Alteryx Desiger 2024.1 build does have Azure SQL Database Quick connect option. If you don't see that option, please reach out to our customer support and they will assist you with finding it. 

[Chris_Donnelly]

Hi, Renat_Isch

IT has recently switched our Microsoft SQL Server setup so that we connect using a DSN with an authentication method of Azure Active Directory Interactive.

Using the DSN inside of Alteryx results in entering credentials all the time.

Using the DSN inside of Microsoft Access results in only having to enter credentials one time after opening (Thus IT has thrown their hands up and said its not their fault).

Do you think your solution should work for me (given slight difference in server type and authentication method of original poster)?

I attempt to follow the steps but on step 4, I'm not sure what I should be putting into the scope and just get an authorization failed when I attempt to create and link.

Thanks,

Chris

[renat_isch]

Hi @Chris_Donnelly, the issue you're facing is caused by the fact that when using ODBC api via DSN only the driver is not able to retain the token across multiple sessions. Therefore, you're prompted to authenticate over and over again. In 23.2 release, we introduced DSN-less support for Azure AD authentication via DCM. in order to use that approach, please enable DCM and use Quick connect + DSN-less options when setting up a connection. Hope this helps.

Cheers,

Renat

[Chris_Donnelly]

Hi Renat,

Thank you for the response.

I am trying to connect using DSN-less connection type as you mention but I do not see Azure Active Directory Interactive as an authentication method. This is the authentication method I use in the DSN connection and it appears it should be an option based on this help article.

Using Alteryx 24.2 release and have DCM enabled. I have both ODBC Driver 17 for SQL Server & ODBC Driver 18 for SQL Server, but neither have Azure Active Directory Interactive as an authentication method.

Is there something I have to do to get this authentication method to show up? I have attempted to use other authentication methods but have not been able to get anything else to work.

Thanks again,

Chris