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.

[KylieF]

Thank you for the idea and feedback!

Our data connectors and IN-DB team always appreciate all the feedback they get on what data sources are the most helpful and how user would like to see them behave. I noticed you're new to the idea boards (welcome!) so if you haven't yet be sure to check out our Submission Guidelines as it included information on the process after an idea has been posted.

[esar]

The similar idea:  https://community.alteryx.com/t5/Alteryx-Designer-Ideas/Support-for-Azure-Active-Directory-AAD-authe...

[machag]

Currently , We are trying to complete a POC with Azure Synapse SQL pool . Same issue with the ODBC 17 driver

[renat_isch]

Hello, everyone, this idea has been implemented and released as part of 23.2 release. The MFA is now completely handled by Alteryx Designer and all credentials are "cached", stored, in DCM. 

[helmick2003usap]

How do we implement the solution?  I'm using and ODBC connection with Driver 17 configured as a DSN.  I'm still getting multiple prompts.  How do I set up the configuration within the DCM to not get repeated reauthentications?

[Maureen_Brennan]

I am also looking for this documentation.  I am on Designer 2023.2, but we are getting the multiple prompts problem when connecting to Azure.  Many thanks!

[renat_isch]

Hi Helmeek/Maureen, 

Thanks for your message. The 23.2 release addressed that behaviour. Please follow the following steps to setup a new connection and benefit from improved behaviour: 

1. Drop an input/output/indb tool, select DCM and set up a new connection;

2. Locate Azure SQL database and select "Quick connect" option;

3. Fill out data source details and create new credentials;

4. Select Azure AD or Azure AD private from the list of authentication methods (Azure AD is Alteryx's multi-tenant app created to enable authentication. If you decide to create a single-tenant authentication app in your Azure AD tenant and use it instead, please select Azure AD private).

5. Create a new set of credentials;

6. Click Link and connect

These two authentication methods no longer rely on the driver to handle authentication - the primary reason users were repeatedly prompted to authenticate. Instead, authentication is now handled directly by Designer and resulting access and refresh tokens get stored in DCM.

Please follow the above steps and let me know if you have any further questions.

[Maureen_Brennan]

Many thanks for your reply!  Yes, we were able to connect using the instructions above.  For me, personally, understanding that we had to have an Azure AD account for Alteryx was also part of the learning curve.  It is also helpful to understand that the authentication methods no longer reply on the driver.  Thank you for summarizing it, which makes it easier to share with the tech support team, who is handling a high volume of application transitions to Azure.  Many thanks!!