Azure Active Directory Managed Service Identity

Version:
2022.3
Last modified: November 29, 2022

This article shows how to authenticate with an Azure AD identity by using a system-assigned or user-assigned managed identity.

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.
  • Azure Managed Identity created. Object (principal) ID is stored.

     

  • The user that corresponds to the Managed Identity is created in the SQL Database and the proper role is assigned to the user.

     

  • The created identity is assigned to a virtual machine that runs in Azure.

For more information, see the What are managed identities for Azure resources? article on the Microsoft documentation portal.

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 Managed Service Identity authentication.

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.
  4. Choose DSN created in the preconditions from the dropdown.
  5. There is no need to enter the User ID and Password.
  6. Choose any table from your database in the Query Builder.
  7. Select OK.

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.
  5. Select the Add Data Source button.
  6. Enter a descriptive Data Source Name.
  7. Enter the name of DSN created in ODBC Data source manager and select Save.
    Enter DNS name
  8. Select Connect Credential.
  9. For Authentication Method select No Credentials.
  10. Select Link.
    Click Link
  11. Select Connect.
    Click Connect
  12. Choose any table from your database in the Query Builder.
  13. Select OK.

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 Input Data tool on 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_database_>;Uid=<_your_user_id_>;Authentication=ActiveDirectoryMsi
    Replace the text between <> with actual values.
  3. Select the 3 dot icon next to the Table or Query option.
  4. Choose any table from your database in the Query Builder.
  5. Select OK.

DSN-less Connection Using DCM

  1. Make sure that DCM is enabled.
  2. Place the Input Data tool on the Designer canvas.
  3. Below Connect a File or Database field select Set Up a Connection.
  4. Choose Microsoft SQL Server - Quick connect from Data Sources.
  5. Select the Add Data Source button.
  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 Save
  9. Select Connect Credential.
  10. Choose or provide Azure Managed Service Identity for Authentication Method and select Link.
  11. Select Create New Credential.
  12. Enter a descriptive Credential Name and Object ID.
  13. Select the Create and Link button.Click Create

     

  14. Select Connect.Click Connect

     

  15. Choose any table from your database in the Query Builder.
  16. Select OK.
Was This Page Helpful?

Running into problems or issues with your Alteryx product? Visit the Alteryx Community or contact support. Can't submit this form? Email us.