Welcome to the Alteryx Knowledge Base
Created/Edited -
Please follow the steps outlined in this article to use an Azure AD or Azure AD--private to connect to Azure SQL Database.
NOTE: The Azure AD and Azure AD--private authentication methods are provided and handled by Alteryx. They are ONLY available in Data Connection Manager (DCM), and are NOT part of the native authentication methods in the ODBC driver for SQL Server.
- Alteryx Designer/Server
- Version >= 2024.1
- Microsoft Azure SQL Database
- ODBC Driver 17 for SQL Server/ODBC Driver 18 for SQL Server
IMPORTANT: Sections A and B require the administrator for both services to perform the steps.
A. Create an Entra (formerly known as Azure AD) Application [ONLY FOR Azure AD--private]
Skip this section if you plan to use Azure AD and NOT Azure AD--private.
- Go to Azure Portal.
- Go to Microsoft Entra ID > App Registrations.
- Create a new application and set the account type accordingly.
- Enter the following in the Redirect URI and click the Register button.
| Platform | URL |
| Web | http://localhost |
- Note down the client ID and tenant ID.
- Go to Manage > Certificates & secrets and create a new client secret.
- Note down the secret value immediately once created.
NOTE: This Entra application is entirely managed by your organisation.
B. Add the Entra ID (formerly known as Azure AD) account to the Database
This section is needed for Azure AD or Azure AD--private.
- Using SQL Server Management Studio, log in to the Azure SQL Database using the Entra Admin account.
- Run the following script to add the Entra ID account to the appropriate database and assign the appropriate roles.
CREATE USER [<ENTRA ID ACCOUNT>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<ENTRA ID ACCOUNT>];
ALTER ROLE db_datawriter ADD MEMBER [<ENTRA ID ACCOUNT>];
ALTER ROLE db_ddladmin ADD MEMBER [<ENTRA ID ACCOUNT>];
NOTE: Replace "<ENTRA ID ACCOUNT>" with the Entra account you want to provision. The roles to add are just suggestions. If your organisation uses a different set of roles or permissions, please use them instead. Please refer to this site for details of the mentioned roles.
Example
Entra ID Account to add: user01@alteryx.com
CREATE USER [user01@alteryx.com] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [user01@alteryx.com];
ALTER ROLE db_datawriter ADD MEMBER [user01@alteryx.com];
ALTER ROLE db_ddladmin ADD MEMBER [user01@alteryx.com];
C. Use the Entra (formerly known as Azure AD) Application in Alteryx
- In Designer, add an Input Data tool to the workflow canvas.
- Configure the tool to use the Data Connection Manager (DCM), if optional.
- In the Data Connections window, click the Data Sources on the left pane.
- Click the Quick Connect link under the Microsoft Azure SQL Database section.
NOTE: You may also use the Quick Connect link under Microsoft SQL Server.
- Create a new connection, datasource and credentials.
- For the authentication method, please select one of the following options:
- Azure AD
- Azure AD--private
For Azure AD
Once you select this authentication method, all you need to do next is to create a credential to store the tokens. Skip to step 8.
NOTE: This method option uses Entra (formerly known as Azure) Application, created and maintained by Alteryx.
For Azure AD--private
For this option, you will need to enter the following fields:
-
- Tenant ID: This is obtained from step 5 of Section A.
- Client ID: This is obtained from step 5 of Section A.
- Client Secret: This is obtained from step 7 of Section A.
NOTE: Keep the default OAuth authority URL unless you are on a non-commercial cloud (such as Azure Government) that requires a specific endpoint.
- Create a new credential for AZURE AD TOKENS--PRIVATE.
NOTE: Leave the scope value as it is.
- Click the Connect button.
You will be prompted to enter the Entra account created in Section B if this is the first time you are using this DCM connection. Depending on the Consent and User Settings in your Entra ID (formerly Azure AD), approval may be required to add the Entra application (Alteryx's or yours) to your Entra tenant. This applies to the first user using this Entra application.
Once the connection to Entra ID is successfully established, the tokens (refresh and access) are retained and will be used subsequently. You will be prompted to authenticate again if the refresh token has expired. This typically occurs after 90 days of inactivity, or if your organisation's policy revokes or limits the token.
NOTE: The lifetime of the refresh token is 90 days, as stated by Microsoft, and it may change in the future. Please refer to the Microsoft Identity Platform documentation to get the latest information.