Azure Active Directory Interactive Authentication
This article shows how to authenticate with an Azure AD identity by using interactive authentication.
Common Prerequisites
- Available account in Azure Active Directory.
- Available Azure MS SQL server.
- Azure Active Directory is set to centrally manage identity and access to Azure SQL Database.
- Installed MS ODBC Driver for SQL Server. The current version is 17.
DSN Connection
You can connect with DNS using Data Connection Manager (DCM) or without it.
Prerequisites: User or System DSN is configured in ODBC data source manager to use Azure Active Directory Interactive authentication.
DSN Connection without DCM
- Place the Input Data tool on the Designer canvas.
- Select the down arrow in the Connect a File or Database field.
- From Data Sources, choose Microsoft Azure SQL Database - ODBC.
- Choose DSN created in the preconditions from the dropdown.
- There is no need to enter the credentials here because the external browser is triggered.
- In the Query Builder, choose any table from your database.
- Select OK.
Credentials are required for each setting change and workflow run. To eliminate this, set Cache Data on the Input Data tool.
DSN Connection Using DCM
- Make sure that DCM is enabled.
- Place the Input Data tool on the Designer canvas.
- Below the Connect a File or Database field, select Set Up a Connection.
- Choose Microsoft Azure SQL Database - ODBC from Data Sources.
- Select the Add Data Source button.
- Enter a descriptive Data Source Name.
- Enter the name of DSN created in ODBC Data source manager and select Save.
- Select Connect Credential.
- For Authentication Method select No Credentials.
- Select Link.
- Select Connect.
- External browser is triggered to enter credentials.
- Choose any table from your database in the Query Builder.
- Select OK.
Credentials are required for each setting change and workflow run. To eliminate this, set Cache Data on the Input Data tool.
DSN-less Connection
You can establish a DSN-less connection using Data Connection Manager (DCM) or without it.
DSN-less Connection without DCM
- Place the Input Data tool on the Designer canvas
- Enter this Connection string in the Connect a File or Database field:
odbc:Driver={ODBC Driver 17 for SQL Server};Server=tcp:<_your_server_>,1433;Database=<_your_db_>;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;Authentication=ActiveDirectoryInteractive
Replace the text between the <> with actual values. - Select the 3 dot icon next to the Table or Query option.
- An external browser is triggered to enter credentials.
- Choose any table from your database in the Query Builder.
- Select OK.
Credentials are required for each setting change and workflow run. To eliminate this, set Cache Data on the Input Data tool.
DSN-less Connection Using DCM
- Make sure that DCM is enabled.
- Place the Input Data tool on the Designer canvas.
- Below the Connect a File or Database field select Set Up a Connection.
- Choose Microsoft SQL Server - Quick connect from Data Sources.
- Select the Add Data Source button.
- Provide a descriptive name for Data Source Name, actual server, and database name.
- Make sure the Driver is set to ODBC Driver 17 for SQL Server.
- Select the Save button.
- Select Connect Credential.
- Choose or provide Azure Active Directory Interactive for Authentication Method and select Link.
- Select Connect.
- An external browser is triggered to enter credentials.
- Choose any table from your database in the Query Builder.
- Select OK.
Credentials are required for each setting change and workflow run. To eliminate this, set Cache Data on the Input Data tool.