Connecting Alteryx to Azure SQL Datawarehouse/Database

[Gurpreet-ce]

Hi everyone,

I am trying to connect Alteryx to Azure SQL Database for doing data analysis for our project. As there is no custom connector for Azure SQL DB, I am using ODBC driver 17 for SQL Server. 

Within the ODBC connection configuration I am using the option: 

 Azure Active Directory Interactive authentication using a Login ID entered by the user

The connection is successful when the Data source is tested.

The issue that I am facing is: When I drag in any tool like In-DB connector, Summarize In-Db, Filter In-Db... I get prompted for authentication from Azure AD every time, multiple times for each tool. 

Even after the the flow is designed and I hit the Run button, I get prompted for authentication again a couple of times. 

Is it possible to connect just once and the session stays alive for rest of the time? Is there any other better way for connecting to Azure SQL DB? 

Note: When I select the option -Azure active directory password authentication using a login ID and password entered by the user, The ODBC connection is not successful from Alteryx and I get the error:
" Error: Input Data (7): Error SQLDriverConnect: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Failed to authenticate the user in Active Directory (Authentication option is 'ActiveDirectoryPassword').
Error code 0x800401F0; state 10
CoInitialize has not been called."

[BrandonB]

Have you tried the "with Azure Active Directory Integrated Authentication" instead? 

[Gurpreet-ce]

Hi, 

Thanks for your reply. 

I tried using the option- Azure Active Directory Integrated Authentication

I get the following error when I try to do that- 

Microsoft ODBC Driver for SQL Server Version 17.05.0001

Running connectivity tests...

Attempting connection
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Failed to authenticate the user "{username}'' in Active Directory (Authentication option is 'ActiveDirectoryIntegrated').
Error code 0xCAA9002C; state 10
Failed to parse XML blob.
Reason:[XML document must have a top level element.
] Line: 0, Position: 0

[BrandonB]

For the username and password, have you used the configuration as specified at: https://help.alteryx.com/current/designer/microsoft-azure-sql-database

 

[Gurpreet-ce]

Hi Brandon,

Thank you for your reply.

I appended the servername to the user ID  ( screenshots attached)

This causes the Active directory to pickup the username as - 

username@company.com@dbname.database.windows.net  and the authentication fails for this account 

The authentication works fine If I remove the servername from the userid field but the issue or repeated authentications still is causing trouble. 
Every new In-Db tool or a  connecting path to a new In-Db tool causes the authentication screen to pop up and ask for password for the username provided in the ODBC connection.

[Gurpreet-ce]

Any further update on this will be helpful. 

[BrandonB]

Hi @Gurpreet-ce 

Would you mind sending an email to support@alteryx.com? They will be able to take a closer look and help you out

[MCarterPXD]

Do we know if Alteryx support was able to solve this issue?  We are having the exact same problem.

Thanks

[cgoodman3]

I was able to connect last week to an Azure SQL database. I don’t remember the settings as I don’t have my laptop infront of me, but I did it using:

- ODBC driver 17 for SQL

- username and password as the authentication method

I’ll post screenshots tomorrow morning (UK time)

[rodalferreira]

I don't know if that's your case, but I had an ODBC connection to Azure SQL Server using SQL Server Authentication Method ID and Pwd entered by user, and I've never had this issue..

Did you try this method?

Cheers,

Rodrigo