Skip to main content

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.

DNS Settings

DSN Connection without DCM

  1. Place the Input Data tool on the Designer canvas.

  2. Select the down arrow in the Connect a File or Database field.

  3. From Data Sources, choose Microsoft Azure SQL Database - ODBC.

    MSQL ODBC
  4. Choose DSN created in the preconditions from the dropdown.

  5. There is no need to enter the credentials here because the external browser is triggered.

    External browser
    External browser
  6. In the Query Builder, choose any table from your database.

  7. Select OK.

Note

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

  1. Make sure that DCM is enabled.

  2. Place the Input Data tool on the Designer canvas.

  3. Below the Connect a File or Database field, select Set Up a Connection.

  4. Choose Microsoft Azure SQL Database - ODBC from Data Sources.

    MSQL ODBC
  5. Select the Add Data Source button.

    Add Data Source
  6. Enter a descriptive Data Source Name.

  7. Enter the name of DSN created in ODBC Data source manager and select Save.

    Enter a name of DNS
  8. Select Connect Credential.

    Connect Credentials
  9. For Authentication Method select No Credentials.

  10. Select Link.

    Click Link
  11. Select Connect.

    Click Connect
  12. External browser is triggered to enter credentials.

  13. Choose any table from your database in the Query Builder.

  14. Select OK.

Note

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

  1. Place the Input Data tool on the Designer canvas

  2. 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.

  3. Select the 3 dot icon next to the Table or Query option.

  4. An external browser is triggered to enter credentials.

  5. Choose any table from your database in the Query Builder.

  6. Select OK.

Note

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

  1. Make sure that DCM is enabled.

  2. Place the Input Data tool on the Designer canvas.

  3. Below the Connect a File or Database field select Set Up a Connection.

  4. Choose Microsoft SQL Server - Quick connect from Data Sources.

    MSQL Server Quick Connect
  5. Select the Add Data Source button.

    Add Data Source
  6. Provide a descriptive name for Data Source Name, actual server, and database name.

  7. Make sure the Driver is set to ODBC Driver 17 for SQL Server.

  8. Select the Save button.

    Click on Save
  9. Select Connect Credential.

  10. Choose or provide Azure Active Directory Interactive for Authentication Method and select Link.

    Click Link
  11. Select Connect.

  12. An external browser is triggered to enter credentials.

  13. Choose any table from your database in the Query Builder.

  14. Select OK.

Note

Credentials are required for each setting change and workflow run. To eliminate this, set Cache Data on the Input Data tool.